[postgis-users] postgis

[Colin Wetherbee]

Gregory Williamson wrote:
> It's been a while since I used MMS, but IIRC it does need a reference
> to a unique id; OID is picked as a default if there is none
> specified.

Yes, this is true.  It doesn't have to be any special or meaningful 
unique ID, like an OID or a primary key, but MapServer looks for those. 
  Anything else must be explicitly specified.

> I think you can put something in the DATA statement like the
> following, assuming that the table has "gid" as a unique id (add a
> serial column and populate it is an easy way to add such a thing).
> Then:
> 
> DATA "the_geom FROM (SELECT table1.the_geom AS the_geom, table1.gid
> AS gid, table2.data AS data FROM table1 LEFT JOIN table2 ON table1.id
> = table2.id) AS new_table USING UNIQUE gid USING SRID=-1"
> 
> Which I think will make the interface use "gid" instead of trying for
>  OIDs, which are being deprecated in PostgreSQL itself.

In general, that DATA statement looks correct.

As another example, here's a DATA statement of mine.

     DATA "line FROM (
         SELECT num, id, line
         FROM jsview_journeyroutes
         WHERE userid = %userid%
       ) AS foo USING UNIQUE id USING SRID=4326"

It's always a good idea to set the USING SRID=xxxx to the proper SRID 
for the data set.

> You can also dump the table data, drop the table, recreate it with an
>  explicit "WITH OIDS" in the table creation, reload the data and away
> you go without changing the MMS interface. But it's better to get rid
> of OIDs where you can since they are not reliablely unique.

Yes, it's better to get rid of OIDs.  Creating an UNIQUE SERIAL column 
is preferable.

Colin