PostGIS In Action 3rd Edition available for Purchase¶
- Get a copy of 2nd edition with purchase of 3rd edition
- Covers PostGIS 3, 3.1, and highlights of upcoming 3.2
- https://www.manning.com/books/postgis-in-action-third-edition
- Available in hard-copy direct from Manning and other publishers
- https://www.postgis.us find code and data here
-
Live Book https://livebook.manning.com/book/postgis-in-action-third-edition
Where to get PostGIS¶
- PostGIS Docker - https://registry.hub.docker.com/r/postgis/postgis/
- Debian / Ubuntu - https://apt.postgresql.org (currently at 3.1.4 and PostgreSQL 14RC1 PostGIS 3.1.4)
- Yum (CentOS, Redhat EL, Scientific Linux): https://yum.postgresql.org (has 3.1.4 for PostgreSQL 12,13,14)
-
Windows ( part of application stackbuilder): https://postgis.net/windows_downloads/ -- (has fresh updates for PostgreSQL 11,12,13,14 with GEOS 3.10, GDAL 3.2, Proj 7.1.1, PostGIS 3.2, SFCGAL 1.4.0)(Experimental Builds section builds on each commit)
-
The manual - https://postgis.net/documentation
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 RASTERPostGIS 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;