[mapserver-users] PostGIS connector now allows sub-selects (ie. table joins)

[Dave Blasby]

I had several requests for allowing more complex SQL queries with the
postgis<->mapserver connector.

This is now in the CVS version.  Its experimental.  Old mapfiles do NOT
have to be modified.

I also noticed that the folks doing the oracle spatial connector had
extended my version of OpenLayer() to query into arbitrary SQL. I
extended the postgis connector to also do this.  Here's some examples
and explainations.

The old way was to have your layer definition look like this.  This
definition still works.

        LAYER
                CONNECTIONTYPE postgis
                NAME "mypostgis"
                DATA "geo_value from ctivalue"
                CONNECTION "host=192.168.50.3 user=postgres port=5555
dbname=mapserv"
	END

Where DATA looked like "<geometry column> from <table>".

I've extended DATA so it can use a sub-select instead of specifing a
table name.  This defines the same layer, except it uses a subselect
instead of a table name.

        LAYER
                CONNECTIONTYPE postgis
                NAME "mypostgis"
                DATA "geo_value from (select oid,* from ctivalue) AS
foo"
                CONNECTION "host=192.168.50.3 user=postgres port=5555
dbname=mapserv"
	END

NOTES:
	+ PostgreSQL does not allow anonymous sub-selects so you ALWAYS have to
put the "... AS foo" after your subselect.
	+ The subselect must return an unique column called "oid".  This is
used by mapserver tag each shape object. See below.
	+ subselects are free to do whatevery they wish.  (ie. join tables)
	+ when you join tables, the GiST (R-Tree) index may not always be used
(although I havent actually seen a case of that happening yet).  I'll
look into it and will probably make some minor syntax changes (in the
future) to ensure its always used.

OID
---
	The required oid reference is a bit confusing, so I'll explain what it
is and where is came from.
	
	In postgresql, each table has a hidden (system defined) column called
'oid' (object id).  Normally one doesnt see it, but its there.
	
	ie. SELECT * FROM <table> WHERE OID = 987693;
	ie. SELECT oid,* FROM <table>;

	I use this to uniquely define each row in the result set for mapserver
queries (ie. mapserver's 'record' number).

	Unfortunately, the oid column disappears in sub-selects, so you have to
explictly define it.
	
	You dont have to use the system provided oid, you can use something
inside your table.  Just rename it oid.  

	ie. DATA "geo_value from (select gid as oid,* from ctivalue) AS foo"

	OID must be unique for each row!!

			
QUERIES		    
-------
They work as before, but I've changed the mechanics of how they actually
get information.

msPOSTGISLayerGetItems() used to query the system tables for the table
mentioned in "DATA" to find out what columns where in it.  With
sub-selects, one cannot do this.

Its hard to predict what the colums will be in an arbitrary piece of SQL
without actually executing it.  Since execution maybe very costly
(especially with lots of tables), i decided to try a different approach.

If you look in msPOSTGISLayerGetItems() and  postresql_NOTICE_HANDLER(),
you'll see that I trick postgresql into giving me its query optimization
output.  I parse this to find the columns the sub-select will produce.


Take a look at it and tell me if it works for you,

dave

ps. People not using the sub-queries should find that their maps work
exactly as they used to.