Postgis error with mapserver

[Jerry Pisk]

This is actually a problem with the PostGIS connector not properly
separating the schema from the table name. find_srids parameters are
(schema, table, column) so the call should've been find_srid('public',
Inun_RiscAlt', 'geom'). There's more that the PostGIS code does not
recognize, such as quoted table and column names (it will think the
quotes are part of the name). I'll file a bug and fix this since it's
holding up my current work as well. Unfortunately due to the timing
this probably won't make it to 4.8 as it is going to be somewhat of a
major change.

For now you can work around it by using USING SRID in your query so
mapserver does not generate that find_srid call.

Jerry

On 1/17/06, Don Isgitt <djisgitt at soundenergy.com> wrote:
> joanvp wrote:
>
> > *Hi!!
> >
> > I've tried to show a postgis layer with UMN Mapserver. I've installed
> > last version of postgresql with postgis (8.1). I've imported a
> > shapefile with FME.
> >
> > I've tried this layer with Quantum GIS and all works fine. The problem
> > is with UMN Mapserver (v4.6). This is the error log
> >
> > ----------------------------------------------
> > Warning*: [MapServer Error]: prepare_database(): Error executing
> > POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY
> > CURSOR FOR SELECT
> > asbinary(force_collection(force_2d(geom)),'NDR'),OID::text from
> > public.Inun_RiscAlt WHERE geom && setSRID('BOX3D(326607
> > 4245586.875,627250 4471069.125)'::BOX3D,
> > find_srid('','public.Inun_RiscAlt','geom') )'
> >
> > Postgresql reports the error as 'ERROR: relation "inun_riscalt" does
> > not exist '
> >
> > 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.php
> >
> > ------------------------
> > This is my mapfile
> > (....)
> > LAYER
> >   NAME inun_riscalt
> >   TYPE POLYGON
> >   STATUS On
> > CONNECTION "user=xxx password=xxxx dbname=bdmapserver host=localhost
> > port=5432"
> > CONNECTIONTYPE postgis
> > DATA "geom from Inun_RiscAlt"
> >   CLASS
> >     NAME "Inun_RiscAlt "
> >     COLOR 182 131 78
> >     OUTLINECOLOR 4 24 93
> >   END
> > END
> > (...)
> >
> > Any solution?
> >
> > Thank you in advanced
> >
> > ------------------------------------------------------------------------
> > Yahoo! Photos
> > Got holiday prints? See all the ways
> > <http://us.rd.yahoo.com/mail_us/taglines/holidayprints/*http://pa.yahoo.com/*http://us.rd.yahoo.com/mail_us/taglines/photos/evt=38089/*http://pg.photos.yahoo.com/ph//print_splash>
> > to get quality prints in your hands ASAP.
>
> Joanvp,
>
> Notice the  public.Inun_RiscAlt versus  inun_riscalt. Postgres forces
> names to lowercase unless they are enclosed in quotes.
>
> Don
>