Geospatial Analysis, KSIT workshop on 2015-2-11

ksit-2015-2-11.md

Introduction To PostGIS

• Blog https://daniel-j-h.github.io/post/gentle-introduction-postgis/
• PostGIS reference: http://postgis.net/docs/reference.html
• Postgres, esp. II.7 Queries http://www.postgresql.org/docs/9.4/interactive/index.html
• geojson.io: http://geojson.io/
• Mapbox: https://www.mapbox.com/
• CartoDB: http://cartodb.com/

Point Geometry From Coordinates

http://postgis.refractions.net/docs/ST_MakePoint.html

SELECT ST_MakePoint(8.4137603, 49.0091767);

                st_makepoint                
--------------------------------------------
 01010000002CC4D963D8D320404E31BDB32C814840

See the SRID note in the documention's example.

Note: For primitives in general, there is ST_Make*, e.g. ST_Make{Point, Polygon, Box2d, ...}

GeoJSON From Geometry

http://postgis.org/docs/ST_AsGeoJSON.html

SELECT ST_AsGeoJSON(ST_MakePoint(8.4137603, 49.0091767));

                     st_asgeojson                      
-------------------------------------------------------
 {"type":"Point","coordinates":[8.4137603,49.0091767]}

Geometry Vs Geography

• geometry, planar http://postgis.net/docs/geometry.html
• geography, ellipsoidal http://postgis.net/docs/geography.html

Geometry

• ST_X, ... http://postgis.net/docs/reference.html#Geometry_Accessors
• ST_Rotate, http://postgis.net/docs/reference.html#Geometry_Editors
• overlaps, intersects, to the left, ... http://postgis.net/docs/reference.html#Operators
• ST_Area, ST_Contains, ST_DWithin, ... http://postgis.net/docs/reference.html#Spatial_Relationships_Measurements
• ST_Buffer, http://postgis.net/docs/reference.html#Geometry_Processing

Patterns And Casting

SELECT name,
       population
FROM planet_osm_point
WHERE place='city'
  AND population ~ '^[0-9]+$'
ORDER BY population::int DESC LIMIT 5;

         name         | population 
----------------------+------------
 Stuttgart            | 613392
 Mannheim             | 307640
 Karlsruhe            | 283959
 Freiburg im Breisgau | 214716
 Heidelberg           | 142889

Exploring Map Features

http://wiki.openstreetmap.org/wiki/Map_Features

SELECT distinct(amenity)
FROM planet_osm_point;

Note: do this with amenity, shop, tourism, "natural", etc. to get a feel for what is there.

Indexed K-Nearest-Neighbor

• http://postgis.net/docs/geometry_distance_centroid.html
• http://postgis.net/docs/geometry_distance_box.html

SELECT name,
       ST_AsGeoJSON(way)
FROM planet_osm_point
WHERE place = 'suburb'
ORDER BY way <-> ST_MakePoint(8.4137603, 49.0091767) LIMIT 5;

    name     |                     st_asgeojson                      
-------------+-------------------------------------------------------
 Südstadt    | {"type":"Point","coordinates":[8.4060496,49.0021606]}
 Oststadt    | {"type":"Point","coordinates":[8.4267919,49.0118481]}
 Rintheim    | {"type":"Point","coordinates":[8.4407597,49.0154961]}
 Dammerstock | {"type":"Point","coordinates":[8.3999556,48.9850514]}
 Beiertheim  | {"type":"Point","coordinates":[8.3889974,48.993349]}

Boundaries And Polygons

Filter on Polygon admin_level

• http://wiki.openstreetmap.org/wiki/Key:admin_level#admin_level
• http://wiki.openstreetmap.org/wiki/DE:Grenze#Verwaltungsgrenzen:_niedrigere_Ebenen