First step is the installation of PostgreSQL and PostGIS
- Download and Install PostgreSQL 9.6 from EnterpriseDB - https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
- Download PostGIS 2.3.2 - http://download.osgeo.org/postgis/windows/pg96/
- If you still don't have PgAdmin, you need to install a PgAdmin III. The PostGIS 2.3.2 still uses PgAdmin III version
- Try and Open your PostgreSQL Database from PgAdmin III. It will give you a warning, just ignore it for now.
- Extract PostGIS to PostgreSQL installation folder (Program Files**)
- Create new Database (UTF8) to implement PostGIS,
WARNING : Do not implement PostGIS in Default Database postgres.
Create Database GOLIVEHEAT UTF8;
- 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;
- 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)') );
- 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;
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
- 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;
[ FIELDS TERMINATED BY ',' ] [ ENCLOSED BY '"' ] [ HEADER ]
Dont forget to UNTICK field geog, because this is the field that we will use for spatial data.
I hope this will help anyone getting started in PostGIS and spatial query data. And to utilize it in REST web services.
