PostGIS Cookbook: For web developers and software architects this book will provide a vital guide to the tools and capabilities available to PostGIS spatial databases. Packed with hands-on recipes and powerful concepts

This chapter focuses on ways to structure data using the functionality provided by the combination of PostgreSQL and PostGIS. These will be useful approaches for structuring and cleaning up imported data, converting tabular data into spatial data "on the fly" as it is entered, and maintaining relationships between tables and datasets using functionality endemic to the powerful combination of PostgreSQL and PostGIS. There are three categories of techniques by which we will leverage these functionalities: automatic population and modification of data using views and triggers, object orientation using PostgreSQL table inheritance, and using PostGIS functions (stored procedures) to reconstruct and normalize problematic data.

Automatic population of data is where this chapter begins. By leveraging PostgreSQL views and triggers, we can create ad hoc and flexible solutions to create connections between and within the tables. By extension, and for more formal or structured cases, PostgreSQL...

Views in PostgreSQL allow for ad hoc representation of data and data relationships in alternate forms. In this recipe, we'll be using views to allow for the automatic creation of point data based on tabular inputs. We can imagine a case where the input stream of data is non-spatial, but includes longitude and latitude or some other coordinates. We would like to automatically show this data as points in space.

CREATE VIEW viewname AS
  SELECT...

–- Drop the table in case it exists
DROP TABLE IF EXISTS chp02.xwhyzed CASCADE; 
CREATE TABLE chp02.xwhyzed
-- This table will contain...

In this recipe, we imagine that we have ongoing updates to our database, which needs spatial representation; however, in this case, we want a hard-coded geometry column to be updated each time an INSERT operation takes place on the database, converting our x and y values to geometry as they are inserted in the database.

The advantage of this approach is that the geometry is then registered in the geometry_columns view, and therefore this approach works reliably with more PostGIS client types than creating a geospatial view. This also provides the advantage of allowing for a spatial index that can significantly speed up a variety of queries. The disadvantage for users using PostgreSQL versions lower than Version 9.0 is that, without a WHERE clause within the trigger, every time an insert takes place, the trigger will be calculated on all points to create geometry. This method could be very expensive on large datasets. However, for users of PostgreSQL...

An unusual and useful property of PostgreSQL databases is that they allow for object inheritance models as they apply to tables. This means that we can have parent/child relationships between tables and leverage that to structure out data in meaningful ways. In our example, we will apply this to hydrology data. This data can be points, lines, polygons, or more complex structures, but they have one commonality; they are explicitly linked in a physical sense and inherently related and are all about water. Water/hydrology is an excellent natural system to model this way, as our ways of modeling it spatially can be quite mixed depending on scales, details, the data collection process, and a host of other factors.

Table partitioning is an approach specific to PostgreSQL that extends inheritance to model tables that typically do not vary from each other in the available fields, but where the child tables represent logical partitioning of the data based on a variety of factors, be it time, value ranges, classifications, or, in our case, spatial relationships. The advantages of partitioning include improved query performance due to smaller indexes and targeted scans of data, bulk loads, and deletes that bypass the costs of maintenance functions like VACUUM. It can thus be used to put commonly used data on a faster and more expensive storage, and the remaining data on a slower and cheaper storage. In combination with PostGIS, we get the novel power of spatial partitioning, which is a really powerful feature for large datasets.

Often data used in a spatial database is imported from other sources. As such it may not be in a form that is useful for our current application. In such a case, it may be useful to write functions that will aid in transforming the data into a form that is more useful for our application. This is particularly the case when going from flat file formats, such as shapefiles, to relational databases such as PostgreSQL.

Data from an external source can have not just table structure issues, but also topological issues endemic to the geospatial data itself. Take, for example, the problem of data with overlapping polygons. If our dataset has polygons that overlap with internal overlays, queries for area, perimeter, and other metrics may not produce predictable or consistent results.

There are a few approaches that can solve the problem of polygon datasets with internal overlays. The general approach presented here was originally proposed by Kevin Neufeld of Refractions Research.

Over the course of writing our query, we will also produce a solution for converting polygons to linestrings.

shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom cm_usearea_polygon chp02.use_area | psql -U me -d postgis_cookbook

PostgreSQL functions abound for the aggregation of tabular data, including sum, count, min, max, and so on. PostGIS as a framework does not explicitly have spatial equivalents of these, but this does not prevent us from building functions using the aggregates in concert with PostGIS's spatial functionality.

In this recipe, we will explore spatial summarization with the United States Census data. US Census data, by nature, is aggregated data. This is done intentionally to protect the privacy of citizens. But when it comes to doing analyses with this data, the aggregate nature of the data can become problematic. There are some tricks to disaggregate data. Amongst the simplest of these is the use of a proportional sum based on area, which we will do in this exercise.