[mapserver-users] Re: Conexion with postgres-postgis

[Ben Madin]

I'm certainly no expert, and I'm guessing it would have helped if you  
had included more of your layer definition:


On 13/01/2009, at 1:28 AM, mapserver-users-request at lists.osgeo.org  
wrote:

> msDrawMap(): Image handling error. Failed to draw layer named  
> 'chile_comuna'. prepare_database(): Query error. Error executing  
> POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor  
> BINARY CURSOR FOR

Here's (essentially) the query MapServer asked of PostGIS :

> SELECT  
> "NAME 
> "::text 
> ,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from  
> chile_comuna WHERE the_geom && setSRID('BOX3D(-71.2556639435156  
> -29.9271886101172,-71.2540857172265 -29.9256103838281)'::BOX3D,  
> find_srid('','chile_comuna','the_geom') )'

Here's the problem PostGIS reported with it.

> Postgresql reports the error as 'ERROR: column "NAME" does not exist  
> LINE 1: DECLARE mycursor BINARY CURSOR FOR SELECT  
> "NAME"::text,asbin... ^ '

It looks to my inexperienced eyes that somewhere in the layer  
(classitem?) it includes a reference to a field NAME.

It may be that it needs to be specified in the query. Check the table  
you imported into PostgreSQL, ie: (I made this up)

# \d chile_comuna

   Column            |         Type             
|                        Modifiers
---------------------+------------------------- 
+---------------------------------------------------------
  gid                 | integer                 | not null default  
nextval('gis_urban_gid_seq'::regclass)
  commune_name    | character varying(24)  |
  the_geom            | geometry                |


> More Help: Error with POSTGIS data variable. You specified 'check  
> your .map file'. Standard ways of specifiying are : (1)  
> 'geometry_column from geometry_table'

This is what you have used, but it only returns the_geom, you need to  
specify the table column AS name

> (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.

Try also specifying the name column (assuming it does exist in the  
table):

the_geom from (gid, the_geom, commune_name AS name from chile_comuna)  
as foo using unique column gid using SRID= 4326;


> For more help, please see http://postgis.refractions.net/documentation/ 
>  Mappostgis.c - version of Jan 23/2004.
>
> I use the command shp2pgsql to pass the information of the shapefile  
> to postgres table.
>
> MapFile
>  LAYER
>     NAME 'chile_comuna'
>     TYPE POLYGON
>     #DATA 'chile_comuna.shp'
>     CONNECTIONTYPE postgis
>   CONNECTION "user=postgres password=1234 dbname=geo_referencia  
> host=localhost"
>   DATA "the_geom from chile_comuna"
> .....
>
> can anyone help me? pleasee...

cheers

Ben




-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...