[Xastir-dev] differences between postGIS and postgres?

[Paul J. Morris]

On Mon, 25 Jan 2010 10:31:09 -0600
Jason KG4WSV <kg4wsv at gmail.com> wrote:
> I'm vague on the differences between standard and spatially enabled
> SQL databases, especially on the user interface side.  If it is
> implemented in standard postgres will I have trouble switching to
> postGIS?

Spatially enabled databases (native support in MySQL >4.1, or the
PostGIS add on to Postgreql), allow you to store points, lines,
polygons, and other spatial data in a form that allows you to easily
ask questions about the relationships of those data in space.  For
example, with spatial data you can easily ask what are the stations
(points) that occur within this county (arbitrary polygon)?  You can
use the spatial database as a data source within a GIS application,
QGIS will let you use data from a PostGIS query as a layer, OpenJump
will do the same with both MySQL and PostGIS.  I've used the database
persistence layer in Xastir to store station locations in PostGIS and
then rendered a map with QGIS. Spatial databases can understand the
coordinate system that you are using for storing data, and so can
transform coordinates on the fly from one datum to another (which might
be of concern if you are working with APRS data and data that uses a
different datum (or coordinate system, like state plane feet)).  

For just point data, it is possible to define a non-spatial table that
includes latitude and longitude numeric columns and to query this table
for all points within a rectangular bounding box (with the poles,
0 meridian, and possibly the 180 degree meridian potentially being
special cases if they fall inside the bounding box).  

Spatial support does come with some costs.  MySQL and PostGIS implement
spatial data differently, and thus need an abstraction layer if you
want your application to be database agnostic (as in the current
db_gis.c implementation in Xastir).  You will likely need to work with
functions to transform data between the native storage formats in the
database and the data structures in your application.  For example, the
Xastir there are internal three database specific functions to store a
station position into a simple table, upon invocation of the interface
storeStationSimpleToGisDb().


function storeStationSimplePointToGisDbPostgis() 
 prepares the sql query: 
"insert into simpleStation (station, transmit_time, position, symbol,
overlay, aprstype, origin, record_type, node_path) values ($1, $2, $3,
$4, $5, $6, $7, $8, $9)"
 where the value of the $3 (position) parameter is set through a call
to the xastir function xastirCoordToLatLongWKT(aStation->coord_lon,
aStation->coord_lat, wkt), which stores the position in wkt as a WGC
standard well known text representation of a point.  

In contrast, 

function storeStationSimplePointToGisDbMysql() 

uses the same xastirCoordToLatLongWKT(aStation->coord_lon,
aStation->coord_lat, wkt) to store the position as a WGC well known
text formated point, but then invokes the MySQL function
PointFromText() to store that value in MySQL's native spatial format. 

 "INSERT INTO simpleStationSpatial (station, transmit_time, position,
symbol, overlay, aprstype, origin, record_type, node_path) VALUES
(?,?,PointFromText(?),?,?,?,?,?,?)"

And, again in contrast, 

function storeStationSimplePointToDbMysql() 

was intended to work with MySQL 4.0, stores the position as simple
latitude and longitude values in a non-spatial table (replacing values
in the query directly rather than using a prepared statement).  

insert into simpleStation (station, symbol, overlay, aprstype,
transmit_time, latitude, longitude, origin, record_type, node_path)
values ('%s','%s','%s','%s','%s','%3.6f','%3.6f','%s','%c','%s')

For security reasons, I'd very strongly recommend using prepared queries
rather than this method, despite the added complexity in the C API for
preparing queries and binding parameters for both MySQL and Postgresql.


For a schema, I'd suggest starting with scripts/db_gis_mysql.sql or 
scripts/db_gis_postgis.sql for compatibility with the existing
implementation, then I'd suggest considering implementing the APRSWorld
schema (which would allow Xastir and an APRSWorld deployment to run
over the same databse).  

The comments I put in db_gis.c suggest that beyond the currently
implemented simple, schema, the roadmap might involve the addition of
storage for CAD objects (with SAR applications in mind), a full
representation of all xastir db data (stations, objects, CAD objects,
etc), and the APRSWorld schema.  

I haven't done much recently with the spatial database code in Xastir,
as I've been playing with using the OpenJump API in a java application
as a test bed for what a schema for a spatial database for a SAR
application ought to look like, before trying to extend Xastir's
support for storage of CAD objects.  

-Paul
-- 
Paul J. Morris
Biodiversity Informatics Manager
Harvard University Herbaria/Museum of Comparative Zoölogy
mole at morris.net  AA3SD  PGP public key available