Putting the Spatial in the Spatial Database

SDE is not a spatial database.

Surprised? So was the PC when he began administering SDE. SDE is an interface between (proprietary) client software and a normal, non-spatial database. It allows spatial data to be stored in SDE’s format in the database, but there’s nothing spatial about the database itself. You can’t ask SQL Server to buffer some features and return the result to you. That’s because the database itself isn’t spatial. To load or manipulate the spatial data, you’ll need ESRI products and SDE middleware.

There are a number of products that better fit the moniker of “spatial database”. Oracle Spatial and DB2’s Spatial Extender are a couple of examples of proprietary spatial database options. MySQL, an open source database, has a limited spatial extension. These products generally allow you to store and access spatial information, but their ability to manipulate or ask questions of the data tends to be extremely limited.

Enter the tandem of PostgreSQL and PostGIS. PostgreSQL is an enterprise-class open source relational database engine similar in scope and functionality to Oracle or SQL Server. PostGIS is an open source project that spatially enables PostgreSQL. It is the only product fully compliant with OGC’s SFSQL (that’s Open Geospatial Consortium Simple Features Specification for SQL), allowing not only data storage and retrieval, but data manipulation as well.

Let’s look at some examples so you have a better idea of what I mean. To load a shape file into PostgreSQL, one simply runs a couple of commands:

shp2pgsql d:\polladdr.shp polladdr < d:\polladdr.sql -d -s 2264 -i psql -d GISData -f d:\polladdr.sql

That’s it. Minus some optional indexing, the polling location shape file is now in PostgreSQL and ready to go.

You can make any query you can imagine from ArcIMS and quite a bit more via simple SQL calls. You don’t have to form complex ArcXML, do a bunch of hops (web service to ArcIMS to SDE to SQL Server and back), and parse through XML results. What you get back is a record set like any other.

The spatial methods are stored as SQL functions. For example,

select name from polladdr where distance(the_geom, GeometryFromText(‘POINT(1450694 508134)’, 2264)) < 5280

uses the functions distance, geometryfromtext, and point to get all of the polling locations within a mile of user defined point (compare that to the ArcXML above!). The result is a record set you can manipulate or bind to a data grid as you would any other record set.

SELECT extent(Transform(the_geom, 4326)) FROM baseprec where precno = 26
returns the extent of voting precinct 26.

SELECT name, area(the_geom)/5280 AS feet FROM juris ORDER BY feet DESC
gets the name and area of the jurisdictions, ordered by area (miles).

You can do intersections, buffers, unions, overlays, reprojections (it’s very easy to convert DD to state plane for those GPS users you support), you name it. You can also do any kind of SQL joins you normally do with your SQL calls, and everything comes back in a nice, neat record set.

As you can see, there are extensive capabilities to query geospatial data in PostgreSQL without the need for a GIS client at all. There are, however, a number of GIS clients that can connect directly to a PostGIS database, including MapServer, JUMP, QGIS, GRASS, FME, and others. There is even an extension to allow ArcGIS to connect to PostgreSQL/PostGIS, though a release to support 9.x isn’t out yet.

I know your next question - that’s great for queries and tables, but how do I visualize the data? Next month I’ll introduce you to the world of scalar vector graphics (SVG), and you’ll see how PostgreSQL and PostGIS can be used to create a map server with no map server at all.