Tools › Highlight Code

Know what you're getting – Unlike many sites, all our code is clearly licensed. Join Siafoo Now or Learn More

Highlight Code

You are viewing code from PostgreSQL script for loading GeoIP data (New BSD). Feel free to modify it; changes will not be saved.

Bold fields are required

Language

Code

Enter code here

--DROP TABLE locations;
--DROP TABLE blocks;

-- Table DDL
CREATE TABLE locations
(
  id bigint NOT NULL,
  country character(2) NOT NULL,
  region character(2),
  city character varying(75),
  postal_code character varying(15),
  latitude numeric(6,4) NOT NULL,
  longitude numeric(7,4),
  metro_code integer,
  area_code integer,
  CONSTRAINT locations_pkey PRIMARY KEY (id)
);

CREATE TABLE blocks
(
  start_ip bigint NOT NULL,
  end_ip bigint NOT NULL,
  location_id bigint NOT NULL,
  CONSTRAINT blocks_pkey PRIMARY KEY (start_ip, end_ip)
);

-- Data is in ISO-8859-15 format
SET client_encoding to "ISO-8859-15";

-- The locations table
COPY locations (id, country, region, city, postal_code, latitude, longitude, metro_code, area_code) 
FROM '/tmp/GeoLiteCity-Location.csv' WITH CSV HEADER;
   
-- Then the blocks table
COPY blocks FROM '/tmp/GeoLiteCity-Blocks.csv' WITH CSV HEADER;

-- Uncomment all of this if you have IP4r
-- Add the range column
--ALTER TABLE blocks ADD COLUMN ip_range ip4r;
--ALTER TABLE blocks ALTER COLUMN ip_range SET STORAGE PLAIN;
-- Perform Conversion
-- UPDATE blocks SET ip_range = ip4r(start_ip::ip4, end_ip::ip4)
-- Drop Unneeded Columns
--ALTER TABLE blocks DROP COLUMN start_ip;
--ALTER TABLE blocks DROP COLUMN end_ip;

-- The Indexes
-- Comment this out if you have IP4r
CREATE INDEX ix_start_end_ip ON blocks 
    USING btree (start_ip, end_ip) WITH (FILLFACTOR=100);
CREATE INDEX ix_end_start_ip ON blocks 
    USING btree (end_ip, start_ip) WITH (FILLFACTOR=100);

-- Uncomment if you have IP4
-- CREATE INDEX ix_ip_range ON blocks
--    USING gist (ip_range) WITH (FILLFACTOR=100);

-- Utility Functions
-- Comment this out if you have IP4r
CREATE OR REPLACE FUNCTION inet_to_block(inet) RETURNS bigint AS $$
    SELECT $1 - inet '0.0.0.0'
$$ LANGUAGE SQL;

-- Uncomment this if you have IP4r
--CREATE OR REPLACE FUNCTION get_location(inet) RETURNS bigint AS $$
--  SELECT location_id FROM %s.blocks
--  WHERE ip_range >>= ip4($1)
--$$ LANGUAGE SQL;

Login
Password
	Remember me