[mapserver-users] Postgres SQL oddity . . .

[Basques, Bob (CI-StPaul)]

Ahh, fixed it.  Mapserver doesn't like the ' * ' in the SQL , at least not on the end . . . hmm, I wonder if it needs (should be)  to be escaped in a subquery??  I took that out and added in a couple of key columns I needed . . .

This works now:

DATA "the_geom from (

                        select
                                distinct on (vname) vname,
                          the_line as the_geom,
                                st_length(st_transform(the_line, 26993)) as feet,
                                (DATE_PART('day', now() - acqtime) * 24 +
                                        DATE_PART('hour', now() - acqtime) * 60 +
                                        DATE_PART('minute', now() - acqtime)
                                        ) as elapsed_min  -- Compiled minutes since last major move of asset.
                        from
                                loc
                        where
                                st_length(st_transform(the_line, 26993)) > 30  -- GPS error
                        order by
                                vname desc

                                        ) as subquery using unique vname using srid=200068"

Bobb



From: mapserver-users-bounces at lists.osgeo.org [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Basques, Bob (CI-StPaul)
Sent: Monday, December 15, 2014 3:48 PM
To: mapserver-users at osgeo.org
Subject: [mapserver-users] Postgres SQL oddity . . .

I'm trying to display a set of GPS points from a layer in Postrgres . . .

Getting this error however:

<ServiceExceptionReport version="1.3.0" xsi:schemaLocation="http://www.opengis.net/ogc http://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd<http://www.opengis.net/ogc%20http:/schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd>"><ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named 'AVL_Plot_00to96'.
msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:  column reference "vname" is ambiguous
LINE 1: select "elapsed_min","vname",encode(ST_AsBinary(ST_Force2D("...

</ServiceException></ServiceExceptionReport>

from this Call:

DATA "the_geom from (

                        select
                                distinct on (vname) vname,
                                st_length(st_transform(the_line, 26993)) as feet,
                                (DATE_PART('day', now() - acqtime) * 24 +
                                        DATE_PART('hour', now() - acqtime) * 60 +
                                        DATE_PART('minute', now() - acqtime)
                                        ) as elapsed_min,  -- Compiled minutes since last major move of asset.
                                *
                        from
                                loc
                        where
                                st_length(st_transform(the_line, 26993)) > 30  -- GPS error
                        order by
                                vname desc

                                        ) as subquery using unique vname using srid=200068"

What I don't understand is how VNAME can be ambiguous when calling only one table (loc)??  The SQL works fine in PGADMIN.   Does the using unique vname have something to do with the problem, which seems to be required.

Help??

Thanks.

bobb

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20141215/b78fdaeb/attachment.html>