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_columnsPostGIS 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.shpPostGIS OGR FDW
- https://github.com/pramsey/pgsql-ogr-fdw
- FOREIGN SERVER, TABLE - no indices
- MATERIALIZED VIEW
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
PostGIS random points generator
- generate M random points inside N (not so) random polygons
- LATERAL JOIN #ftw, random(), generate_series()
Don't click the link yet
PostGIS polygon from centroid
Think, don't click the link yet!
PostGIS rectangular grid
Think, don't click the link yet!
PostGIS features covered by other layer
PostGIS workshop
By Michal Zimmermann
PostGIS workshop
- 1,501
