Hello Thomas (when you ask for help it's good karma to include your name),
If I was in this situation, here would be my steps:
1. Focus first on Postgres, and see if I can get the first 4 characters
of a date column, through the commandline tool "psql". For example, see
the "substring" function at
http://www.postgresql.org/docs/9.5/static/functions-string.html
Here is a psql command that works for me with Postgres 9.5, where I am
querying a date column "pubdate", using the first 4 characters, and
trying to return the instances where those characters are '2013':
# select id, geom, substring(to_char(pubdate, 'YYYY-MM-DD') from 1 for
4) from "mytable" where substring(to_char(pubdate, 'YYYY-MM-DD') from 1
for 4) = '2013';
2. Once that is working, I would move onto test through OGR, directly to
this Postgres instance, with the commandline utility "ogrinfo".
Here is my first command to connect:
ogrinfo -ro PG:"host=localhost user=postgres password=postgres
dbname=mydbname_csu port=5438" mytable -summary
Next I want to try that same psql select command through ogrinfo, which
has a "-sql" switch to test queries:
ogrinfo -ro PG:"host=localhost user=postgres password=postgres
dbname=mydbname port=5438" mytable -sql "select geom,
substring(to_char(pubdate, 'YYYY-MM-DD') from 1 for 4) from mytable
where substring(to_char(pubdate, 'YYYY-MM-DD') from 1 for 4) = '2013'
That should return the correct features.
3. Once that is working, then I would use that in my mapfile layer, and
try labeling features just with those 4 date characters, such as:
LAYER
NAME "test"
STATUS ON
TYPE POLYGON
CONNECTIONTYPE OGR
CONNECTION "PG:host=localhost user=postgres password=postgres
dbname=mydbname port=5438"
DATA "select geom, id, substring(to_char(pubdate, 'YYYY-MM-DD')
from 1 for 4) as pubtext from mytable"
PROCESSING "CLOSE_CONNECTION=DEFER"
LABELITEM "pubtext"
CLASS
NAME "test"
STYLE
COLOR 235 234 85
END # STYLE
LABEL
COLOR 255 0 0
FONT sans-italic
TYPE truetype
SIZE 8
POSITION AUTO
PARTIALS FALSE
OUTLINECOLOR 255 255 255
END # LABEL
END # CLASS
END # LAYER
The above was tested with MS4W 3.1.3 (MapServer 7.0.1) and PostgreSQL 9.5
-jeff
--
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/
On 2016-03-23 7:17 AM, wiltomap wrote:
I have a mapfile defining a layer which is about leaks fixes on a drinking
water network.
I would like to write an EXPRESSION (inside a CLASS) to select all current
year operations. I rely upon a PostgreSQL/PostGIS table which is storing
leaks fixes dates in a DATE type field named "datinter" (format is
YYY-MM-DD). For certain reasons I won't explain here (depending on the
website general infrastructure), I can't add columns to the table to display
year from the datinter column. I need to do it into the EXPRESSION parameter
of my mapfile.
I have tried things like this :
CLASS
NAME "2016"
EXPRESSION ("[datinter]" > "2016-01-01")
...
END
This kind of expression does filter some of the table rows but mixes
different years... I don't understand what selection is made. Any idea on
how to write the expression to achieve what I need?
Thanks in advance!
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
