PostGIS in Action

PostGIS in Action, Third Edition
brief contents
contents
foreword
preface
acknowledgments
about this book
Who should read this book?
How this book is organized: a roadmap
About the code
liveBook discussion forum
About the title
about the Author
about the cover illustration
Part 1—Introduction to PostGIS
1 What is a spatial database?
1.1 Thinking spatially
1.2 Introducing PostGIS
1.2.1 Why PostGIS
1.2.2 Standards conformance
1.2.3 PostGIS is powerful
1.2.4 Built on top of PostgreSQL
1.2.5 Free—as in money
1.2.6 Free—as in freedom
1.2.7 Alternatives to PostGIS
1.3 Installing PostGIS
1.3.1 Verifying versions of PostGIS and PostgreSQL
1.4 Spatial data types
1.4.1 Geometry type
1.4.2 Geography type
1.4.3 Raster type
1.4.4 Topology type
1.5 Hello real world
1.5.1 Digesting the problem
1.5.2 Modeling
1.5.3 Loading data
1.5.4 Writing the query
1.5.5 Viewing spatial data with OpenJump
Summary
2 Spatial data types
2.1 Type modifiers
2.1.1 Subtype type modifiers
2.1.2 Spatial reference identifier
2.2 Geometry
2.2.1 Points
2.2.2 Linestrings
2.2.3 Polygons
2.2.4 Collection geometries
2.2.5 The M coordinate
2.2.6 The Z coordinate
2.2.7 Polyhedral surfaces and TINs
2.2.8 Generating TINs
2.2.9 Curved geometries
2.2.10 Spatial catalog for geometry
2.3 Geography
2.3.1 Differences between geography and geometry
2.3.2 Spatial catalogs for geography
2.4 Raster
2.4.1 Properties of rasters
2.4.2 Creating rasters
2.4.3 Spatial catalog for rasters
Summary
3 Spatial reference systems
3.1 Spatial reference systems: What are they?
3.1.1 Geoids
3.1.2 Ellipsoids
3.1.3 Datum
3.1.4 Coordinate reference system
3.1.5 Spatial reference system essentials
3.1.6 Projections
3.2 Selecting a spatial reference system for storing data
3.2.1 Pros and cons of using EPSG:4326
3.2.2 Geography data type for EPSG:4326
3.2.3 Mapping just for presentation
3.2.4 Covering the globe when distance is a concern
3.3 Determining the spatial reference system of source data
3.3.1 Guessing at a spatial reference system
3.3.2 When the SRS is missing from the spatial_ref_sys table
3.4 History of PROJ support in PostGIS
3.4.1 PROJ 4
3.4.2 PROJ 5
3.4.3 PROJ 6
3.4.4 PROJ 7
3.4.5 PROJ 8 and beyond
Summary
4 Working with real data
4.1 PostgreSQL built-in tools
4.1.1 Psql
4.1.2 pgAdmin4
4.1.3 Pg_dump and pg_restore
4.2 Downloading files
4.3 Extracting files
4.4 Importing and exporting shapefiles
4.4.1 Importing with shp2pgsql
4.4.2 Importing and exporting with shp2pgsql-gui
4.4.3 Exporting with pgsql2shp
4.5 Importing and exporting vector data with ogr2ogr
4.5.1 Environment variables in ogr2ogr
4.5.2 Ogrinfo
4.5.3 Importing with ogr2ogr
4.5.4 Exporting with ogr2ogr
4.6 Querying external data using PostgreSQL foreign data wrappers
4.6.1 File_fdw foreign data wrapper
4.6.2 Ogr_fdw foreign data wrapper
4.6.3 Converting hstore tags to jsonb
4.7 Importing raster data with raster2pgsql
4.7.1 Raster2pgsql command-line switches
4.7.2 Raster2pgsql supported formats
4.7.3 Loading a single file with raster2pgsql
4.7.4 Loading multiple files and tiling in shell script
4.7.5 Using PostgreSQL functions to output raster data
4.8 Exporting raster data with GDAL
4.8.1 Using gdalinfo to inspect rasters
4.8.2 Gdal_translate and gdalwarp
Summary
5 Using PostGIS on the desktop
5.1 Desktop viewing tools at a glance
5.1.1 OpenJUMP
5.1.2 QGIS
5.1.3 gvSIG
5.1.4 Jupyter Notebook and JupyterLab
5.1.5 Spatial database support
5.1.6 Format support
5.1.7 Web services supported
5.2 OpenJUMP
5.2.1 OpenJUMP feature summary
5.2.2 Installing OpenJUMP
5.2.3 Ease of use
5.2.4 OpenJUMP plug-ins
5.2.5 OpenJUMP scripting
5.2.6 OpenJUMP format support
5.2.7 PostGIS support
5.2.8 Registering data sources
5.2.9 Rendering PostGIS geometries
5.2.10 Exporting data
5.3 QGIS
5.3.1 Installing QGIS
5.3.2 Using QGIS with PostGIS
5.4 GvSIG
5.4.1 Using gvSIG with PostGIS
5.4.2 Exporting data
5.5 JupyterLab and Jupyter Notebook
5.5.1 Installing Jupyter
5.5.2 Launching Jupyter Notebook
5.5.3 Launching JupyterLab
5.5.4 Creating a Python notebook
5.5.5 Magic commands
5.5.6 Performing raw queries with Jupyter Notebook
5.5.7 Using GeoPandas, Shapely, and Matplotlib to work with spatial data
5.5.8 Viewing data on a map with folium
Summary
6 Geometry and geography functions
6.1 Output functions
6.1.1 Well-known text (WKT) and well-known binary (WKB)
6.1.2 Keyhole Markup Language (KML)
6.1.3 Geography Markup Language (GML)
6.1.4 Geometry JavaScript Object Notation (GeoJSON)
6.1.5 Scalable Vector Graphics (SVG)
6.1.6 Mapbox Vector Tiles (MVT) and protocol buffers
6.1.7 Tiny WKB (TWKB)
6.1.8 Extensible 3D Graphics (X3D)
6.1.9 Examples of output functions
6.1.10 Geohash
6.2 Constructor functions
6.2.1 Creating geometries from text and binary formats
6.2.2 Creating geographies from text and binary formats
6.2.3 Using text or binary representations as function arguments
6.3 Accessor and setter functions
6.3.1 Spatial reference identifiers
6.3.2 Transforming geometry to different spatial references
6.3.3 Using transformation with the geography type
6.3.4 Geometry type functions
6.3.5 Geometry and coordinate dimensions
6.3.6 Retrieving coordinates
6.3.7 Checking geometry validity
6.3.8 Number of points that define a geometry
6.4 Measurement functions
6.4.1 Geometry planar measurements
6.4.2 Geodetic measurements
6.5 Decomposition functions
6.5.1 Bounding box of geometries
6.5.2 Boundaries and converting polygons to linestrings
6.5.3 Centroid, median, and point on surface
6.5.4 Returning points defining a geometry
6.5.5 Decomposing multi-geometries and geometry collections
6.6 Composition functions
6.6.1 Making points
6.6.2 Making polygons
6.6.3 Promoting single geometries to multi-geometries
6.7 Simplification functions
6.7.1 Grid snapping and coordinate rounding
6.7.2 Simplification
Summary
7 Raster functions
7.1 Raster terminology
7.2 Raster constructors
7.2.1 Converting geometries to rasters with ST_AsRaster
7.2.2 Loading rasters with raster2pgsql
7.2.3 Constructing rasters from scratch: ST_MakeEmptyRaster and ST_AddBand
7.2.4 Setting pixels: ST_SetValue and ST_SetValues
7.2.5 Creating rasters from other rasters
7.2.6 Converting other raster formats with ST_FromGDALRaster
7.3 Raster output functions
7.3.1 ST_AsPNG, ST_AsJPEG, and ST_AsTiff
7.3.2 Output using ST_AsGDALRaster
7.3.3 Using psql to export rasters
7.4 Raster accessors and setters
7.4.1 Basic raster metadata properties
7.4.2 Pixel statistics
7.4.3 Pixel value accessors
7.4.4 Band metadata setters
7.5 Georeferencing functions
7.5.1 Metadata setters
7.5.2 Processing functions
7.6 Reclassing functions
7.7 Polygonizing functions
7.7.1 ST_ConvexHull
7.7.2 ST_Envelope
7.7.3 ST_Polygon
7.7.4 ST_MinConvexHull
Summary
8 Spatial relationships
8.1 Bounding box and geometry comparators
8.1.1 The bounding box
8.1.2 Bounding box comparators
8.2 Relating two geometries
8.2.1 Interior, exterior, and boundary of a geometry
8.2.2 Intersections
8.2.3 A house plan model
8.2.4 Contains and within
8.2.5 Covers and covered by
8.2.6 Contains properly
8.2.7 Overlapping geometries
8.2.8 Touching geometries
8.2.9 The faces of equality: geometry
8.2.10 Underpinnings of relationship functions
Summary
Part 2—Putting PostGIS to work
9 Proximity analysis
9.1 Nearest neighbor searches
9.1.1 Which places are within X distance?
9.1.2 Using ST_DWithin and ST_Distance for N closest results
9.1.3 Using ST_DWithin and DISTINCT ON to find closest locations
9.1.4 Intersects with tolerance
9.1.5 Items between distances
9.1.6 Finding the N closest places using KNN distance operators
9.2 Using KNN with geography types
9.2.1 Using window functions to number the closest N places
9.3 Geotagging
9.3.1 Tagging data to a specific region
9.3.2 Linear referencing: snapping points to the closest linestring
9.3.3 PostGIS cluster window functions
Summary
10 PostGIS TIGER geocoder
10.1 Installing the PostGIS TIGER geocoder
10.2 Loading TIGER data
10.2.1 Configuration tables
10.2.2 Loading nation and state data
10.3 Normalizing addresses
10.3.1 Using normalize_address
10.3.2 Using the PAGC address normalizer
10.4 Geocoding
10.4.1 Geocoding using address text
10.4.2 Geocoding using normalized addresses
10.4.3 Geocoding intersections
10.4.4 Batch geocoding
10.5 Reverse geocoding
Summary
11 Geometry and geography processing
11.1 Using spatial aggregate functions
11.1.1 Creating a multipolygon from many multipolygon records
11.1.2 Creating linestrings from points
11.2 Clipping, splitting, tessellating
11.2.1 Clipping
11.2.2 Splitting
11.2.3 Tessellating
11.3 Breaking linestrings into smaller segments
11.3.1 Segmentizing linestrings
11.3.2 Creating two-point linestrings from many-point linestrings
11.3.3 Breaking linestrings at point junctions
11.4 Translating, scaling, and rotating geometries
11.4.1 Translating
11.4.2 Scaling
11.4.3 Rotating
11.5 Using geometry functions to manipulate and create geographies
11.5.1 Cast-safe functions
Summary
12 Raster processing
12.1 Loading and preparing raster data
12.2 Forming larger rasters using spatial aggregate functions
12.2.1 Reconstituting tiled files
12.2.2 Carving out areas of interest using clipping and unioning
12.2.3 Using specific expression types with ST_Union
12.3 Working with bands
12.3.1 Using ST_AddBand to form multiband rasters from single-band rasters
12.3.2 Using ST_Band to process a subset of bands
12.4 Tiling rasters
12.5 Raster and geometry intersections
12.5.1 Pixel stats
12.5.2 Adding a Z coordinate to a 2D linestring using ST_Value and ST_SetZ
12.5.3 Converting 2D polygons to 3D polygons
12.6 Raster statistics
12.6.1 Extruding pixel values
12.6.2 Raster statistics functions
12.7 Map algebra
12.7.1 Choosing between expression or callback function
12.7.2 Using a single-band map algebra expression
12.7.3 Using a single-band map algebra function
12.7.4 Map algebra with neighborhoods
Summary
13 Building and using topologies
13.1 What topology is
13.2 Using topologies
13.2.1 Installing the topology extension
13.2.2 Creating a topology
13.2.3 The topogeometry type
13.2.4 Recap of using topologies
13.3 Topology of Victoria, BC
13.3.1 Creating the Victoria topology
13.3.2 Adding primitives to a topology
13.3.3 Creating topogeometries
13.4 Fixing topogeometry issues by editing topology primitives
13.4.1 Removing faces by removing edges
13.4.2 Checking for shared faces
13.4.3 Editing topogeometries
13.5 Inserting and editing large data sets
13.6 Simplifying with topology in mind
13.7 Topology validation and summary functions
Summary
14 Organizing spatial data
14.1 Spatial storage approaches
14.1.1 Heterogeneous columns
14.1.2 Homogeneous columns
14.1.3 Typmod vs. constraints
14.1.4 Table inheritance
14.1.5 Table partitioning
14.2 Modeling a real city
14.2.1 Modeling using heterogeneous geometry columns
14.2.2 Modeling using homogeneous geometry columns
14.2.3 Modeling using partitioning
14.3 Making auto-updatable views
14.4 Using triggers and rules
14.4.1 Triggers
14.4.2 Using INSTEAD OF triggers
14.4.3 Using other triggers
Summary
15 Query performance tuning
15.1 The query planner
15.1.1 Different kinds of spatial queries
15.1.2 Common table expressions and how they affect plans
15.2 Planner statistics
15.3 Using explain to diagnose problems
15.3.1 Text explain vs. pgAdmin graphical explain
15.3.2 The plan with no index
15.4 Planner and indexes
15.4.1 The plan with a spatial index
15.4.2 Indexes
15.5 Common SQL patterns and how they affect plans
15.5.1 Subqueries in SELECT
15.5.2 FROM subqueries and basic CTEscommon table expressions (CTEs)
15.5.3 Window functions and self joins
15.5.4 Lateral joins
15.6 System and function settings
15.6.1 Key system variables that affect plan strategies
15.6.2 Function-specific settings
15.6.3 Encouraging parallel plans
15.7 Optimizing spatial data
15.7.1 Fixing invalid geometries
15.7.2 Reducing the number of vertices by simplification
15.7.3 Reducing the number of vertices by breaking geometries apart
15.7.4 Clustering
Summary
Part 3—Using PostGIS with other tools
16 Extending PostGIS with pgRouting and procedural languages
16.1 Solving network routing problems with pgRouting
16.1.1 Installing pgRouting
16.2 Extending PostgreSQL with PLs
16.2.1 Basic installation of PLs
16.2.2 What you can do with PLs
16.3 PL/R
16.3.1 Getting started with PL/R
16.3.2 What you can do with PL/R
16.3.3 Using R packages in PL/R
16.3.4 Converting geometries into R spatial objects and plotting spatial objects
16.3.5 Outputting plots as binaries
16.4 PL/Python
16.4.1 Installing PL/Python
16.4.2 Writing a PL/Python function
16.4.3 Using Python packages
16.4.4 Geocoding example
16.5 PL/V8: JavaScript in the database
16.5.1 Installing PL/V8
16.5.2 Enabling PL/V8 in a database
16.5.3 Using other JavaScript libraries and functions in PL/V8
16.5.4 Using PL/V8 to write map algebra functions
Summary
17 Using PostGIS in web applications
17.1 Limitations of conventional web technologies
17.2 Mapping servers
17.2.1 Lightweight mapping servers
17.2.2 Full mapping servers
17.3 Mapping clients
17.3.1 Proprietary services
17.4 Using MapServer
17.4.1 Installing MapServer
17.4.2 Security considerations
17.4.3 Creating WMS and WFS services
17.4.4 Calling a mapping service using a reverse proxy
17.5 Using GeoServer
17.5.1 Installing GeoServer
17.5.2 Setting up PostGIS workspaces
17.5.3 Accessing PostGIS layers via GeoServer WMS/WFS
17.6 Basics of OpenLayers and Leaflet
17.6.1 OpenLayers primer
17.6.2 Leaflet primer
17.6.3 Synopsis of the OpenLayers and Leaflet APIs
17.7 Displaying data with PostGIS queries and web scripting
17.7.1 Using PostGIS and PostgreSQL geometry output functions
17.7.2 Using PostGIS MVT output functionsMapbox Vector Tiles (MVT)
Summary
Appendix A—Additional resources
A.1 Planet sites
A.2 Open source tools and offerings
A.2.1 Self-contained GIS suites
A.2.2 Open source desktop tools
A.3 Open source extract-transform-load (ETL)
A.4 Places to get free data
Appendix B—Installing, compiling, and upgrading
B.1 Installing PostgreSQL and PostGIS
B.1.1 Using PostgreSQL and PostGIS in Docker
B.1.2 EnterpriseDB one-click installers
B.1.3 MacOS-specific installers
B.1.4 Installing on a Linux server (Red Hat EL, CentOS) using YUM
B.1.5 PostgreSQL Apt repository
B.1.6 Other available binaries and distros
B.1.7 Database as a service offerings for PostGIS
B.1.8 Compiling and installing from PostGIS source
B.2 Creating a PostGIS database
B.3 Upgrading PostGIS
B.3.1 PostGIS soft upgrade using extensions
B.3.2 Upgrading PostgreSQL and PostGIS using pg_upgrade
B.3.3 Upgrading PostGIS from 1.X to 2.X or 3.X
Appendix C—SQL primer
C.1 The information_schema
C.2 Querying data with SQL
C.2.1 SELECT, FROM, WHERE, and ORDER BY clauses
C.2.2 Indexes
C.2.3 Aliasing
C.2.4 Why use AS when you don’t need to
C.2.5 Using subselects
C.2.6 JOINs
C.3 UPDATE, INSERT, and DELETE
C.3.1 UPDATE
C.3.2 INSERT
C.3.3 DELETE
Appendix D—PostgreSQL features
D.1 What makes PostgreSQL special?
D.1.1 PostgreSQL’s unique features
D.1.2 Basic enterprise features
D.1.3 Advanced enterprise features
D.1.4 More features in PostgreSQL 12, 13, and 14
D.2 Useful PostgreSQL resources
D.2.1 General resources
D.2.2 PostgreSQL-specific tools
D.3 Connecting to a PostgreSQL server
D.3.1 Core configuration files
D.3.2 Launching psql
D.3.3 Launching pgAdmin
D.3.4 Connection difficulties
D.3.5 Enabling advanced administration for pgAdmin
D.4 Controlling access to data
D.4.1 Connection rules
D.4.2 Users and groups (rolesusers and groups (roles))
D.4.3 Rights management
D.5 Backup and restore
D.5.1 Backup
D.5.2 Restore
D.5.3 Setting up automated jobs for backup
D.6 Data structures and objects
D.6.1 PostgreSQL objects
D.6.2 Built-in data types
D.6.3 Anatomy of a database function
D.6.4 Defining custom data types
D.6.5 Creating tables and views
D.7 Writing functions in SQL
D.7.1 When to use SQL functions
D.7.2 Creating an SQL function
D.7.3 Rules
D.7.4 Creating aggregate functions
D.8 Writing functions in PL/pgSQL
D.8.1 When to use PL/pgSQL functions
D.8.2 Creating a PL/pgSQL function
D.8.3 Creating triggers
D.9 Index performance
D.9.1 B-tree index gotchas
D.9.2 Functional index gotchas
D.10 Computed columns
index
Symbols
Numerics
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z