PostGRES+PostGIS.md

Start the db

Open PgAdmin III from dash
sudo /usr/sbin/service postgresql start
reconnect from PGAdmin using password

PostGIS Documentation

View installed and available extensions

SELECT * FROM pg_extension
SELECT * FROM pg_available_extensions

Installing postgis on Database

DONT INSTALL ON postgres DATABASE

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;

Well-Known Text (WKT) form and the Well-Known Binary (WKB) form.

Both WKT and WKB include information about the type of the object and the coordinates which form the object. Examples of the text representations (WKT) of the spatial objects of the features are as follows: • POINT(0 0) • LINESTRING(0 0,1 1,1 2) • POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) • MULTIPOINT((0 0),(1 2)) • MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) • MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))) • GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))

SRID

The OpenGIS specification also requires that the internal storage format of spatial objects include a spatial referencing system identifier (SRID). The SRID is required when creating spatial objects for insertion into the database.

Input/Output of these formats are available using the following interfaces: bytea WKB = ST_AsBinary(geometry); text WKT = ST_AsText(geometry); geometry = ST_GeomFromWKB(bytea WKB, SRID); geometry = ST_GeometryFromText(text WKT, SRID);

A valid insert statement to create and insert an OGC spatial object would be: INSERT INTO geotable ( the_geom, the_name ) VALUES ( ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place');

Terraloupe - SRID

At terraloupe, we use WGS84, EPSG/SRID - 4326

Make a postgis db and check Schemas/public/Tables/spatial_ref_sys entries. Here you will find all World Geodetic System Entry for EPSG/SRID - 4326 has below columns:- srid - 4326; auth_name - "EPSG"; auth_srid - 4326; srtext - "GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]"; proj4text - "+proj=longlat +datum=WGS84 +no_defs"

WGS84

https://confluence.qps.nl/qinsy/en/world-geodetic-system-1984-wgs84-29855173.html https://gis.stackexchange.com/questions/3334/difference-between-wgs84-and-epsg4326 https://epsg.io/4326, http://spatialreference.org/ref/epsg/wgs-84/

###Creating a table with z coordinate point/LINESTRING/Polygon and explicitly specifying srid 4326 CREATE TABLE ptzgeogwgs84(gid serial PRIMARY KEY, geog geography(POINTZ,4326) ); CREATE TABLE lgeog(gid serial PRIMARY KEY, geog geography(LINESTRING, 4326) ); CREATE TABLE lgeog(gid serial PRIMARY KEY, geog geography(POLYGON,4326) );

CREATE TABLE global_points (id SERIAL PRIMARY KEY, name VARCHAR(64), location GEOGRAPHY(POINT,4326)); INSERT INTO global_points (name, location) VALUES ('Town', ST_GeogFromText('SRID=4326;POINT(-110 30)') ); INSERT INTO global_points (name, location) VALUES ('Forest', ST_GeogFromText('SRID=4326;POINT(-109 29)') ); INSERT INTO global_points (name, location) VALUES ('London', ST_GeogFromText('SRID=4326;POINT(0 49)') );

Show a distance query and note, London is outside the 1000km tolerance

SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeogFromText('SRID=4326; POINT(-110 29)'), 1000000);

You can see the power of GEOGRAPHY in action by calculating how close a plane flying from Seattle to London (LINESTRING(-

122.33 47.606, 0.0 51.5)) comes to Reykjavik (POINT(-21.96 64.15)). -- Distance calculation using GEOGRAPHY (122.2km) SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography); -- Distance calculation using GEOMETRY (13.3 "degrees") SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)':: geometry);

Index the test table with a spherical index

CREATE INDEX global_points_gix ON global_points USING GIST ( location );

Get Information of All tables with Geography data

SELECT * FROM geography_columns;

Get Information of All tables with Geometry data

SELECT * FROM geometry_columns;

Altering table, adding 3D linestring

CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25), geom geometry(LINESTRING,4326) ); ALTER TABLE roads ADD COLUMN geom2 geometry(LINESTRINGZ,4326);

Altering a non-geometric Table using AddGeometryColumn

CREATE TABLE ROADS ( ID int4, ROAD_NAME varchar(25) )

AddGeometryColumn( <schema_name>, <table_name>, <column_name>, , , ) SELECT AddGeometryColumn(’public’, ’roads’, ’geom’, 4326, ’LINESTRING’, 2)

Check geometries are Valid and Simple

SELECT ST_IsValid('LINESTRING(0 0, 1 1)'), ST_IsValid('LINESTRING(0 0, 0 0, 0 0)');

Enforce geometries added are Valid and Simple

Note - Strictly compliant OGC geometries cannot have Z or M values. The ST_IsValid() function won’t consider higher dimensioned geometries invalid! Invocations of AddGeometryColumn() will add a constraint checking geometry dimensions, so it is enough to specify 2 there. ALTER TABLE mytable ADD CONSTRAINT geometry_valid_check CHECK (ST_IsValid(the_geom));