Database Reference
In-Depth Information
Chapter 2. Structures that Work
In this chapter, we will cover:
• Using geospatial views
• Using triggers to populate a geometry column
• Structuring spatial data with table inheritance
• Extending inheritance - table partitioning
• Normalizing imports
• Normalizing internal overlays
• Using polygon overlays for proportional census estimates
Introduction
Thischapterfocusesonwaystostructuredatausingthefunctionalityprovidedbythe
combinationofPostgreSQLandPostGIS.Thesewillbeusefulapproachesforstruc-
turingandcleaningupimporteddata,convertingtabulardataintospatialdata"onthe
fly" as it is entered, and maintaining relationships between tables and datasets us-
ing functionality endemic to the powerful combination of PostgreSQL and PostGIS.
Therearethreecategoriesoftechniquesbywhichwewillleveragethesefunctional-
ities: automatic population and modification of data using views and triggers, object
orientationusingPostgreSQLtableinheritance,andusingPostGISfunctions(stored
procedures) to reconstruct and normalize problematic data.
Automaticpopulationofdataiswherethischapterbegins.ByleveragingPostgreSQL
viewsandtriggers,wecancreateadhocandflexiblesolutionstocreateconnections
betweenandwithinthetables.Byextension,andformoreformalorstructuredcases,
PostgreSQL provides table inheritance and table partitioning that allow for explicit
hierarchical relationships between tables. This can be useful in cases where an ob-
jectinheritancemodelenforcesdatarelationshipsthateitherrepresentthedatabet-
ter,therebyresultingingreaterefficiencies,orreducetheadministrativeoverheadof
maintainingandaccessingthedatasetsovertime.WithPostGISextendingthatfunc-
tionality,theinheritancecanapplynotjusttothecommonlyusedtableattributes,but
canalsoleveragespatialrelationshipsbetweentables,resultingingreaterqueryef-
ficiency with very large datasets. Finally, we will explore PostGIS SQL patterns that
providetablenormalizationofdatainputs,sodatasetsthatcomefromflatfilesystems
or are not normalized can be converted to a form we would expect in a database.
Search WWH ::




Custom Search