Comments 0

Document transcript

A

Spatial Querying FrameworkUsing PostGIS

Background

While one could hand craft necessaryfunctions

to execute spatiallyawarequeries, it is far more economical to take advantage of what already exists. Theactive PostGIS community has done an excellent jobensuring that PostgreSQL canbe spatially enabled.However, data must beadequately ingested,

stored, andindexed

such that any

client application can rapidly

execute spatial queries.

PostgreSQL / PostGISBackend

PostgreSQL is not spatially awareout-of-the-box. Rather, it must be extendedwith

PostGISstored procedures

to execute

spatial queries.

The PostGIS manual is anexcellent resource

and should be printed and kepton hand:http://postgis.refractions.net/docs/index.html.

A frequently overlooked point

is that creating a spatial table in aPostgreSQL/PostGIS

database is a 2-step process:1.create a normal non-spatial table, 2.then aspatial column must be added using the OpenGIS"AddGeometryColumn" function(seehttp://postgis.refractions.net/docs/ch04.html#Create_Spatial_Table).

Thisspatial column must also be properly indexed.

Required

Software

PostgreSQL 9.0:http://www.postgresql.org/

PostGIS 1.5.2:http://postgis.refractions.net/

GEOS 3.2.2:http://trac.osgeo.org/geos/

Proj 4.7:http://trac.osgeo.org/proj/

GDAL 1.7.2:http://www.gdal.org/

pgAdmin 1.12.2:http://www.pgadmin.org/

(excellent GUI for managing PostgreSQLdatabases)

Ifa resource

wishes to also provide Web Map Service (WMS) or Web FeatureService (WFS), which are OpenGIS standards forserving images

or XML responses,respectively, then I highly recommend MapServer,http://mapserver.org/. This is aCGI-based application that may connect directly to PostgreSQL and/or ArcViewshapefiles to return tiled images or WFS responses. There are thousands ofMapServer-based applications,but one such example is one I maintain calledSimpleMappr

(http://www.simplemappr.net). This uses

PHP-MapScript instead ofthe CGI-based instance

to avoid having to create static “map files”, a requiredMapServerconfiguration file. There are also Python and SWIG-based versions ofMapScript.

Although MapServer can

be used to execute spatial queriesdirectlyagainst

ArcView shapefiles, it is an in-memoryapplication and thus will not scale

ifthe shapefiles are appreciably large or if there are many shapefiles.

Instead,shapefiles can be ingested into a performance-friendly PostGIS/PostgreSQLdatabaseand MapServer used to servetiled images or WFS responses

from thespatial table(s).

Environment Set-up

In order tospatially-enable PostgreSQL database, it is useful to create adatabase template that can be re-used without superuser permissions:

# Set postgis-1.5.2 path where postgis.sql and spatial_ref_sys.sql are found

$ POSTGIS_SQL_PATH=`pg_config--sharedir`/contrib/postgis-1.5

# Create

theUTF-8template spatial database

(CAUTION: PostgreSQL is SQL_ASCII by default)

$ createdb-E UTF8-T template0 template_postgis

# Add PLPGSQL language support.

$ createlang-d template_postgis plpgsql

# Load

the PostGIS SQL routines.

$ psql-d template_postgis-f $POSTGIS_SQL_PATH/postgis.sql

$ psql-d template_postgis-f $POSTGIS_SQL_PATH/spatial_ref_sys.sql

# Enable

users to alter spatial tables.

$ psql-d template_postgis-c "GRANT ALL ON geometry_columns

TO PUBLIC;"

$ psql-d template_postgis-c "GRANT ALL ON geography_columns TO PUBLIC;"

I recommend MacPorts (http://www.macports.org/)because it has thenecessary, up-to-date ports:

sudo port install postgresql90

sudo port install postgresql90-server

sudo port install postgis

sudo port install proj

sudo port install gdal

port clean--all vile

Creating and spatially enabling a test PostgreSQL database will be much thesame as the Ubuntu example provided above. However, the necessary postgis.sqland spatial_ref_sys.sql locations will of course be different.

Front-end Applications

By far the best, freely available front-end application for querying ordisplaying data from PostgreSQL is OpenLayers,http://openlayers.org/. It can drawtiles from Google Maps, Yahoo, or Bing, data from WMS, WFS, KML, GeoRSS, amongother sources, and has a rich set of GUI tools such as free-hand line, circle, orpolygon drawing. If

Google Maps, Yahoo, or Bing are used as base layers, thecommercialSpherical Mercator projection must be added to the spatial database’s