I am using the mapserver map file interface to access a postgres
database, if I attempt to use a table aliases in a LABELITEM or
EXPRESSION clause in the map file I get a syntax error reported from the
postgres database
eg msDrawMap(): Image handling error. Failed to draw layer named 'castles'.
prepare_database(): Query error. Error executing POSTGIS DECLARE (the
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
pnt.place_name::text,certain::text,castle_name::text,asbinary(force_collection(force_2d(gis_ngr)),'NDR'),castle_id::text
from (select ct.castle_id, ct.gis_ngr ,ct.certain, ct.possible,
ct.question,ct.castle_name, pnt.place_name, distance(pnt.gis_ngr,
ct.gis_ngr) from castle_table ct,place_name_table pnt, county_table
where county_table.county_id= ct.county_id and pnt.county_id =
county_table.county_id and county_table.county_name='Surrey' ) as foo
WHERE gis_ngr && setSRID('BOX3D(454541.666665
119270.833332,579541.666669 181770.833334)'::BOX3D, 27700 )'
Postgresql reports the error as 'ERROR: missing FROM-clause entry for
table "pnt"'
For the input
===========================================================
LABELITEM "pnt.place_name"
CONNECTIONTYPE POSTGIS
CONNECTION "dbname=castle user=dp42 host=localhost port=5432"
#DATA "gis_ngr from (select castle_id, gis_ngr ,certain,
possible, question,castle_name from castle_table,county_table where
county_table.county_id= castle_table.county_id and
county_table.county_name='Surrey' ) as foo using srid=27700 using
unique castle_id"
DATA "gis_ngr from (select ct.castle_id, ct.gis_ngr ,ct.certain,
ct.possible, ct.question,ct.castle_name, pnt.place_name,
distance(pnt.gis_ngr, ct.gis_ngr)
from castle_table ct,place_name_table pnt,
county_table where county_table.county_id=
ct.county_id and
pnt.county_id = county_table.county_id and
county_table.county_name='Surrey' ) as
foo using srid=27700 using unique castle_id"
CLASS
NAME "Manor Sites"
SYMBOL 'circle'
SIZE 3
OUTLINECOLOR 255 0 0
EXPRESSION ( "[certain]" = "t" and "[castle_name]" !=
"%sitename%" and distance < %sitedistance% )
LABEL
======================================================
If I remove the table alias, ie say LABELITEM "place_name" everything is
ok, is there a way round this problem ?
Its mapserver 4.10
regards
David.
begin:vcard
fn:David Potts
n:Potts;David
x-mozilla-html:FALSE
version:2.1
end:vcard
