PostGIS in Action (3rd)
Where the adventure begins

PostGIS in Action: (ed. #1) Chapter 3 - Data Modeling

PostGIS In Action 3rd Edition Chapters    PostGIS In Action 2nd Edition Chapters    1st Edition Chapters
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
  • columns.
  • 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.


pgRouting: A Practical Guide is now available in Hardcopy from Hardcopy from Amazon. E-Book or Bulk discounts on Hard-copy from Locate Press.

PostGIS in Action,3rd edition MEAP    PostgreSQL: Up and Running       D3.js in Action 2nd Edition   PostGIS Cookbook 2nd edition