<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] postgis</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>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.<BR>
<BR>
Sayeth the postGIS manual:<BR>
<BR>
"Mapserver requires unique identifiers for each spatial record when doing queries, and the PostGIS module of<BR>
Mapserver uses the PostgreSQL oid value to provide these unique identifiers. A side-effect of this is that in<BR>
order to do fast random access of records during queries, an index on the oid is needed.<BR>
To build an "oid index", use the following SQL:<BR>
CREATE INDEX [indexname] ON [tablename] ( oid );"<BR>
<BR>
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:<BR>
<BR>
DATA "the_geom FROM (SELECT table1.the_geom AS the_geom, table1.gid AS gid,<BR>
table2.data AS data FROM table1 LEFT JOIN table2 ON table1.id = table2.id) AS new_table USING<BR>
UNIQUE gid USING SRID=-1"<BR>
<BR>
Which I think will make the interface use "gid" instead of trying for OIDs, which are being deprecated in PostgreSQL itself.<BR>
<BR>
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.<BR>
<BR>
HTH,<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
DigitalGlobe<BR>
<BR>
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of nickthegreek-<BR>
Sent: Wed 4/2/2008 7:00 PM<BR>
To: postgis-users@postgis.refractions.net<BR>
Subject: [postgis-users] postgis<BR>
<BR>
<BR>
gello to everyone i need a big .... what i have done is..to load a shp file<BR>
in the postgresql in the table postgis...<BR>
<BR>
in the mapfile i have the connectiontype and the connection and in the data<BR>
i have ' the_geoms from a file'..ok up now<BR>
<BR>
the shp file has been saved in the database in<BR>
postgis->shecmas->public->tables..<BR>
when i select to see what this file has inside it shows the columns<BR>
gid,...... ..... and has put column the_geom<BR>
<BR>
it has put the_geom automatically..the only column that added..<BR>
when i try to show this map i take this error and i don't know that to do<BR>
...pls helpme ..i have to give this exercise in monday<BR>
<BR>
Warning: [MapServer Error]: msDrawMap(): Failed to draw layer named 'canada<BR>
states'. in C:\ms4w\Apache\htdocs\canada.php on line 203<BR>
<BR>
Warning: [MapServer Error]: prepare_database(): Error executing POSTGIS<BR>
DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR<BR>
SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from<BR>
province WHERE the_geom && setSRID('BOX3D(-179.041698235478<BR>
25.406659782504,179.75431737052 89.1495117937299)'::BOX3D,<BR>
find_srid('','province','the_geom') )' Postgresql reports the error as<BR>
'ERROR: column "oid" does not exist LINE 1:<BR>
...inary(force_collection(force_2d(the_geom)),'NDR'),OID::text ... ^ ' More<BR>
Help: Error with POSTGIS data variable. You specified 'check your .map<BR>
file'. Standard ways of specifiying are : (1) 'geometry_column from<BR>
geometry_table' (2) 'geometry_column from (sub query) as foo using unique<BR>
column name using SRID=srid#' Make sure you put in the 'using unique column<BR>
name' and 'using SRID=#' clauses in. For more help, please see<BR>
<A HREF="http://postgis.refractions.net/documentation/">http://postgis.refractions.net/documentation/</A> Mappostgis.c - version of Jan<BR>
in C:\ms4w\Apache\htdocs\canada.php on line 203<BR>
<BR>
Warning: [MapServer Error]: msPOSTGISLayerRetrievePGVersion(): Error<BR>
executing POSTGIS statement (msPOSTGISLayerRetrievePGVersion():select<BR>
substring(version() from 12 for (position('on' in version()) - 13)) in<BR>
C:\ms4w\Apache\htdocs\canada.php on line 203<BR>
<BR>
Fatal error: Call to a member function saveImage() on a non-object in<BR>
C:\ms4w\Apache\htdocs\canada.php on line 204<BR>
--<BR>
View this message in context: <A HREF="http://www.nabble.com/postgis-tp16457583p16457583.html">http://www.nabble.com/postgis-tp16457583p16457583.html</A><BR>
Sent from the PostGIS - User mailing list archive at Nabble.com.<BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>