List of Listings

Chapter 1. What is a spatial database?

Listing 1.1. Set up fastfoods and franchises lookup

Listing 1.2. Using the geometry data type to store data

Listing 1.3. Using the geography data type to store data

Listing 1.4. Using the geometry data type to store roads data

Listing 1.5. List franchise name, count of restaurants on a principal highway

Listing 1.6. Return the principal highway that has the most restaurants and how many

Chapter 2. Geometry types

Listing 2.1. Adding points

Listing 2.2. Adding linestrings

Listing 2.3. Forming geometrycollections from constituent geometries

Listing 2.4. Building circularstrings

Listing 2.5. Creating curved polygons

Chapter 3. Organizing spatial data

Listing 3.1. Code to partition roads into various states

Listing 3.2. Region tagging and clipping data to a specific arrondissement

Listing 3.3. Breaking our data into separate tables with homogeneous geometry columns

Listing 3.4. Creating a child table

Listing 3.5. Adding another child and additional constraints

Listing 3.6. Adopt an orphan

Listing 3.7. Making views updateable with rules

Listing 3.8. PL/PGSQL BEFORE INSERT trigger function to redirect insert

Listing 3.9. Trigger that dynamically creates tables as needed

Listing 3.10. Binding same trigger function to multiple tables

Chapter 4. Geometry functions

Listing 4.1. Example use of ST_SRID

Listing 4.2. Differences between ST_GeometryType and GeometryType

Listing 4.3. Coordinate and geometry dimensions of various geometries

Listing 4.4. Example of ST_NPoints and ST_NumPoints

Listing 4.5. Calculating the length of a multilinestring with different spheroids

Listing 4.6. Comparing spheroid and sphere calculations in geography

Listing 4.7. ST_Box2D and casting a box to a geometry

Listing 4.8. Example of ST_Envelope

Listing 4.9. Examples of ST_Boundary

Listing 4.10. Centroid of various geometries

Listing 4.11. Example using ST_GeometryN with generate_series

Listing 4.12. Point constructor functions

Listing 4.13. ST_Polygonize, ST_BuildArea, ST_MakePolygon

Chapter 5. Relationships between geometries

Listing 5.1. Return our sales region diced up

Listing 5.2. Create sample geometries to exercise intersect relationships

Listing 5.3. How is ST_Covers different from ST_Contains?

Listing 5.4. How is ST_ContainsProperly different from ST_Contains?

Listing 5.5. What’s left of the polygon and line after clipping

Listing 5.6. Bisecting a polygon using the knife trick

Listing 5.7. Find the closest road to each location; search 10 kilometers out

Listing 5.8. Find the closest two roads to each station; search 1 kilometer out.

Listing 5.9. ST_Box2D and geometry

Listing 5.10. ST_OrderingEquals equality versus ST_Equals

Listing 5.11. DISTINCT is not always DISTINCT

Listing 5.12. A count DISTINCT is not always a DISTINCT count.

Listing 5.13. Guaranteeing unique geometries

Listing 5.14. ST_Equals testing—a self-intersecting polygon is not equal to itself.

Listing 5.15. ST_Relate In action

Chapter 6. Spatial reference system considerations

Listing 6.1. Determing WGS 84 UTM SRID of a geometry

Listing 6.2. Using functional indexes

Chapter 7. Working with real data

Listing 7.1. Generate DDL to rename columns

Listing 7.2. Converting data from native format to more optimized format

Listing 7.3. Putting in indexing and preparation for querying

Listing 7.4. OGR2OGR supported formats list

Listing 7.5. Displaying ogrinfo about GPX file

Listing 7.6. Loading data from GPX

Listing 7.7. Use ogrinfo to list fields for a Personal GeoDatabase layer

Listing 7.8. Export PostGIS table and query to KML

Listing 7.9. Export PostGIS table and query to MapInfo tab format

Chapter 8. Techniques to solve spatial problems

Listing 8.1. Basic ST_DWithin query

Listing 8.2. Create a simple units conversion table

Listing 8.3. Which bridges are within a half-mile of San Francisco?

Listing 8.4. Example SQL function to convert between two units

Listing 8.5. Using the unit conversion function

Listing 8.6. Compare distance measurement accuracy of various spatial refs

Listing 8.7. Distances between cities in kilometers using various projections

Listing 8.8. Area calculations for large objects

Listing 8.9. Using multiple spatial reference ids

Listing 8.10. Compare areas of 10-meter UTM radius buffers around cities

Listing 8.11. Create dummy observation data

Listing 8.12. Query to snap points to linestrings—version 1

Listing 8.13. Query to snap points to linestring without subquery—version 2

Listing 8.14. Geocode an address to a point

Listing 8.15. Create line path from point observations

Listing 8.16. Make two-point lines from linestrings

Listing 8.17. Make two-point lines from multilinestrings or linestrings

Listing 8.18. Function to cut linestring at point junctions

Listing 8.19. Analysis pre-dissolving records

Listing 8.20. Create one record per city

Listing 8.21. Divide the United States into quadrants.

Listing 8.22. Bisect the state of Idaho

Listing 8.23. upgis_slicegeometry—cuts a geometry into equal areas

Listing 8.24. Generate a rectangle and a hexagonal grid centered in the United States.

Listing 8.25. Example of scaling a hexagon to different sizes

Listing 8.26. Combining scaling and translation to maintain the centroid

Listing 8.27. Example of ST_Rotate rotating a hexagon from 0 to 270 degrees

Listing 8.28. ST_Rotate in combination with ST_Translate to rotate about a centroid

Chapter 9. Performance tuning

Listing 9.1. Index, vacuum, explain

Listing 9.2. Subselects gone too far

Listing 9.3. Explain plan of subselect gone too far query

Listing 9.4. Query plan of count of streets and min length with no subselects

Listing 9.5. Rank number results using the self-join approach (pre-PostgreSQL 8.4)

Listing 9.6. Using window frame to number results—PostgreSQL 8.4+

Listing 9.7. Simplified state versus non-simplified

Listing 9.8. Simplify on the fly and still use an index

Listing 9.9. Filter rings function and its application

Listing 9.10. Collecting points into multipoint bunches

Chapter 10. Enhancing SQL with add-ons

Listing 10.1. Example of the geocode function

Listing 10.2. Listing specific elements of addy in geocode results

Listing 10.3. Batch geocoding with the geocode function

Listing 10.4. Saving PostgreSQL data in R data format with PL/R

Listing 10.5. Plotting PostgreSQL data with R

Listing 10.6. Function to get list of supported rgdal raster formats

Listing 10.7. Demonstrating subset and c functions in R

Listing 10.8. Reading metadata from image files with rgdal

Listing 10.9. Plotting linestrings with R

Listing 10.10. Compute sum of range of numbers

Listing 10.11. PL/Python function to import Excel data

Chapter 11. Using PostGIS in web applications

Listing 11.1. Map with INCLUDEs

Listing 11.2. Sample layer from layers.inc.map

Listing 11.3. Snippet of a reverse proxy in C#

Listing 11.4. PostGIS MapServer layers using proxy

Listing 11.5. OpenLayers general setup

Listing 11.6. Revising the map to have Yahoo as an option

Listing 11.7. Adding WMS layers to OpenLayers

Listing 11.8. geoextnewenglandwin.htm: basic structure of page

Listing 11.9. geoextnewenglandwin.js: using GeoExt to display OL map in Ext window

Listing 11.10. Core pieces of datafeeder.php

Listing 11.11. Smarty data_json.tpl file

Listing 11.12. KML template to format in KML format

Listing 11.13. Adding a feature grid window

Listing 11.14. PHP find all roads within one mile of a requested lon lat

Chapter 12. Using PostGIS in a desktop environment

Listing 12.1. SQL art

Chapter 13. PostGIS raster

Listing 13.1. Getting general summary info about rasters in the Kauai table

Listing 13.2. Creating three clones of Pele in Kauai

Listing 13.3. Intersection of raster with geometry

Listing 13.4. Adding a Z coordinate to a 2D linestring

Listing 13.5. Reclassify Pele’s pixels

Listing 13.6. Exporting raster data from PostGIS raster type

Listing 13.7. Example MapServer PostGIS raster layer

Appendix B. Installing, compiling, and upgrading

Listing B.1. Creating a template_postgis for 1.3

Listing B.2. Creating template_postgis for 1.5

Listing B.3. Hard upgrade

Appendix C. SQL primer

Listing C.1. List all columns in hello schema

Listing C.2. Subselects used in a table alias

Listing C.3. Same statement written using the WITH clause

Listing C.4. Same statement written using a correlated subquery

Listing C.5. Combining water and land features

Listing c.6. INTERSECT compared to INNER JOIN

Listing C.7. Combining standard SQL and spatial aggregates

Listing C.8. Find roads within 1 km from each police station and number sequentially

Listing C.9. Multivalue row INSERT: two insert facilities

Listing C.10. Simple value INSERT using SELECT instead of VALUES

Listing C.11. Construct polygons from line work and insert into polygon table

Listing C.12. Example SELECT INTO and CREATE TABLE

Appendix D. PostgreSQL features

Listing D.1. Example pg_hba.conf—trust all local connections

Listing D.2. Common GRANT options

Listing D.3. Common backup statements

Listing D.4. Common restore statements

Listing D.5. Create a simple type and use it

Listing D.6. Creating a table and a view

Listing D.7. Example SQL function returns square

Listing D.8. Making a view updateable

Listing D.9. Creating first and last aggregate functions

Listing D.10. Putting our first and last to work

Listing D.11. utmzone

Listing D.12. Trigger function applied to geography table inPL/PgSQL