I'm drawing a set of lines for a layer using geometries from a postgis
database. However I want to label the lines with a constant value that
isn't a column in the database. This is my DATA query:
DATA "geom FROM (SELECT asset.id as assetid, geom from
asset_position_lines LEFT JOIN asset ON asset_position_lines.assetid =
asset.id WHERE asset.id = 606 AND ( cube(cube(cube(EXTRACT(EPOCH FROM
TIMESTAMP WITH TIME ZONE '2006-09-27T00:00:00-07:00'), EXTRACT(EPOCH
FROM TIMESTAMP WITH TIME ZONE '2006-09-28T23:59:00-07:00')),
xmin(!BOX!), xmax(!BOX!)), ymin(!BOX!), ymax(!BOX!)) @ cubespacetime OR
cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE
'2006-09-27T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME
ZONE '2006-09-28T23:59:00-07:00')), xmin(!BOX!), xmax(!BOX!)),
ymin(!BOX!), ymax(!BOX!)) && cubespacetime ) ) AS new_table USING UNIQUE
assetId USING SRID=4326"
Ignore all the CUBE stuff in the WHERE clause, the important part is the
SELECT columns. I've tried adding a new column using SQL (which works
when I run the query directly in postgis) like this:
DATA "geom FROM (SELECT asset.id as assetid, 'test, test' as name, geom
from asset_position_lines LEFT JOIN asset ON
asset_position_lines.assetid = asset.id WHERE asset.id = 606 AND (
cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE
'2006-09-27T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME
ZONE '2006-09-28T23:59:00-07:00')), xmin(!BOX!), xmax(!BOX!)),
ymin(!BOX!), ymax(!BOX!)) @ cubespacetime OR
cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE
'2006-09-27T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME
ZONE '2006-09-28T23:59:00-07:00')), xmin(!BOX!), xmax(!BOX!)),
ymin(!BOX!), ymax(!BOX!)) && cubespacetime ) ) AS new_table USING UNIQUE
assetId USING SRID=4326"
'test, test' is a name I'm using for testing, which I want applied as a
label to the lines. I apply a LABELITEM of "name" in the mapfile. This
makes mapserver return a premature end of script error. However, if I
put that constant string inside some SQL function like UPPER() it works
fine:
DATA "geom FROM (SELECT asset.id as assetid, UPPER('test, test') as
name, geom from asset_position_lines LEFT JOIN asset ON
asset_position_lines.assetid = asset.id WHERE asset.id = 606 AND (
cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE
'2006-09-27T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME
ZONE '2006-09-28T23:59:00-07:00')), xmin(!BOX!), xmax(!BOX!)),
ymin(!BOX!), ymax(!BOX!)) @ cubespacetime OR
cube(cube(cube(EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE
'2006-09-27T00:00:00-07:00'), EXTRACT(EPOCH FROM TIMESTAMP WITH TIME
ZONE '2006-09-28T23:59:00-07:00')), xmin(!BOX!), xmax(!BOX!)),
ymin(!BOX!), ymax(!BOX!)) && cubespacetime ) ) AS new_table USING UNIQUE
assetId USING SRID=4326"
How come I have to use a function for this to work? Is there another
way I could do this without using a function here? Btw, I'm using
mapserver 4.10.0, thanks.
--
Graham Davis
Refractions Research Inc.
[EMAIL PROTECTED]
