[mapserver-users] Get current year in a CLASS EXPRESSION (mapfile)

[Michael Smith]

Rather than substring on the to_char with a full date, just use
to_char(datefield,'YYYY').

Mike

-- 
Michael Smith

Remote Sensing/GIS Center
US Army Corps of Engineers



On 3/23/16,  10:50 AM, "mapserver-users on behalf of Jeff McKenna"
<mapserver-users-bounces at lists.osgeo.org on behalf of
jmckenna at gatewaygeomatics.com> wrote:

>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
>mapserver-users at lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/mapserver-users