List of Figures

Chapter 1. What is a spatial database?

Figure 1.1. Pushpin madness!

Figure 1.2. The basic geometries: a point, a linestring, and a polygon

Figure 1.3. The Utah salt flats—we can model them with linestrings, points, and polygons.

Figure 1.4. Linestrings and polygons created in the following code snippets

Figure 1.5. The Plugins menu of pgAdmin III shows the PostGIS Shapefile and DBF loader.

Figure 1.6. Loading into the geometry data type

Figure 1.7. Loading data into the geography data type. We pretend our data is 4326 instead of 4269 because they’re similar. We go ahead and index and check the load into geography.

Figure 1.8. U.S. Route 1 in Maryland, with three Hardee’s restaurants in the 10-mile buffer, and the 10-mile buffer around the route

Chapter 2. Geometry types

Figure 2.1. Three points created using the code in listing 2.1

Figure 2.2. Open and closed linestrings created using the code in listing 2.2. The points that make up the lines are shown as well.

Figure 2.3. A non-simple linestring tested for simplicity

Figure 2.4. Triangle-shaped polygon

Figure 2.5. Polygon with interior rings (holes)

Figure 2.6. We model the Seattle area as a polygon with two rings. Lake Washington fills up the hole. We’re also overlooking the existence of Mercer Island in the lake, which would make this a multipolygon.

Figure 2.7. Example of a self-intersecting polygon with text representation of POLYGON((2 0,0 0,1 1,1 -1, 2 0))'). This is an example of an invalid polygon, but with the naked eye it’s impossible to see that it’s one invalid polygon and not one valid multipolygon or two valid polygons.

Figure 2.8. A single multipoint geometry—not three distinct points!

Figure 2.9. Multilinest rings generated with WKT of inline examples

Figure 2.10. Multipolygon generated with WKT of MULTIPOLYGON(((2.25 0,1.25 1,1.25 -1,2.25 0)),((1 -1,1 1,0 0,1 -1)))

Figure 2.11. geometrycollection formed from code in listing

Figure 2.12. A simple five-point circular string, the WKT CIRCULARSTRING (0 0,2 0, 2 1, 2 3, 4 3). The control points are POINT(2 0)and POINT(2 3).

Figure 2.13. Three circular strings generated from the code in listing 2.4

Figure 2.14. A compound curve generated from the previous code

Figure 2.15. Curvepolygons generated from the code in listing 2.5

Figure 2.16. Compoundcurve in a curvepolygon with a circularstring hole

Chapter 3. Organizing spatial data

Figure 3.1. Paris arrondissements

Figure 3.2. Our dataset overlaid on the arrondissements without caring about geometry type

Figure 3.3. Only empty parent tables and child tables holding ar17 data are searched.

Chapter 4. Geometry functions

Figure 4.1. Simple linestring, polygon, and polygon with holes overlaid with their boundaries from the code in listing 4.9

Figure 4.2. Geometries and centroids (denoted by stars) generated from the code in listing 4.10. Observe that the centroid isn’t always a point on the geometry.

Figure 4.3. Geometries and stars representing the point on the surface generated from code in listing 4.10

Figure 4.4. ST_Simplify and ST_SimplifyPreserveTopology, going from an eight-sided polygon to a four-sided polygon

Chapter 5. Relationships between geometries

Figure 5.1. The first image is the POLYGON (our property) overlaid with the LINESTRING (our planned road), and the second is the intersection of the two. This results in a MULTILINESTRING that represents the portion of the property we need to take over.

Figure 5.2. Result of code in listing 5.1. The first image shows a region overlaid against square tiles. The second shows the result of intersection of square tiles with the region.

Figure 5.3. A shaded polygon with a hole (a house with a courtyard), a linestring (a walkway), a multipoint represented as two dots (two greeters: front door and courtyard greeter), and a point as a triangle (a door) generated from the code in listing 5.2

Figure 5.4. The polygon with a hole (a house with a courtyard), the linestring (a walkway), the multipoint (two greeters), and the point as a triangle (a door) seen together. All are generated from the code in listing 5.2.

Figure 5.5. Queries from listing 5.5 output. Observe that the difference of the polygon and the line is pretty much the polygon we started out with.

Figure 5.6. Result of the knife trick in listing 5.6

Figure 5.7. Various geometries and their bounding boxes from geometries in listing

Figure 5.8. Disjoint relationship expressed in intersection matrix (FF*FF****)

Figure 5.9. Equality relationship expressed in intersection matrix (T*F**FFF*)

Figure 5.10. The geometries from the query in listing 5.15

Figure 5.11. ST_Relate(triangle,2dline) = FF2101102, ST_Relate(2dline,triangle) = F11F00212

Figure 5.12. Intersection matrix of ST_Within (T*F**F***)

Chapter 6. Spatial reference system considerations

Figure 6.1. A geoid seen from different angles

Figure 6.2. The geoid and the ellipsoid seen together

Chapter 7. Working with real data

Figure 7.1. Quantum GIS Shapefile to PostGIS Import Tool

Figure 7.2. Using shp2pgsql-gui to load the states table

Figure 7.3. shp2pgsql-gui Import Options dialog box showing the advanced options

Chapter 8. Techniques to solve spatial problems

Figure 8.1. Snapping points to a line using the query from listings 8.12 and 8.13. The red dots are the original points and the blue triangles are the snapped ones.

Figure 8.2. Using our upgis_cutlineatpoints function, we cut Mission street with a point that’s within 100 feet of the line.

Figure 8.3. Our throwaway_grid

Figure 8.4. State of Idaho bisected

Figure 8.5. The state of Oklahoma broken into four equal quadrants

Figure 8.6. The center portions of the hexagonal and rectangular grids generated with code from listing 8.24. The highlighted center tiles are the location of our paintbrush CTE.

Figure 8.7. Diagram of query in listing 8.25. The dark area is the original hexagon (xfactor: 1, yfactor: 1), and the larger area is the hexagon scaled to twice its size (xfactor: 2, yfactor: 2).

Figure 8.8. Result of query in listing 8.26. This demonstrates scaling and then translating to maintain the original centroid position. The darkened black-bordered geometry is our original hexagon, and the outermost hexagon is our hexagon scaled to twice its size in all directions. The various spectral colors are incremental scalings ranging from 0.5 to 2 in steps of 0.5.

Figure 8.9. Result of query in listing 8.27 of rotating a hexagon from 0 to 270 in 45-degree increments

Figure 8.10. Result of query in listing 8.28. The gray area is our original hexagon, and the shaded geometry is our hexagon rotated 45 degrees.

Chapter 9. Performance tuning

Figure 9.1. Graphical explain controls

Figure 9.2. Graphical explain analyze of our bridge and city intersects query

Figure 9.3. Graphical explain after the addition of the index, first with the index tool tip and then with the nested loop tool tip

Figure 9.4. EXPLAIN ANALYZE graphical plan for many subselect queries

Figure 9.5. Cities with streets and count with min length using no subselects

Figure 9.6. Cities’ streets and count with min length using no subselects after disabling the hashagg strategy

Chapter 10. Enhancing SQL with add-ons

Figure 10.1. We plot the shortest route through the Twin Cities.

Figure 10.2. Optimized shortest-distance travel path for visiting all nuclear power plants in Spain starting from Almaraz

Figure 10.3. Demonstration output of running the previous statements in R

Figure 10.4. Result of SELECTch10.graph_income_house()

Figure 10.5. pgRouting Twin Cities results plotted with PL/R

Chapter 11. Using PostGIS in web applications

Figure 11.1. Setting up a GeoServer workspace

Figure 11.2. Adding a GeoServer PostGIS data source

Figure 11.3. Selecting PostGIS layers

Figure 11.4. Layer preview screen of GeoServer

Figure 11.5. Result of our map in listing 11.5

Figure 11.6. Map after adding change in listing 11.6

Figure 11.7. Example of overlaying WMS layers on an OpenStreetMap base using the code in listing 11.7

Figure 11.8. Example of a GeoExt application using ExtJS collapsible panels and OpenLayers

Figure 11.9. OpenLayers map in an ExtJS movable, stretchable, collapsible window using listing 11.9

Figure 11.10. Displaying KML layer in Google Earth using template in listing 11.12

Figure 11.11. Application with feature grid in sync with map using table column layout from the code in listing 11.13

Chapter 12. Using PostGIS in a desktop environment

Figure 12.1. OpenJUMP drop-down list for Connection and Link to add a connection

Figure 12.2. Adding a new PostGIS database connection

Figure 12.3. OpenJUMP Connection Manager with a new connection

Figure 12.4. Adding a PostGIS table in OpenJUMP

Figure 12.5. Datastore Layer setup in OpenJUMP

Figure 12.6. Output of SQL art query after applying custom styles

Figure 12.7. Adding a layer and PostGIS connection

Figure 12.8. The QGIS PostGIS connection screen allows you to specify a search in geometry_columns only.

Figure 12.9. QGIS PostGIS Connect tables and schemas; myplaces.place_geometry is a table consisting of different kinds of geometry types. Each type shows as a separate layer option.

Figure 12.10. QGIS Build Query allows you to sample field data and double-click to drop a value into the Where window.

Figure 12.11. QGIS dd database vector layer

Figure 12.12. QGIS vector file sampling

Figure 12.13. uDig Layer > Add connection

Figure 12.14. uDig CQL interface for filtering data

Figure 12.15. gvSIG Project Manager window

Figure 12.16. gvSIG adding a new PostGIS connection

Figure 12.17. gvSIG pick PostGIS layers

Figure 12.18. gvSIG basic export options

Chapter 13. PostGIS raster

Figure 13.1. Example of a raster topo map and select pixel ranges vectorized using ST_DumpAsPolygons and further smoothed. In the raster version you can make out the pixels, whereas in the vector version you can’t.

Figure 13.2. The Kauai BIL elevation model file in pseudocolor as single file and after chunking

Figure 13.3. The shaded NED relief with terrain elevations

Figure 13.4. Original Pele file overlaid with her database envelope, her database band 1 polygon self, and her database polygon self flipped back to line up with her original file self.

Figure 13.5. The envelope of a rotated Pele overlaid with the convex hull. The shaded area is the convex hull of the new rotated image.

Figure 13.6. A fatter and taller shadow of Pele overlaid on her original self

Figure 13.7. The clone envelopes of Pele overlaid on the envelopes of the Kauai raster tiles

Figure 13.8. Kauai raster intersected with a 100-meter radius buffer as detailed in listing 13.3. The darker patches represent higher elevations.

Figure 13.9. Chunked Pele’s envelope overlaid with the original Pele image

Figure 13.10. Pele reclassified output from OpenJUMP rast_simp created using listing 13.5

Figure 13.11. Dark tiles represent tiles that have pixels with information.

Figure 13.12. Our original WGS 84 map after being warped to NA LAEA (SRID:2163)

Appendix B. Installing, compiling, and upgrading

Figure B.1. Common error on Windows Vista

Figure B.2. New Database dialog box in pgAdmin III with the template_postgis database selected

Figure B.3. If you’re using pgAdmin III for backup, your screen should look like this.

Figure B.4. Creating a new database with pgAdmin III using template_postgis

Figure B.5. Doing a restore with pgAdmin III

Appendix C. SQL primer

Figure C.1. Diagram of a LEFT JOIN. The darkened region represents the portion of records returned by a LEFT JOIN. The x stands for multiplication and the + is additive. The first circle is M and the second circle is N.

Figure C.2. Diagram of an INNER JOIN. The darkened region represents the portion of records returned by the INNER JOIN. The x denotes that it’s multiplicative. The first circle is M and the second circle is N.

Figure C.3. Diagram of a RIGHT JOIN. The darkened region represents the portion of records returned by a RIGHT JOIN. The x stands for multiplication and the + is additive. The first circle is M and the second circle is N.

Figure C.4. Diagram of a FULL JOIN. The darkened region represents the portion of records returned by a FULL JOIN. The x stands for multiplication and the + is additive. The first circle is M and the second circle is N.

Figure C.5. Diagram of a CROSS JOIN. The darkened region represents the portion of records returned by the CROSS JOIN. The x stands for multiplication. The first circle is M and the second circle is N.

Figure C.6. UNION ALL versus UNION. The thick box is M and the thinner box is N. The first UNION ALL shared regions are duplicated; in UNION only one of the shared regions is kept, resulting in a distinct set.

Figure C.7. INTER-SECT—the darkened region is the intersection of two data sets returned by an INTERSECT clause.

Figure C.8. A demonstration of EXCEPT