[mapserver-users] Hacking Mappostgis.c to allow Views as Mapserver Data Source

[Rodrigo Cabral]

Folks:

I'm not a postgis user <yet>, but anyway I agree with the "USING UNIQUE ID
urid" sufix proposed by Grahan/Sean. This format looks like the Oracle
Spatial srid definition (i.e. "USING SRID srid"), and keeping some standard
like making the DATA statement "shape FROM table [USING xxx]" is always a
good idea.

Another improvement which I will probably include in SDO is projection,
which mapserver can do with proj.4, but Oracle also have it implemented. It
could be interesting for some users to use SDO's projection functions. Thus
I'm planning in extending DATA syntax for Oracle Spatial like this:

DATA "shape FROM table USING SRID srid, VIEW viewsrid"

Generally, it could be implemented dbwise like this:

DATA "shape FROM table USING <arg1>, <arg2>, ..., <argn>"

Where <argi> varies depending on the data source (i.e. SDO, postgis, etc.)

Cheers,
Rod.

----- Original Message -----
From: "Sean Gillies" <sgillies at i3.com>
To: "Dave Blasby" <dblasby at refractions.net>
Cc: <mapserver-users at lists.gis.umn.edu>
Sent: Tuesday, March 19, 2002 4:25 PM
Subject: Re: [mapserver-users] Hacking Mappostgis.c to allow Views as
Mapserver Data Source


Dave,

Thanks for the response.

Perhaps the answer to the OID issue is to allow a user to
specify an optional alternative to OID in the Mapserver
DATA parameter like

"the_geom from view USING UNIQUE ID urid"

If there is no ' USING UNIQUE ID *' string, meaning

    strstr(layer->data, " USING UNIQUE ID") == NULL)

you could default to the OID?

This was suggested to me by David Graham.

At this moment I am writing a function that will parse the
layer->data into geom_column_name, table_name and urid_name
using the 'using unique id' keywords. I'd like to contribute
it the the code if you think it would be useful.

One more thing -- I am having problems with sub-selects here
with my win32 mapserver.  A DATA parameter like:

"the_geom from (select oid,* from table) as foo"

is failing because the Mapserver goes to the geometry_columns
table (using find_srid()) looking for an f_table_name of
'(select oid,* from table) as foo'.  Have I uncovered a win32-
specific bug?

cheers,
Sean

Dave Blasby wrote:
> Sean,
>
> Thanks a lot for your comments and work on the mappostgis.c
> connector!
> Its always great to see someone contributing!
>
> Sean Gillies wrote:
>
>>1. Querying a unique row ID called "URID" instead of the Postgres
>>    OID.
>
>
> The reason why I used OID was because it is always available in every
> table, and is always unique.  Relying on a column called "URID" to
> exist,
> be an integer, and be unique is a pretty hefty assumption. Plus it
> requires people to manually add this column to every table.
>
> As an alternative, you can check with msPOSTGISLayerGetItems() to
> see if there is a "URID" column.  If there is, use it, otherwise use
> OID.
> This way only people with a "URID" column thats non-integer or
> non-unique
> will have problems.
>
> Perhaps we should call it "UNIQUE_INT_ID" so its highly unlikely someone
> would
> mistake what it is and what it should contain?
>
[snip]
> dave