Bob,
If you want to keep * in the select, you have to alias the table name and add
that to the *.
Eg,
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.
L.*
from
Loc l
where
st_length(st_transform(the_line, 26993)) > 30
-- GPS error
order by
vname desc
) as subquery using unique vname using
srid=200068"
From: <Basques>, "Bob (CI-StPaul)"
<bob.basq...@ci.stpaul.mn.us<mailto:bob.basq...@ci.stpaul.mn.us>>
Date: Monday, December 15, 2014 at 5:07 PM
To: "Rahkonen Jukka (Tike)"
<jukka.rahko...@mmmtike.fi<mailto:jukka.rahko...@mmmtike.fi>>,
"mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org>"
<mapserver-users@lists.osgeo.org<mailto:mapserver-users@lists.osgeo.org>>
Subject: [EXTERNAL] Re: [mapserver-users] Postgres SQL oddity . . .
Resent-From: Michael Smith
<michael.sm...@usace.army.mil<mailto:michael.sm...@usace.army.mil>>
Vname is NOT distinct in the data table.
I got the SQL to pass by dropping the “ * “ from the SQL, but ran into a
different problem, how to run a logical expression against a POSTGRES call,
looks like it only works with SHP files. Crap!!
Bobb
From: Rahkonen Jukka (Tike) [mailto:jukka.rahko...@mmmtike.fi]
Sent: Monday, December 15, 2014 4:01 PM
To: Basques, Bob (CI-StPaul);
mapserver-us...@osgeo.org<mailto:mapserver-us...@osgeo.org>
Subject: Re: Postgres SQL oddity . . .
Hi,
If vname is unique in your table, why do you need to do "select distinct" on it?
I would make a blind guess with an alias name select distinct on (vname)
vname_distinct
Usually it is not as easy, though
-Jukka Rahkonen-
________________________________
Basques, Bob wrote:
> 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
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
