book

PostGIS in Action, Second Edition

Name: PostGIS in Action, Second Edition
ISBN: 9781617291395

by Regina O. Obe, Leo S. Hsu

April 2015

Beginner to intermediate

600 pages

18h 13m

English

Manning Publications

Read now

Unlock full access

PostGIS in Action, Second Edition
Regina O. Obe and Leo S. Hsu
Copyright
Dedication
Brief Table of Contents
Table of Contents
Praise for the First Edition of PostGIS in Action
Foreword
Preface
Acknowledgments
About this Book
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

Part 1. Introduction to PostGIS
Chapter 1. What is a spatial database?
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
Chapter 2. Spatial data types
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
Chapter 3. Spatial reference system considerations
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
Chapter 4. Working with real data
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
Chapter 5. Using PostGIS on the desktop
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
Chapter 6. Geometry and geography functions
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
Chapter 7. Raster functions
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
Chapter 8. PostGIS TIGER geocoder
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
Chapter 9. Geometry relationships
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
Part 2. Putting PostGIS to work
Chapter 10. Proximity analysis
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
Chapter 11. Geometry and geography processing
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
Chapter 12. Raster processing
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
Chapter 13. Building and using topologies
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
Chapter 14. Organizing spatial data
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
Chapter 15. Query performance tuning
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
Part 3. Using PostGIS with other tools
Chapter 16. Extending PostGIS with pgRouting and procedural languages
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
Chapter 17. Using PostGIS in web applications
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
Appendix A. Additional resources
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
Appendix B. Installing, compiling, and upgrading
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
Appendix C. SQL primer
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
Appendix D. PostgreSQL features
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
Index
SYMBOLABCDEFGHIJK
L
MNOPQRSTUVWXY
List of Figures
List of Tables
List of Listings

Content preview from PostGIS in Action, Second Edition

Chapter 15. Query performance tuning

This chapter covers:

Planner basics

Reading plans

Common query patterns

Spatial processing for better performance

Influencing plans

When dealing with several tables at once—especially large ones—tuning queries becomes a major consideration. The way you write your queries is also important. Two queries can return exactly the same data, but one can take a hundred times longer to finish. The complexity of spatial objects, memory allocation, and even storage affects performance.

In this chapter, you’ll learn

How to use the planner and read query plans
How to write efficient queries
How to monitor query performance
How to organize spatial data to improve query performance

Much of this information has more to ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781617291395Publisher Support Publisher Website Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

PostGIS in Action, Second Edition

by Regina O. Obe, Leo S. Hsu

Chapter 15. Query performance tuning

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.