Solving Spatial Problems with PostGIS


Regina Obe

PostGIS In Action 3rd Edition available for Purchase

40% off on all Locate Press e-Books with coupon code foss4g2021 Sept 24th-Oct 4 2021 https://locatepress.com/pgrouting

Where to get PostGIS

Topics We'll Cover

  • ogr_fdw - Loading Data - using ogr_fdw Foreign Data Wrapper extension
  • postgis
    • Taking measurements
    • Data Simplification
    • Reprojecting data to measure preserving planar projection
    • Proximity Analysis (both geometry/geography)
    • Breaking Geometries apart
    • Breaking Lines into approximate equal segments
    • Aggregating points into lines
  • postgis_raster
    • Creating Raster data from Vector data

How you install extensions in PostGIS 3+

This is running in psql. If in pgAdmin just manually reconnect to your gisdb. Example is gisdb but do for any spatial databases you have.

CREATE DATABASE gisdb;
ALTER DATABASE gisdb SET search_path=public,postgis,tiger,contrib;
\c gisdb
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS contrib;
-- core includes geometry / geography
CREATE EXTENSION postgis SCHEMA postgis; 
-- raster type and functions
CREATE EXTENSION postgis_raster SCHEMA postgis;
-- extended support for 3D geometries
CREATE EXTENSION postgis_sfcgal SCHEMA postgis;
-- topogeometry relational view of spatial data
CREATE EXTENSION postgis_topology;
-- needed by tiger geocoder
CREATE EXTENSION fuzzystrmatch SCHEMA contrib;
--  for standardizing addresses
CREATE EXTENSION address_standardizer SCHEMA contrib;
-- geocoding and reverse geocoding using US TIGER data
CREATE EXTENSION postgis_tiger_geocoder;

How you upgrade

SELECT postgis_extensions_upgrade();

Check your installation

SELECT postgis_full_version();
screen
POSTGIS="3.2.0dev 3.2.0alpha1-19-gf48c4a86a" [EXTENSION] 
PGSQL="140" GEOS="3.10.0dev-CAPI-1.15.0" 
SFCGAL="1.3.10" PROJ="7.1.1" 
GDAL="GDAL 3.2.3, released 2021/04/27" 
LIBXML="2.9.9" LIBJSON="0.12" 
LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER

PostGIS 3.0 enhanced GeoJSON support to now accept full features

very very old way of creating a feature collection - https://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html (painful)

New way

SELECT json_build_object('type', 'FeatureCollection', 'features', 
    json_agg(ST_AsGeoJSON(r.*)::json) )
FROM recent_building_permits AS r;