PostGIS workshop

MUNI Geotechnology Camp

@zimmicz

PostGIS

PostGIS sources

PostGIS mailing list
Paul Ramsey's http://blog.cleverelephant.ca
My https://www.zimmi.cz/posts
GIS StackExchange

PostGIS installation

CREATE EXTENSION postgis;

\d spatial_ref_sys
\d geometry_columns
\d geography_columns

PostGIS data

geometry versus geography
tons of different CRS
tons of different units

PostGIS big data import

ogr2ogr -f "PostgreSQL" PG:"dbname=database user=username active_schema=osm" \
        -s_srs EPSG:4326 \
        -t_srs EPSG:5514 \
        -lco GEOMETRY_NAME=geom \
        -nlt PROMOTE_TO_MULTI \
        -gt 65000 \
        gis.osm_buildings_a_free_1.shp

https://gist.github.com/zimmicz/169c109c3e9f2a39ff9dde90e1602f30

PostGIS OGR FDW

https://github.com/pramsey/pgsql-ogr-fdw
FOREIGN SERVER, TABLE - no indices
MATERIALIZED VIEW

https://gist.github.com/zimmicz/e3aaaf3b173f394349aae4fc5cdca511

PostgreSQL optimization tips

UNLOGGED tables
TEMPORARY tables
More of little is always better than less of bigger
Think before you code
Use stored procedures
Try to avoid curves - how do you interpolate one?
Valid data
CTE
INDICES

PostGIS spatial indices

http://postgis.org/support/rtree.pdf
R-tree algorithms

PostGIS @#!?> functions

ST_Difference 1:1 relationship
ST_Disjoint
ST_Union, ST_Buffer, ST_Collect

https://gist.github.com/zimmicz/a5a640c04e4a95c089d6f803106de783

https://gist.github.com/zimmicz/58753e6fe40a77089b86ff90df571c18

https://gist.github.com/zimmicz/b6e7c36a05fca3adf5113a2ee80710b6

PostGIS random points generator

generate M random points inside N (not so) random polygons
LATERAL JOIN #ftw, random(), generate_series()

https://gist.github.com/zimmicz/01131920f3560dc294c1e279cd1ac2b0

Don't click the link yet

PostGIS polygon from centroid

https://gist.github.com/zimmicz/828772137eff52835012a508c303a040

Think, don't click the link yet!

PostGIS rectangular grid

https://gist.github.com/zimmicz/585b782cf0c3b5b2efb84bf4955a0405

Think, don't click the link yet!

PostGIS features covered by other layer

https://gist.github.com/zimmicz/03ec7ab499725761d5bff6d9a1db8cbd

https://gist.github.com/zimmicz/d0b0ee93e4e4bcef907cbc9ee1393a8a

Made with Slides.com