[postgis-users] postgis

[Gregory Williamson]

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. 

Sayeth the postGIS manual:

"Mapserver requires unique identifiers for each spatial record when doing queries, and the PostGIS module of
Mapserver uses the PostgreSQL oid value to provide these unique identifiers. A side-effect of this is that in
order to do fast random access of records during queries, an index on the oid is needed.
To build an "oid index", use the following SQL:
CREATE INDEX [indexname] ON [tablename] ( oid );"

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. 

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.

HTH,

Greg Williamson
Senior DBA
DigitalGlobe

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.

(My corporate masters made me say this.)



-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of nickthegreek-
Sent: Wed 4/2/2008 7:00 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] postgis
 

gello to everyone i need a  big .... what  i have done is..to load a shp file
in the postgresql in the table postgis...

in the mapfile i have the connectiontype and the connection and in the data
i have ' the_geoms from a file'..ok up now

the shp file has been saved in the database in
postgis->shecmas->public->tables..
when i select to see what this file has inside it shows the columns
gid,...... .....   and has put column the_geom

it has put the_geom automatically..the only column that added..
when i try to show this map i take this error and i don't know that to do
...pls helpme ..i have to give this exercise in monday

Warning: [MapServer Error]: msDrawMap(): Failed to draw layer named 'canada
states'. in C:\ms4w\Apache\htdocs\canada.php on line 203

Warning: [MapServer Error]: prepare_database(): Error executing POSTGIS
DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR
SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
province WHERE the_geom && setSRID('BOX3D(-179.041698235478
25.406659782504,179.75431737052 89.1495117937299)'::BOX3D,
find_srid('','province','the_geom') )' Postgresql reports the error as
'ERROR: column "oid" does not exist LINE 1:
...inary(force_collection(force_2d(the_geom)),'NDR'),OID::text ... ^ ' More
Help: Error with POSTGIS data variable. You specified 'check your .map
file'. Standard ways of specifiying are : (1) 'geometry_column from
geometry_table' (2) 'geometry_column from (sub query) as foo using unique
column name using SRID=srid#' Make sure you put in the 'using unique column
name' and 'using SRID=#' clauses in. For more help, please see
http://postgis.refractions.net/documentation/ Mappostgis.c - version of Jan
in C:\ms4w\Apache\htdocs\canada.php on line 203

Warning: [MapServer Error]: msPOSTGISLayerRetrievePGVersion(): Error
executing POSTGIS statement (msPOSTGISLayerRetrievePGVersion():select
substring(version() from 12 for (position('on' in version()) - 13)) in
C:\ms4w\Apache\htdocs\canada.php on line 203

Fatal error: Call to a member function saveImage() on a non-object in
C:\ms4w\Apache\htdocs\canada.php on line 204
-- 
View this message in context: http://www.nabble.com/postgis-tp16457583p16457583.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080402/b45e24ba/attachment.html>