Tuesday, March 14, 2017

How to - Getting Started Postgis and Spatial Query in PostgreSQL

This is a step by step on how to start using PostGIS and creating your own spatial query. This tutorial is focused on spatial query to support programming a REST web service from a backend database.

First step is the installation of PostgreSQL and PostGIS
  1. Download and Install PostgreSQL 9.6 from EnterpriseDB - https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
  2. Download PostGIS 2.3.2 - http://download.osgeo.org/postgis/windows/pg96/
  3. If you still don't have PgAdmin, you need to install a PgAdmin III. The PostGIS 2.3.2 still uses PgAdmin III version
  4. Try and Open your PostgreSQL Database from PgAdmin III. It will give you a warning, just ignore it for now.
  5. Extract PostGIS to PostgreSQL installation folder (Program Files**)
 Second Step, Lets activate PostGIS in PostgreSQL from PgAdmin III
  1. Create new Database (UTF8) to implement PostGIS, 
    WARNING : Do not implement PostGIS in Default Database postgres.

    Create Database GOLIVEHEAT UTF8;

  2. Enable PostGIS using query Extension :

    -- Enable PostGIS (includes raster)
    CREATE EXTENSION postgis;
    -- Enable Topology
    CREATE EXTENSION postgis_topology;
    -- Enable PostGIS Advanced 3D 
    -- and other geoprocessing algorithms
    -- sfcgal not available with all distributions
    CREATE EXTENSION postgis_sfcgal;
    -- fuzzy matching needed for Tiger
    CREATE EXTENSION fuzzystrmatch;
    -- rule based standardizer
    CREATE EXTENSION address_standardizer;
    -- example rule data set
    CREATE EXTENSION address_standardizer_data_us;
    -- Enable US Tiger Geocoder
    CREATE EXTENSION postgis_tiger_geocoder;
    
    

  3. Create Table : Locations, and populate with initial data

    CREATE TABLE locations(loc_id integer primary key
       , loc_name varchar(70), geog geography(POINT) );
    INSERT INTO locations(loc_id, loc_name, geog)
     VALUES (1, 'Waltham, MA', ST_GeogFromText('POINT(42.40047 -71.2577)') )
       , (2, 'Manchester, NH', ST_GeogFromText('POINT(42.99019 -71.46259)') )
       , (3, 'TI Blvd, TX', ST_GeogFromText('POINT(-96.75724 32.90977)') );
    

  4. Test Query

    --Test Query Spatial 1 ;
    SELECT row_to_json(fc)
     FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
     FROM (SELECT 'Feature' As type
     , ST_AsGeoJSON(lg.geog)::json As geometry
     , row_to_json((SELECT l FROM (SELECT loc_id, loc_name) As l
       )) As properties
       FROM locations As lg   ) As f )  As fc;
        
    --Test Query Spatial 2 ;
    SELECT row_to_json(fc)
     FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
     FROM (SELECT 'Feature' As type
     , ST_AsGeoJSON(lg.geog)::json As geometry
     , row_to_json((loc_id, loc_name)) As properties
       FROM locations As lg   ) As f )  As fc;
    
    
As you can see, the test query will give you a JSON based result. Since its related to geo location, the format follows a GeoJSON standards.

Now lets populate the database with a rather large dataset.
  • Download Data from : http://lite.ip2location.com/download?db=db5&type=csv&version=4
  • Populate data to table ip2location_geom.

    CREATE TABLE ip2location_geom(
     ip_from bigint NOT NULL,
     ip_to bigint NOT NULL,
     country_code character(2) NOT NULL,
     country_name character varying(64) NOT NULL,
     region_name character varying(128) NOT NULL,
     city_name character varying(128) NOT NULL,
     latitude real NOT NULL,
     longitude real NOT NULL,
     geog geography(Point,4326), 
     CONSTRAINT ip2location_geom_pkey PRIMARY KEY (ip_from, ip_to)
    );
    

  •  Import CSV file from Downloaded file using Wizards 

  • [ FIELDS TERMINATED BY ',' ] [ ENCLOSED BY '"' ] [ HEADER ]
    Dont forget to UNTICK field geog, because this is the field that we will use for spatial data.
  • Test Query - Data IP2Location.

    -- First Query : 
    select country_name, count(country_name) from ip2location_geom 
    where country_name = 'Indonesia' 
    group by country_name;
    
    -- Second Query : 
    select * from ip2location_geom where country_name = 'Indonesia'  
    

  • Update Geometry field in Table 

    UPDATE ip2location_geom set geog = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
    

  • Query GeoJSON for ip2location_geom

    SELECT row_to_json(fc) 
    FROM ( 
      SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
     FROM (
       SELECT 'Feature' As type
      , ST_AsGeoJSON(lg.geog)::json As geometry
      , row_to_json((country_code, country_name, region_name, city_name, longitude, latitude)) As properties
       FROM ip2location_geom As lg   
      ) As f 
    )  As fc;
    
    -- Query Results : Confirms the lat lon from existing data is the same as the lat lon in spatial data.
    
    
    

  • Query GeoJSON for ip2location_geom only for Indonesia

    --Query GeoJSON for ip2location_geom only for Indonesia : ON Query DB 
    --We already confirms that latitude and longitude is the same. Don't view lat lon in this query !!
    SELECT row_to_json(fc) 
    FROM ( 
      SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
     FROM (
      SELECT 'Feature' As type
       , ST_AsGeoJSON(lg.geog)::json As geometry
       , row_to_json((country_code, country_name, region_name, city_name)) As properties
         FROM ip2location_geom As lg WHERE lg.country_name = 'Indonesia'   
      ) As f 
    )  As fc;
    
    
From this point on, you can call the query from web service to produce a GeoJSON format for your data.
I hope this will help anyone getting started in PostGIS and spatial query data. And to utilize it in REST web services.