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.