Chapter 3 - Data Modeling

PostGIS in Action: Chapter 3 - Data Modeling

Back to Chapter Listings
Download chapter code and data

Note: This chapter features a clipping of Paris in OSM format. If you want all of Paris or any other popular metro city like Boston, MA (US), San Diego, California (US), Denver, CO (US), Victoria (Canada), Lagos (Nigeria) check out Metro Teczno which has frequently updated OSM feeds of various popular metro areas often updated weekly.

Options for structuring geometry data
Modeling a city
hstore data type
Using table inheritance
Using triggers and rules

UPDATE: This chapter has been significantly reworked from the earliest versions that were released via MEAP. We have replaced the Hello Town example with real data covering Paris, France that we clipped from OpenStreetMap and geocommons data sets. The concepts are more or less the same with addition of coverage of using hstore data type to combine the strengths of schema and schema-less designs. Note that PostGIS in Action is out now in e-Book as well as print version.

This chapter covers various approaches for storing spatial data in PostgreSQL/PostGIS. In this chapter we cover core PostgreSQL and PostGIS storage concepts:

Table inheritance and constraint exclusion for partitioning rows
Table schemas for logical data separation
Using single geometry well constrained columns in a table, using unconstrained geometry columns, and using using multiple geometry
Using triggers and rules to redirect inserts and populate additional data columns.

We also discuss the pros and cons of each approach.

Although this chapter focuses on GIS data storage, many of the concepts are applicable for general PostgreSQL data storage.