Notes On Postgres And PostGIS

I recently worked on a python/jupyterhub project with both postgis and postgres as separate deployments into a kubernetes (and a minikube) environment. Some handy commands and snippets are included below.

psql The Postgres Command Client

The Postgres client has some quirky commands as shown below.

List user defined functions in a schema

\df schema_name.*

\df+ schema_name.function_name

Command Purpose
'/Applications/Postgres.app/Contents/Versions/9.3/bin'/psql -p5600 -h 192.168.99.100 -U postgres Connect to the postgres server as user postgres, handy for container based servers
/Applications/Postgres.app/Contents/Versions/9.3/bin/psql -p5600 -h 192.168.99.100 -U username dbname Login to the dbname db as given username
docker run –name pg-new-mount -p 5600:5432 -d –volume pgdata:/var/lib/postgresql/data postgres The docker run to support the client above
CREATE TABLE foo (id SERIAL); Simple create table
\z lists the tables for the current user
\d+ foo Describes the table foo
SELECT current_database(); Shows the current database
\l Lists the databases
SELECT datname FROM pg_database; list databases
\connect database_name connect to a new database
\du List roles
\dn List db schemas
\dt schema_name.* List the tables in the schema_name schema (if you have access)
select tablename from informationschema.tables where tableschema = 'schemaname'; show schema information
SELECT rolname FROM pg_roles; Show all the roles in the installation, it really is rolname
show search_path; Shows the search path psql uses when looking for tables in schemas
alter database schemanamemetadata set searchpath=schemaname;
set searchpath TO schemaname; As above
SELECT CURRENTUSER, SESSIONUSER; Show the current role
select * from pgstatactivity where datname = ''; List current connections, includes this one!!
SELECT pgterminatebackend (pgstatactivity.pid) FROM pgstatactivity WHERE pgstatactivity.datname = ''; Remove all connections including this one!!
\dx List extensions
select * from pg_extension
select * from pgavailableextensions;
\z schema_name.table; Shows the privs on a specific table
select schemaname, viewname from pgcatalog.pgviews where schemaname NOT IN ('pgcatalog', 'informationschema') order by schemaname, viewname; List the views
\timing Turns on timing on the psql command line

Create and use a view

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

See also materialized view

psycopg2 Postgres Driver

This will need to be installed if tests are to be run locally.

pip3 install psycopg2

DONT FORGET cursor.mogrify to show the exact sql thats going to RUN!!!!!

Notes On Geographic operations And Tools

WGS84 SRID is EPSG code 4979 for sat nav systems, see

WHS84 spatial reference

PostGis Tutorial notes

From PostGis Tutorial notes

General GIS SQL Commands

Command Notes
SELECT * FROM geometry_columns; Lists the geometry columns and associated tables
select STGeometryType(wkt), STNDims(wkt), STSRID(wkt) from tablewithwktcolumnofpolygons List info about a geom column
select STASTEXT(wkt) from tablewithwktcolumnofpolygons; Will get back the original polygon.
select STNPoints(wkt) from tablewithwktcolumnofpolygons; Num of points in the sector polygon
ST_Length Returns zero for a polygon, works for a line string
STStartPoint, STEndPoint
select STArea(wkt) from tablewithwktcolumnofpolygons limit 1
ST_Perimeter
SELECT 'POINT(0 0)'::geometry; Short form conversion
SELECT 0.9::text;
SELECT 'SRID=4326;POINT(0 0)'::geometry;
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography) Closest point of approach between string and a point 

Distance calculation
SELECT ST_Distance('POINT(-122.311777 47.449889)'::geography, 'POINT(-0.461388 51.477500)':: geography);
  st_distance   
----------------
 7724659.483029
(1 row)



select ST_Area(wkt) from <table with wkt column of polygons> limit 1 ->

st_area      
------------------
 2.33855408729244

select ST_Perimeter(wkt) from <table with wkt column of polygons> limit 1 ->

st_perimeter   
------------------
6.22493894102638

 select ST_ASTEXT('LINESTRINGZ(2.12 45.74 10.0, 3.86 46.26 600)'); ->
 LINESTRING Z (2.12 45.74 10,3.86 46.26 600)

 select * from <table with wkt column of polygons>  where ST_Intersects(wkt, ST_GeomFromText('LINESTRING Z (2.12 45.74 10,3.86 46.26 600)',4326));

Distances

See PostGis ST_Distance

\d+ <table with wkt column of polygons>



-- same as geometry example but note units in meters - use sphere for slightly faster less accurate

SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
FROM (SELECT
ST_GeogFromText('SRID=4326;POINT(2.5499 49.01)') as gg1,
ST_GeogFromText('SRID=4326;POINT(-0.46 51.47)') as gg2
) As foo;

-->

spheroid_dist  |   sphere_dist   
-----------------+-----------------
347759.89163265 | 347275.64141935
(1 row)

Bounding Radius

select ST_ASText((bound).center), (bound).radius  from (select ST_MinimumBoundingRadius(wkt) as bound from <table with wkt column of polygons>) as subquery

st_astext                  |      radius       
--------------------------------------------+-------------------
POINT(2.61719589492066 47.3237780689945)   |  1.16299645675826
POINT(1.0822659630465 43.1904234118613)    | 0.826763292488026
POINT(-0.366666665 45.91041667)            |  1.74005752149813
POINT(1.0822659630465 43.1904234118613)    | 0.826763292488026
POINT(2.61719589492066 47.3237780689945)   |  1.16299645675826
POINT(1.283333335 45.59527778)             |  2.19553505092441
POINT(1.324583335 44.45666667)             |  1.30180010086408
POINT(1.63381688120039 45.8827575846081)   |  1.29474388689664
POINT(1.324583335 44.45666667)             |  1.30180010086408
POINT(1.324583335 44.45666667)             |  1.30180010086408
POINT(1.63381688120039 45.8827575846081)   |  1.29474388689664
POINT(3.28903998123588 45.4191111629066)   |  1.20416149145394
POINT(3.000833335 43.468055555)            |   1.5964052259641
POINT(3.000833335 43.468055555)            |   1.5964052259641
POINT(1.324583335 44.45666667)             |  1.30180010086408
POINT(-0.651666665 43.758333335)           |  1.50379704835608
POINT(3.000833335 43.468055555)            |   1.5964052259641
POINT(-0.651666665 43.758333335)           |  1.50379704835608
POINT(-0.651666665 43.758333335)           |  1.50379704835608
POINT(-0.651666665 43.758333335)           |  1.50379704835608
POINT(1.63381688120039 45.8827575846081)   |  1.29474388689664
POINT(3.28903998123588 45.4191111629066)   |  1.20416149145394
POINT(-0.366666665 45.91041667)            |  1.74005752149813
POINT(-0.366666665 45.91041667)            |  1.74005752149813
POINT(1.63381688120039 45.8827575846081)   |  1.29474388689664
POINT(3.000833335 43.468055555)            |   1.5964052259641
POINT(3.28903998123588 45.4191111629066)   |  1.20416149145394
POINT(2.61719589492066 47.3237780689945)   |  1.16299645675826
POINT(1.0822659630465 43.1904234118613)    | 0.826763292488026
POINT(2.61719589492066 47.3237780689945)   |  1.16299645675826
POINT(1.0822659630465 43.1904234118613)    | 0.826763292488026
POINT(-0.860764308903883 45.6163264793239) |  1.17377245814572
POINT(-0.809554408861334 45.3585590121638) |  1.05950860540141
POINT(2.657083335 46.72416667)             | 0.857504958664802
POINT(-0.0370833349999999 43.716944445)    |  2.01037247133303
POINT(3.28903998123588 45.4191111629066)   |  1.20416149145394
POINT(1.985833335 43.45888889)             |  1.25874058195442
POINT(-0.366666665 45.91041667)            |  1.74005752149813
(38 rows)

Distances And Shapes

Distance Between Two Points

Consider a polygon around a eighth of the globe:

SELECT  ST_Perimeter(geog) As per_meters from ST_GeogFromText('POLYGON Z (( 0.0 0.0 0.0, 0.0 45.0 0.0, -45.0 45.0 0.0, -45.0 0.0 0.0, 0.0 0.0 0.0))') as geog;
    per_meters    
------------------
 18480525.9999676

A linestring:

SELECT  ST_Length(geog) As per_meters from ST_GeogFromText('MULTILINESTRING(( 0.0 0.0 0.0, 0.0 45.0 0.0))') as geog;
    per_meters    
------------------
 4984944.37797774

On the sphere this would be 5004000m delta = 19.04 km 0.4%

All together :

SELECT  ST_Length(geog) As per_meters_spheroidal, ST_Length(geog, false) As per_meters_spherical from (select ST_GeogFromText('SRID=4326;MULTILINESTRING(( 0.0 0.0 0.0, 0.0 45.0 0.0))') as geog) as foo;
 per_meters_spheroidal | per_meters_spherical
-----------------------+----------------------
      4984944.37797774 |     5003778.58805842
(1 row)

At a higher latitude (more european) :

SELECT  ST_Length(geog) As per_meters_spheroidal, ST_Length(geog, false) As per_meters_spherical from (select ST_GeogFromText('SRID=4326;MULTILINESTRING(( 45.0 0.0 0.0, 45.0 45.0 0.0))') as geog) as foo;
 per_meters_spheroidal | per_meters_spherical
-----------------------+----------------------
      4984944.37797774 |     5003778.58805842

Distance is 18.83421 km

Find Intersects Between A Line String And The Sectors Table

select * from <table with wkt column of polygons> where ST_Intersects(wkt, ST_GeomFromText('LINESTRING(2.56282 49.0011, -0.47896 51.46662)', 4326));

Duplicate Airspace ids with different altitudes

 select id, min_flight_level, max_flight_level, av_icao_state_id, av_name, object_id  from unit.sectors where av_airspace_id = 'EGTTCPT';

 id  | min_flight_level | max_flight_level | av_icao_state_id | av_name | object_id
-----+------------------+------------------+------------------+---------+-----------
  40 |            17500 |            21500 | EG               |         |   2209913
 325 |            15500 |            17500 | EG               |         |   2209752
(2 rows)

Time: 1.564 ms

BUT EGTTCPT only appears once in the intersects list above, how come?

See

http://prj2epsg.org/ Converts WKT from .prj files to EPSG codes.

Spatial reference lists

Getting the schema working was hard but this link actually helped : https://www.postgresql.org/message-id/51FBFC48.5080009%40boscorama.com