[mapserver-users] Mapserver WMS time support with Oracle

[Stefan Schantz]

Hi,
we had the same issue.
When we call mapserver via cgi, we set the apache environment for oracle like
this:
SetEnv NLS_LANG=German_Germany.UTF8
SetEnv NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

So, we can use a MapServer supported time format.

When we call mapserver via fastcgi, we set the environment for oracle in a
script:


#!/bin/sh
export ORACLE_HOME="(...)/instantclient_11_2
export ORACLE_BASE="(...)/instantclient_11_2
export LD_LIBRARY_PATH="(...)/instantclient_11_2
export TNS_ADMIN="(...)/instantclient_11_2
export NLS_LANG=German_Germany.UTF8
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"
MAPSERV="(...)/mapserv.fcgi"
MS_MAPFILE="(...)/wms_geoportal.map" exec ${MAPSERV}

regards,
Stefan

> "D. Nappo" <domenico.nappo at gmail.com> hat am 21. Januar 2014 um 15:18
> geschrieben:
> 
>  ...but unfortunately MapServer wms time support doesn't include that format:
>   <http://mapserver.org/it/ogc/wms_time.html>
> 
> 
>  2014/1/21 D. Nappo <domenico.nappo at gmail.com
> <mailto:domenico.nappo at gmail.com> >
>    > >    Many thanks!
> >    It helped: the default date format in our system is DD-Mon-RR and I found
> > it out with SELECT * FROM nls_database_parameters WHERE parameter LIKE
> > '%DATE%'
> > 
> > 
> > 
> >    2014/1/20 Umberto Nicoletti <umberto.nicoletti at gmail.com
> > <mailto:umberto.nicoletti at gmail.com> >
> >      > > >      On Mon, Jan 20, 2014 at 2:44 PM, D. Nappo
> >      > > > <domenico.nappo at gmail.com <mailto:domenico.nappo at gmail.com> >
> >      > > > wrote:
> > >        > > > >        Hi there,
> > > > 
> > > >        I couldn't figure out how to solve this. I have a layer so
> > > > configured:
> > > > 
> > > >        LAYER
> > > >              NAME "layer"
> > > >              TYPE point
> > > >              CONNECTION "conn_string...."
> > > >              CONNECTIONTYPE ORACLESPATIAL
> > > >              TEMPLATE     "templates/hotspot_template.html"
> > > >              DATA "SHAPE FROM (SELECT POINT as SHAPE, ACQ_DATE,
> > > > ACQ_TIME, SATELLITE, CONFIDENCE, VERSION, BRIGHT_T31, FRP FROM
> > > > gwsprd.HOT_SPOTS_MODIS) using unique OGR_FID"
> > > >              METADATA
> > > >                    "wms_title"             "WMS test"
> > > >                    "wms_srs"               "EPSG:4326"
> > > >                    "wms_extent"            "-180 -90 180 90"
> > > >                    "wms_timeextent" "2000-01-01/2020-12-31"
> > > >                    "wms_timeitem" "acq_date"
> > > >                    "wms_timedefault" "2014-01-01"
> > > >                    "wms_timeformat" "YYYY-MM-DD"
> > > >      > > > 
> > > 
> > >      Try with this:
> > > 
> > >      "wms_timeformat" "DD-MM-YY"
> > > 
> > >      it is the default format Oracle expects when converting a string to a
> > > date and no format has been explicitly provided.
> > > 
> > >      Hth,
> > >      Umberto
> > > 
> > > 
> > >        > > > >                    "wms_enable_request" "*"
> > > >                END
> > > >        CLASS
> > > >                    SYMBOL 'circle'
> > > >                    SIZE 2
> > > >                    COLOR        255 0 0
> > > >                END
> > > >            END
> > > > 
> > > > 
> > > > 
> > > >        Now, the date column in the oracle table is the acq_date field.
> > > > 
> > > > 
> > > >        The problem is that Mapserver translates that with a wrong oracle
> > > > query (which it works in postgreslq, I guess):
> > > > 
> > > >        SELECT OGR_FID,rownum, SHAPE FROM
> > > >        (SELECT OGR_FID, POINT as SHAPE,  ACQ_DATE, ACQ_TIME, SATELLITE,
> > > > CONFIDENCE, VERSION, BRIGHT_T31, FRP FROM gwsprd.HOT_SPOTS_MODIS)
> > > >        WHERE  acq_date = '2014-01-16'
> > > > 
> > > > 
> > > >        The query above uses a wrong filter and it gives the error:
> > > > 
> > > >        ORA-01861: literal does not match format string
> > > > 
> > > > 
> > > >        How can I tell to Mapserver to use a correct query??? Or do I
> > > > missing anyhting???
> > > >        Something like:
> > > > 
> > > >        WHERE acq_date = to_date('2014-01-16','YYYY-MM-DD')
> > > >        _______________________________________________
> > > >        mapserver-users mailing list
> > > >        mapserver-users at lists.osgeo.org
> > > > <mailto:mapserver-users at lists.osgeo.org>
> > > >        <http://lists.osgeo.org/mailman/listinfo/mapserver-users>
> > > >      > > >    > >  > 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20140122/5bee52a9/attachment-0001.html>