14 Organizing spatial data

In chapter 2 we walked through all the possible geometry, geography, and raster types PostGIS offers and how you can create and store them. In this chapter we’ll continue our study by demonstrating the different table layouts you can design to store spatial data. Then we’ll apply these various design approaches to a real-world example (Paris, France). We’ll finish the chapter with a discussion and examples of using views for database abstraction and using triggers to manage inserts and updates in tables and views. Our main focus will be the geometry type, which is still the most commonly used type in PostGIS.

You can download all the data and code for this chapter at www.postgis.us/chapter_14_edition_3. Before we start, you’ll need to load the ch14_data.sql and ch14_staging_data.sql scripts from the chapter download file.

14.1 Spatial storage approaches

14.1.1 Heterogeneous columns

14.1.2 Homogeneous columns

14.1.3 Typmod vs. constraints

14.1.4 Table inheritance

14.1.5 Table partitioning

14.2 Modeling a real city

14.2.1 Modeling using heterogeneous geometry columns

14.2.2 Modeling using homogeneous geometry columns

14.2.3 Modeling using partitioning

14.3 Making auto-updatable views

14.4 Using triggers and rules

14.4.1 Triggers

14.4.2 Using INSTEAD OF triggers

14.4.3 Using other triggers