Database Reference
In-Depth Information
Normalizing internal overlays
Datafromanexternalsourcecanhavenotjusttablestructureissues,butalsotopo-
logicalissuesendemictothegeospatialdataitself.Take,forexample,theproblemof
datawithoverlappingpolygons.Ifourdatasethaspolygonsthatoverlapwithinternal
overlays,queriesforarea,perimeter,andothermetricsmaynotproducepredictable
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
toflattenandgettheunionofthepolygons,suchthatwehaveanormalizeddataset.
Thefirststepindoingsousingthisapproachwillbetoconvertthepolygonstolines-
trings. We can then node those linestrings and convert them back to polygons, rep-
resenting the union of all the polygon inputs. We will perform the following tasks:
1. Converting polygons to linestrings.
2. Converting linestrings back to polygons.
3. Finding center points of resultant polygons.
4. Use resultant points to query tabular relationships.
