[postgis-users] postgis

[Mark Leslie]

MapServer should also be capable of recognizing primary keys on tables 
and using that column in preference to the oid.
If in doubt, follow Gregs advice and use the explicit USING UNIQUE 
clause.  You do need to ensure that the column is in fact unique, and I 
believe in still needs to be a numeric column.

-- 
Mark Leslie
Geospatial Software Architect
LISAsoft Pty Ltd
+61 (0)2 8570 5050

Commercial Support for Geospatial Open Source Software
http://www.lisasoft.com/LISAsoft/SupportedProducts.html


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.
> 
> 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
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users