You can read about the postgres "to_char" function that I used (to go
from date to a string) here:
http://www.postgresql.org/docs/9.5/static/functions-formatting.html
-jeff
--
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/
On 2016-03-23 11:45 AM, Jeff McKenna wrote:
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
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
