Normalizing internal overlays

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.

Getting ready

First, we'll load our dataset using the following command:

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

How to do it...

Now that the data is loaded into a table in the database, we can leverage PostGIS to flatten and...