Database Reference
In-Depth Information
Normalizing imports
Oftendatausedinaspatialdatabaseisimportedfromothersources.Assuchitmay
notbeinaformthatisusefulforourcurrentapplication.Insuchacase,itmaybeuse-
fultowritefunctionsthatwillaidintransformingthedataintoaformthatismoreuse-
ful for our application. This is particularly the case when going from flat file formats,
such as shapefiles, to relational databases such as PostgreSQL.
Note
A shapefile is a de facto as well as formal standard for the storage of spatial
data, and is probably the most common delivery format for vector spatial data.
A shapefile, in spite of its name, is never just one file, but a collection of files. It
consistsofatleast
*.shp
(whichcontainsgeometry),
*.shx
(anindexfile),and
*.dbf
(which contains the tabular information for the shapefile). It is a powerful
andusefulformatbut,asaflatfile,itisinherentlynonrelational.Eachgeometryis
associated in a one-to-one relationship with each row in a table.
There are many structures that might serve as a proxy for relational stores in a
shapefile.Wewillexploreonehere—asinglefieldwithdelimitedtextformultiplerela-
tions.Thisisanot-too-uncommonhacktoencodemultiplerelationshipsintoaflatfile.
The other common approach is to create multiple fields to store what in a relational
arrangement would be a single field.
Getting ready
The dataset we will be working with is a trails dataset that has linear extents for a
set of trails in a park system. The data is the typical data that comes from the GIS
world—as a flat shapefile, there are no explicit relational constructs in the data.
First, we load the data using the following command:
shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom
trails chp02.trails | psql -U me -d
postgis_cookbook
