GeoLite2 for PostgreSQL

pg_geolite2_csv.sql

-- GeoLite2 CSV files for use with PostgreSQL 9+
-- by Miles Elam <[email protected]>

-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
-- A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
-- OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
-- SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
-- LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
-- DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
-- THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
-- (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

-- This software consists of voluntary contributions and is licensed
-- under the MIT license. For more information, see
-- http://www.doctrine-project.org
-- http://www.opensource.org/licenses/mit-license.html  MIT License

BEGIN;

CREATE EXTENSION file_fdw;

CREATE SERVER geoip_server FOREIGN DATA WRAPPER file_fdw;
COMMENT ON SERVER geoip_server IS 'Used or loading GeoLite2 IP-to-location mapping';

CREATE FOREIGN TABLE geo.geolite2_city_blocks_ipv4
   (network inet NOT NULL,
    geoname_id integer ,
    registered_country_geoname_id integer ,
    represented_country_geoname_id integer ,
    is_anonymous_proxy boolean NOT NULL,
    is_satellite_provider boolean NOT NULL,
    postal_code character varying(126) ,
    latitude numeric ,
    longitude numeric ,
    accuracy_radius integer )
   SERVER geoip_server
   OPTIONS (filename '/var/local/postgresql/geo/GeoLite2-City-Blocks-IPv4.csv', format 'csv', header 'true');
COMMENT ON FOREIGN TABLE geo.geolite2_city_blocks_ipv4
  IS 'GeoLite2 IPv4 mapping to geocode CSV (Comma Separated Value) file.';

CREATE FOREIGN TABLE geo.geolite2_city_blocks_ipv6
   (network inet NOT NULL,
    geoname_id integer ,
    registered_country_geoname_id integer ,
    represented_country_geoname_id integer ,
    is_anonymous_proxy boolean NOT NULL,
    is_satellite_provider boolean NOT NULL,
    postal_code character varying(126) ,
    latitude numeric ,
    longitude numeric ,
    accuracy_radius integer )
   SERVER geo_server
   OPTIONS (filename '/var/local/postgresql/geo/GeoLite2-City-Blocks-IPv6.csv', format 'csv', header 'true');
COMMENT ON FOREIGN TABLE geo.geolite2_city_blocks_ipv6
  IS 'GeoLite2 IPv6 mapping to geocode CSV (Comma Separated Value) file.';

CREATE FOREIGN TABLE geo.geolite2_city_locations_en
   (geoname_id integer NOT NULL,
    locale_code character(2) NOT NULL,
    continent_code character(2) NOT NULL,
    continent_name character varying(126) NOT NULL,
    country_iso_code character(2) ,
    country_name character varying(126) ,
    subdivision_1_iso_code character varying(3) ,
    subdivision_1_name character varying(126) ,
    subdivision_2_iso_code character varying(3) ,
    subdivision_2_name character varying(126) ,
    city_name character varying(126) ,
    metro_code smallint ,
    time_zone character varying(126) )
   SERVER geo_server
   OPTIONS (filename '/var/local/postgresql/geo/GeoLite2-City-Locations-en.csv', format 'csv', header 'true');
COMMENT ON FOREIGN TABLE geo.geolite2_city_locations_en
  IS 'GeoLite2 geocode CSV (Comma Separated Value) file, English language localization.';

CREATE TYPE geo.continent AS ENUM
   ('Africa',
    'Antarctica',
    'Asia',
    'Europe',
    'North America',
    'Oceania',
    'South America');
COMMENT ON TYPE geo.continent
  IS 'Continents as given by the GeoLite2 geocode file, English language localization.';

CREATE MATERIALIZED VIEW geo.networks AS 
 SELECT geolite2_city_blocks_ipv4.network,
    COALESCE(geolite2_city_blocks_ipv4.represented_country_geoname_id, geolite2_city_blocks_ipv4.geoname_id) AS geoname,
    geolite2_city_blocks_ipv4.geoname_id AS physical_geoname,
    COALESCE(geolite2_city_blocks_ipv4.registered_country_geoname_id, geolite2_city_blocks_ipv4.geoname_id) AS isp_geoname,
    geolite2_city_blocks_ipv4.accuracy_radius,
    geolite2_city_blocks_ipv4.latitude,
    geolite2_city_blocks_ipv4.longitude,
    geolite2_city_blocks_ipv4.postal_code,
    geolite2_city_blocks_ipv4.is_anonymous_proxy,
    geolite2_city_blocks_ipv4.is_satellite_provider
   FROM geo.geolite2_city_blocks_ipv4
UNION ALL
 SELECT geolite2_city_blocks_ipv6.network,
    COALESCE(geolite2_city_blocks_ipv6.represented_country_geoname_id, geolite2_city_blocks_ipv6.geoname_id) AS geoname,
    geolite2_city_blocks_ipv6.geoname_id AS physical_geoname,
    COALESCE(geolite2_city_blocks_ipv6.registered_country_geoname_id, geolite2_city_blocks_ipv6.geoname_id) AS isp_geoname,
    geolite2_city_blocks_ipv6.accuracy_radius,
    geolite2_city_blocks_ipv6.latitude,
    geolite2_city_blocks_ipv6.longitude,
    geolite2_city_blocks_ipv6.postal_code,
    geolite2_city_blocks_ipv6.is_anonymous_proxy,
    geolite2_city_blocks_ipv6.is_satellite_provider
   FROM geo.geolite2_city_blocks_ipv6
WITH DATA;
COMMENT ON MATERIALIZED VIEW geo.networks
  IS 'IP to geocode mapping materialized for indexing.';
  
CREATE INDEX networks_network_gist
  ON geo.networks
  USING gist
  (network inet_ops);
COMMENT ON INDEX networks_network_gist
  IS 'IP to geocode index. GiST to allow searching IPs as inet within inet network blocks.';

CREATE MATERIALIZED VIEW geo.locations AS 
 SELECT geolite2_city_locations_en.geoname_id AS id,
    geolite2_city_locations_en.continent_name::geo.continent AS continent,
    geolite2_city_locations_en.country_iso_code AS country_iso,
    geolite2_city_locations_en.country_name AS country,
    geolite2_city_locations_en.subdivision_1_iso_code AS sub1_iso,
    geolite2_city_locations_en.subdivision_1_name AS sub1,
    geolite2_city_locations_en.subdivision_2_iso_code AS sub2_iso,
    geolite2_city_locations_en.subdivision_2_name AS sub2,
    geolite2_city_locations_en.city_name AS city,
    geolite2_city_locations_en.metro_code AS metro,
    geolite2_city_locations_en.time_zone AS timezone
   FROM geo.geolite2_city_locations_en
WITH DATA;
COMMENT ON MATERIALIZED VIEW geo.networks
  IS 'Geocode list materialized for indexing.';

CREATE UNIQUE INDEX locations_geocode_idx
  ON geo.locations
  USING btree
  (id);
COMMENT ON UNIQUE INDEX locations_geocode_idx
  IS 'Geocode list B-tree index.';

CREATE OR REPLACE FUNCTION geo.geolocation(
    IN ip inet,
    OUT network inet,
    OUT latitude numeric,
    OUT longitude numeric,
    OUT accuracy_radius integer,
    OUT continent geo.continent,
    OUT country character varying,
    OUT physical_continent geo.continent,
    OUT physical_country character varying,
    OUT isp_continent geo.continent,
    OUT isp_country character varying,
    OUT postal_code character varying,
    OUT sub1 character varying,
    OUT sub2 character varying,
    OUT metro smallint,
    OUT city character varying,
    OUT timezone character varying,
    OUT anonymous_proxy boolean,
    OUT satellite_provider boolean)
  RETURNS SETOF record AS
$BODY$select n.network, n.latitude, n.longitude, n.accuracy_radius,
       l.continent, l.country,
       physical.continent, physical.country,
       isp.continent, isp.country,
       n.postal_code,
       physical.sub1, physical.sub2,
       physical.metro, physical.city, physical.timezone,
       n.is_anonymous_proxy, n.is_satellite_provider
 from geo.networks n
 left outer join geo.locations l on n.geoname = l.id  -- who it actually belongs to
 left outer join geo.locations physical on n.physical_geoname = physical.id  -- physical location
 left outer join geo.locations isp on n.isp_geoname = isp.id  -- isp location
 where n.network && ip
 limit 1
$BODY$
  LANGUAGE sql STABLE LEAKPROOF STRICT;
COMMENT ON FUNCTION geo.geolocation(inet)
  IS 'Convenience function which also acts as an API to IP-geocode lookups.';

-----------

CREATE TABLE geo.geocache (
  network inet NOT NULL,
  latitude numeric(6,4),
  longitude numeric(7,4),
  accuracy_radius integer,
  continent geo.continent NOT NULL,
  country geo.country,
  physical_continent geo.continent NOT NULL,
  physical_country character varying(126),
  isp_continent geo.continent NOT NULL,
  isp_country character varying(126),
  postal_code character varying(126),
  sub1 character varying(126),
  sub2 character varying(126),
  metro smallint,
  city character varying(126),
  timezone character varying(126),
  anonymous_proxy boolean NOT NULL DEFAULT false,
  satellite_provider boolean NOT NULL DEFAULT false,
  updated timestamp(0) without time zone NOT NULL DEFAULT now(),
  CONSTRAINT geocache_pkey PRIMARY KEY (network)
);
COMMENT ON TABLE geo.geocache
  IS 'Geolocation cache table to speed access to geocode lookups, especially when most access in from a limited range of geography.';

CREATE INDEX geocache_network_gist
  ON geo.geocache
  USING gist
  (network inet_ops);
COMMENT ON INDEX geocache_network_gist
  IS 'IP to geocode index. GiST to allow searching IPs as inet within inet network blocks.';

COMMIT;