book

PostGIS in Action, Second Edition

by Leo S. Hsu, Regina O. Obe

April 2015

Beginner to intermediate

600 pages

18h 13m

English

Manning Publications

Read now

Unlock full access

Regina O. Obe and Leo S. Hsu
Who should read this book?GIS practitioners and programmersDB practitionersScientists, researchers, educators, and engineersRoadmapPart 1: Learning PostGISPart 2: Putting PostGIS to workPart 3: Using PostGIS with other toolsAppendixesCode and other conventionsCode downloadsAuthor OnlineAbout the titleAbout the cover illustration

1.1. Thinking spatially1.2. Introducing PostGIS1.2.1. Why PostGIS1.2.2. Alternatives to PostGIS1.2.3. Installing PostGIS1.3. Spatial data types1.3.1. Geometry type1.3.2. Geography type1.3.3. Raster type1.3.4. Topology type1.4. Hello real world1.4.1. Digesting the problem1.4.2. Modeling1.4.3. Loading data1.4.4. Writing the query1.4.5. Viewing spatial data with OpenJump1.5. Summary
2.1. Type modifiers2.1.1. Subtype type modifiers2.1.2. Spatial reference identifier2.2. Geometry2.2.1. Points2.2.2. Linestrings2.2.3. Polygons2.2.4. Collection geometries2.2.5. The M coordinate2.2.6. The Z coordinate2.2.7. Polyhedral surfaces and TINs2.2.8. Curved geometries2.2.9. Spatial catalog for geometry2.2.10. Managing geometry columns2.3. Geography2.3.1. Differences between geography and geometry2.3.2. Spatial catalogs for geography2.4. Raster2.4.1. Properties of rasters2.4.2. Creating rasters2.4.3. Spatial catalog for rasters2.5. Summary
3.1. Spatial reference systems: what are they?3.1.1. Geoids3.1.2. Ellipsoids3.1.3. Datum3.1.4. Coordinate reference system3.1.5. Spatial reference system essentials3.1.6. Projections3.2. Selecting a spatial reference system for storing data3.2.1. Pros and cons of using EPSG:43263.2.2. Geography data type for EPSG:43263.2.3. Mapping just for presentation3.2.4. Covering the globe when distance is a concern3.3. Determining the spatial reference system of source data3.3.1. Guessing at a spatial reference system3.3.2. When the spatial reference system is missing from spatial_ref_sys table3.4. Summary
4.1. General utilities4.1.1. PostgreSQL built-in tools4.1.2. Downloading files4.1.3. Extracting files4.2. Importing and exporting shapefiles4.2.1. Importing with shp2pgsql4.2.2. Importing and exporting with shp2pgsql-gui4.2.3. Exporting with pgsql2shp4.3. Importing and exporting vector data with ogr2ogr4.3.1. Environment variables4.3.2. Ogrinfo4.3.3. Importing with ogr2ogr4.3.4. Exporting with ogr2ogr4.4. Importing OpenStreetMap data with osm2pgsql4.4.1. Getting OSM data4.4.2. Loading OSM-formatted data with osm2pgsql4.5. Importing and exporting raster data4.5.1. Using gdalinfo to inspect rasters4.5.2. Importing raster data with raster2pgsql4.5.3. Gdal_translate and gdalwarp4.5.4. Using PostgreSQL functions to output raster data4.6. Summary
5.1. Desktop viewing tools at a glance5.1.1. Capsule reviews5.1.2. Spatial database support5.1.3. Format support5.1.4. Web services supported5.2. OpenJUMP workbench5.2.1. OpenJUMP feature summary5.2.2. PostGIS support5.2.3. Register data source5.2.4. Rendering PostGIS geometries5.2.5. Exporting data5.3. QGIS5.3.1. Installing QGIS5.3.2. Using QGIS with PostGIS5.3.3. Importing and exporting layers5.4. uDig5.4.1. Using uDig with PostGIS5.4.2. Connecting to PostGIS5.4.3. Viewing and filtering PostGIS data5.5. gvSIG5.5.1. Using gvSIG with PostGIS5.5.2. Exporting data5.6. Summary
6.1. Output functions6.1.1. Well-known binary (WKB) and well-known text (WKT)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. Extensible 3D Graphics (X3D)6.1.7. Examples of output functions6.1.8. Geohash6.2. Constructor functions6.2.1. Creating geometries from text and binary formats6.2.2. Creating geographies from text and binary formats6.2.3. Using text or binary representations as function arguments6.3. Accessor and setter functions6.3.1. Spatial reference identifiers6.3.2. Transforming geometry to different spatial references6.3.3. Using transformation with the geography type6.3.4. Geometry type functions6.3.5. Geometry and coordinate dimensions6.3.6. Retrieving coordinates6.3.7. Checking geometry validity6.3.8. Number of points that defines a geometry6.4. Measurement functions6.4.1. Geometry planar measurements6.4.2. Geodetic measurements6.5. Decomposition functions6.5.1. Bounding box of geometries6.5.2. Boundaries and converting polygons to linestrings6.5.3. Centroid and point on surface6.5.4. Returning points defining a geometry6.5.5. Decomposing multi-geometries and geometry collections6.6. Composition functions6.6.1. Making points6.6.2. Making polygons6.6.3. Promoting single geometries to multi-geometries6.7. Simplification functions6.7.1. Grid snapping and coordinate rounding6.7.2. Simplification6.8. Summary
7.1. Raster terminology7.2. Raster constructors7.2.1. Converting geometries to rasters with ST_AsRaster7.2.2. Loading rasters with raster2pgsql7.2.3. Constructing rasters from scratch: ST_MakeEmptyRaster and ST_AddBand7.2.4. Setting pixels: ST_SetValue and ST_SetValues7.2.5. Creating rasters from other rasters7.2.6. Converting other raster formats with ST_FromGDALRaster7.3. Raster output functions7.3.1. ST_AsPNG, ST_AsJPEG, and ST_AsTiff7.3.2. Output using ST_AsGDALRaster7.3.3. Using psql to export rasters7.4. Raster accessors and setters7.4.1. Basic raster metadata properties7.4.2. Pixel statistics7.4.3. Pixel value accessors7.4.4. Band metadata setters7.5. Georeferencing functions7.5.1. Metadata setters7.5.2. Processing functions7.6. Reclassing functions7.7. Polygonizing functions7.7.1. ST_ConvexHull7.7.2. ST_Envelope7.7.3. ST_Polygon7.7.4. ST_MinConvexHull7.8. Summary
8.1. Installing the PostGIS TIGER geocoder8.2. Loading TIGER data8.2.1. Configuration tables8.2.2. Loading nation and state data8.3. Normalizing addresses8.3.1. Using normalize_address8.3.2. Using the PAGC address normalizer8.4. Geocoding8.4.1. Geocoding using address text8.4.2. Geocoding using normalized addresses8.4.3. Batch geocoding8.5. Reverse geocoding8.6. Summary
9.1. Bounding box and geometry comparators9.1.1. The bounding box9.1.2. Bounding box comparators9.2. Relating two geometries9.2.1. Interior, exterior, and boundary of a geometry9.2.2. Intersections9.2.3. A house plan model9.2.4. Contains and within9.2.5. Covers and covered by9.2.6. Contains properly9.2.7. Overlapping geometries9.2.8. Touching geometries9.2.9. Crossing geometries9.2.10. Disjoint geometries9.3. The faces of equality: geometry9.3.1. Spatial equality versus geometric equality9.3.2. Bounding-box equality9.4. Underpinnings of relationship functions9.4.1. The intersection matrix9.4.2. Using ST_Relate9.5. Summary
10.1. Nearest neighbor searches10.1.1. Which places are within X distance?10.1.2. Using ST_DWithin and ST_Distance for N closest results10.1.3. Using ST_DWithin and DISTINCT ON to find closest locations10.1.4. Intersects with tolerance10.1.5. Finding N closest places using KNN distance bounding-box operators10.1.6. Combining KNN distance-box operators with ST_Distance10.1.7. Using window functions to find closest N places10.2. Using KNN with geography types10.3. Geotagging10.3.1. Tagging data to a specific region10.3.2. Linear referencing: snapping points to the closest linestring10.4. Summary
11.1. Using spatial aggregate functions11.1.1. Creating a multipolygon from many multipolygon records11.1.2. Creating linestrings from points11.2. Clipping, splitting, tessellating11.2.1. Clipping11.2.2. Splitting11.2.3. Tessellating11.3. Breaking linestrings into smaller segments11.3.1. Segmentizing linestrings11.3.2. Creating two-point linestrings from many-point linestrings11.3.3. Breaking linestrings at point junctions11.4. Translating, scaling, and rotating geometries11.4.1. Translating11.4.2. Scaling11.4.3. Rotating11.5. Using geometry functions to manipulate and create geographies11.5.1. Cast-safe functions11.5.2. Transformation-recommended functions11.6. Summary
12.1. Loading and preparing data12.2. Forming larger rasters using spatial aggregate functions12.2.1. Reconstituting tiled files12.2.2. Carving out areas of interest using clipping and unioning12.2.3. Using specific expression types with ST_Union12.3. Working with bands12.3.1. Using ST_AddBand to form multiband rasters from single-band rasters12.3.2. Using ST_Band to process a subset of bands12.4. Tiling rasters12.5. Raster and geometry intersections12.5.1. Pixel stats12.5.2. Adding a Z coordinate to a 2D linestring using ST_Value12.5.3. Converting 2D polygon to 3D polygon12.6. Raster statistics12.6.1. Extruding pixel values12.6.2. Raster statistics functions12.7. Map algebra12.7.1. Choosing between expression or callback function12.7.2. Using a single-band map algebra expression12.7.3. Using a single-band map algebra function12.7.4. Map algebra with neighborhoods12.8. Summary
13.1. What topology is13.2. Using topologies13.2.1. Installing the topology extension13.2.2. Creating a topology13.2.3. The topogeometry type13.2.4. Recap of using topologies13.3. Topology of Victoria, BC13.3.1. Creating the Victoria topology13.3.2. Adding primitives to a topology13.3.3. Creating topogeometries13.4. Fixing topogeometry issues by editing topology primitives13.4.1. Removing faces by removing edges13.4.2. Checking for shared faces13.4.3. Editing topogeometries13.5. Inserting and editing large data sets13.6. Simplifying with topology in mind13.7. Topology validation and summary functions13.8. Summary
14.1. Spatial storage approaches14.1.1. Heterogeneous columns14.1.2. Homogeneous columns14.1.3. Typmod versus constraints14.1.4. Table inheritance14.2. Modeling a real city14.2.1. Modeling using heterogeneous geometry columns14.2.2. Modeling using homogeneous geometry columns14.2.3. Modeling using inheritance14.3. Making auto-updateable views14.4. Using rules and triggers14.4.1. Rules versus triggers14.4.2. Using rules14.4.3. Using triggers14.5. Summary
15.1. The query planner15.1.1. Different kinds of spatial queries15.1.2. Common table expressions and how they affect plans15.2. Planner statistics15.3. Using explain to diagnose problems15.3.1. Text explain versus pgAdmin graphical explain15.3.2. The plan with no index15.4. Planner and indexes15.4.1. The plan with a spatial index15.4.2. Options for defining indexes15.5. Common SQL patterns and how they affect plans15.5.1. SELECT subqueries15.5.2. FROM subqueries and basic CTEs15.5.3. Window functions and self-joins15.5.4. Laterals15.6. System and function settings15.6.1. Key system variables that affect plan strategy15.6.2. Function-specific settings15.7. Optimizing spatial data15.7.1. Fixing invalid geometries15.7.2. Reducing the number of vertices by simplification15.7.3. Clustering15.8. Summary
16.1. Solving network routing problems with pgRouting16.1.1. Installing pgRouting16.1.2. Basic navigation16.1.3. Traveling salesman16.2. Extending PostgreSQL with PLs16.2.1. Basic installation of PLs16.2.2. What you can do with PLs16.3. PL/R16.3.1. Getting started with PL/R16.3.2. Saving data sets and plotting16.3.3. Using R packages in PL/R16.3.4. Converting geometries into R spatial objects and plotting spatial objects16.3.5. Outputting plots as binaries16.4. PL/Python16.4.1. Installing PL/Python16.4.2. Writing a PL/Python function16.4.3. Using Python packages16.4.4. Geocoding example16.5. PL/V8, CoffeeScript, and LiveScript16.5.1. Installing PL/V816.5.2. Using other JavaScript libraries and functions in PL/V816.5.3. Using PL/V8 to write map algebra functions16.6. Summary
17.1. Limitations of conventional web technologies17.2. Mapping servers17.2.1. Platform considerations17.2.2. OGC web service support17.2.3. Supported data sources17.3. Mapping clients17.3.1. Proprietary services17.4. Using MapServer17.4.1. Installing MapServer17.4.2. Creating WMS and WFS services17.4.3. Calling a mapping service using a reverse proxy17.5. Using GeoServer17.5.1. Installing GeoServer17.5.2. Setting up PostGIS workspaces17.5.3. Accessing PostGIS layers via GeoServer WMS/WFS17.6. Basics of OpenLayers and Leaflet17.6.1. OpenLayers primer17.6.2. Leaflet primer17.6.3. Synopsis of the three different APIs17.7. Displaying data with PostGIS queries and web scripting17.7.1. Displaying PostGIS rasters using raster queries17.7.2. Using PostGIS and PostgreSQL geometry output functions17.8. Summary
A.1. Planet sitesA.2. Open source tools and offeringsA.2.1. Self-contained GIS suites that include PostGISA.2.2. Open source desktop toolsA.2.3. Open source extract-transform-load (ETL)A.3. Proprietary vendors that support PostGISA.4. Places to get free data
B.1. Installing PostgreSQL and PostGISB.1.1. Desktop Linux, Windows, Mac OS X using one-click installersB.1.2. Installing on a Linux server (Red Hat EL, CentOS) using YUMB.1.3. Mac OS X–specific installersB.1.4. PostgreSQL APT repositoryB.1.5. Other available binaries and distrosB.1.6. Compiling and installing from PostGIS sourceB.2. Creating a PostGIS databaseB.2.1. Spatializing a PostgreSQL 9.0 or lower database or PostGIS without rasterB.3. Upgrading PostGISB.3.1. PostGIS soft upgrade using extensionsB.3.2. Upgrading PostGIS from 1.X to 2.X
C.1. information_schemaC.2. Querying data with SQLC.2.1. SELECT, FROM, WHERE, and ORDER BY clausesC.2.2. Using subselectsC.2.3. JOINsC.2.4. SetsC.2.5. Using SQL aggregatesC.2.6. Window functions and window aggregatesC.2.7. LATERALsC.3. UPDATE, INSERT, and DELETEC.3.1. UPDATEC.3.2. INSERTC.3.3. DELETE
D.1. What makes PostgreSQL special?D.1.1. PostgreSQL’s unique featuresD.1.2. Basic enterprise featuresD.1.3. Advanced enterprise featuresD.1.4. More features in PostgreSQL 9.3, 9.4, and coming in 9.5D.2. Useful PostgreSQL resourcesD.2.1. General resourcesD.2.2. PostgreSQL-specific toolsD.3. Connecting to a PostgreSQL serverD.3.1. Core configuration filesD.3.2. Launching psqlD.3.3. Launching pgAdmin IIID.3.4. Connection difficultiesD.3.5. Enabling advanced administration for pgAdmin IIID.4. Controlling access to dataD.4.1. Connection rulesD.4.2. Users and groups (roles)D.4.3. Rights managementD.5. Backup and restoreD.5.1. BackupD.5.2. RestoreD.5.3. Setting up automated jobs for backupD.6. Data structures and objectsD.6.1. PostgreSQL objectsD.6.2. Built-in data typesD.6.3. Anatomy of a database functionD.6.4. Defining custom data typesD.6.5. Creating tables and viewsD.7. Writing functions in SQLD.7.1. When to use SQL functionsD.7.2. Creating an SQL functionD.7.3. RulesD.7.4. Creating aggregate functionsD.8. Writing functions in PL/pgSQLD.8.1. When to use PL/pgSQL functionsD.8.2. Creating a PL/pgSQL functionD.8.3. Creating triggersD.9. Index performanceD.9.1. B-tree index gotchasD.9.2. Functional index gotchas
SYMBOLABCDEFGHIJK
MNOPQRSTUVWXY

Content preview from PostGIS in Action, Second Edition

Appendix C. SQL primer

PostgreSQL supports almost the whole ANSI SQL 92, 1999 standard logic, as well as many of the SQL:2003, SQL:2006, SQL:2008 constructs, and some of the SQL:2011 constructs. In this appendix, we’ll cover some of these as well as some PostgreSQL-specific SQL language extensions. Because we’ll remain fairly focused on standard functionality, the content in this appendix is also applicable to other standards-compliant relational databases.

C.1. information_schema

The information_schema is a catalog introduced in SQL 92 and enhanced in each subsequent version of the spec. Although it’s a standard, sadly most commercial and open source databases don’t completely support it. We know that the following common databases do: PostgreSQL ...