[mapserver-users] cgi variable substituition in DATA statement
Rahkonen Jukka
jukka.rahkonen at mmmtike.fi
Thu Aug 22 01:45:41 PDT 2013
Hi,
Pity, it would have been so easy. Now I fear that nothing else than hard work will help and you must systematically re-build your masterpiece SQL step by step. Start from something very simple and make it to work, add more stuff into the query and retry. Begin for example with something as simple as
DATA "wkb_geom FROM (SELECT ico.footprint_poly::geometry as wkb_geom,
ico.service_name FROM ows.imagepool_catalogue_ows ico
WHERE ico.service_name=%STARTDATE%)"
I am pretty sure that at least you will find the exact place where your query comes more clever than Mapserver.
-Jukka Rahkonen-
> -----Alkuperäinen viesti-----
> Lähettäjä: thomas bonfort [mailto:thomas.bonfort at gmail.com]
> Lähetetty: 22. elokuuta 2013 11:19
> Vastaanottaja: Rahkonen Jukka
> Kopio: Peter Freimuth; mapserver-users at lists.osgeo.org
> Aihe: Re: [mapserver-users] cgi variable substituition in DATA statement
>
> That unfortunately will not change anything, the token replacement is done
> blindly, i.e. without taking into account quotes at all.
>
> --
> thomas
>
> On 21 August 2013 23:03, Rahkonen Jukka <jukka.rahkonen at mmmtike.fi>
> wrote:
> > Hi,
> >
> > Blind guess, but perhaps quetes inside the SQL of you DATA leads to
> > unexpected result
> >
> > AND (((acquisition_date >= date_trunc('day',date '%STARTDATE%')
> >
> > Make a try to write this as
> >
> > ________________________________________
> > Lähettäjä: mapserver-users-bounces at lists.osgeo.org
> > [mapserver-users-bounces at lists.osgeo.org] käyttäjän
> > Peter Freimuth [freimuth69 at gmail.com] puolesta
> > Lähetetty: 21. elokuuta 2013 20:43
> > Vastaanottaja: thomas bonfort; mapserver-users at lists.osgeo.org
> > Aihe: Re: [mapserver-users] cgi variable substituition in DATA
> > statement
> >
> > Hi Thomas,
> > thanks for the hints. I compiled 6.4beta2 this morning and applied the
> > proposed changes but the behaviour keeps the same.
> > The substitution is just not done.
> > I changed the VALIDATION block on map and layer level to below and
> > also using now DATAPATTERN '.*' on map level.
> > VALIDATION
> > #'PRODUCTID' '^[a-zA-Z0-9\-\_\,]+$'
> > #'CONTRACTIDS' '^[a-zA-Z0-9\-\_\,]+$'
> > #'STARTDATE' '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> > #'ENDDATE' '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> > 'PRODUCTID' '.*'
> > 'CONTRACTIDS' '.*'
> > 'STARTDATE' '.*'
> > 'ENDDATE' '.*'
> > 'default_PRODUCTID' '1234567'
> > 'default_CONTRACTIDS' 'GDNAMLI-USA-MT'
> > 'default_STARTDATE' '2011-01-01'
> > 'default_ENDDATE' '2013-09-01'
> > END
> > It seems that the mechanismn is not working at all and i cannot find
> > anything in the logs even with debug level 5.
> > Any further idea?
> > Thanks,
> > Peter
> >
> > On 20/08/2013 5:35 PM, thomas bonfort wrote:
> >> try setting your VALIDATIONs to '.*' to see if your problem lies in
> >> your regex or elsewhere. For 6.3/4 You can also remove your
> >> xxx_validation_pattern metadata entries, and should move the
> >> default_xxx ones into the VALIDATION block.
> >>
> >> --
> >> thomas
> >>
> >> On 21 August 2013 01:26, Peter Freimuth <freimuth69 at gmail.com> wrote:
> >>> Hi,
> >>> i am trying to get the dynamic variable substitution work on
> >>> Mapserver 6.3dev but somehow it does'nt work as it worked before and as
> documented.
> >>> When i execute
> >>> http://10.10.4.18/cgi-bin/LI_USA-
> MT.cgi?SERVICE=WMS&VERSION=1.3.0&RE
> >>> QUEST=GetMap&BBOX=48.046271,-115.929840,48.198407,-
> 115.666914&CRS=EP
> >>>
> SG:4326&WIDTH=1647&HEIGHT=953&LAYERS=data_view&STYLES=&FORMAT=i
> mage/
> >>> png&DPI=96&TRANSPARENT=TRUE&STARTDATE=2011-01-
> 01&ENDDATE=2013-09-01
> >>>
> >>> or
> >>>
> >>> http://10.10.4.18/cgi-bin/LI_USA-
> MT.cgi?SERVICE=WMS&VERSION=1.3.0&RE
> >>> QUEST=GetMap&BBOX=48.046271,-115.929840,48.198407,-
> 115.666914&CRS=EP
> >>>
> SG:4326&WIDTH=1647&HEIGHT=953&LAYERS=data_view&STYLES=&FORMAT=i
> mage/
> >>> png&DPI=96&TRANSPARENT=TRUE
> >>>
> >>> I get an error in
> >>> msDrawMap(): Image handling error. Failed to draw layer named
> 'data_view'.
> >>> msPostGISLayerWhichShapes(): Query error. Error executing query: ERROR:
> >>> invalid input syntax for type date: "%STARTDATE%"
> >>> LINE 8: ... (((acquisition_date >= date_trunc('day',date '%STARTDAT...
> >>> ^ So
> >>> the substitution does not happen or the default is not applied.
> >>> Any hint on what i am doing wrong would be great.
> >>>
> >>> See below the components from the mapfile:
> >>>
> >>> MAP
> >>> .....
> >>> #
> >>> # Start of web interface definition
> >>> #
> >>> DATAPATTERN '^[a-zA-Z0-9\-\_\,]+$'
> >>>
> >>> WEB
> >>> IMAGEPATH
> "/mnt/mapserver/mapserver_wms/geocloud_config/image_path"
> >>> METADATA
> >>> .....
> >>> 'PRODUCTID_validation_pattern' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'default_PRODUCTID' '1234567'
> >>> 'CONTRACTIDS_validation_pattern' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'default_CONTRACT_IDS' '0000'
> >>> 'STARTDATE_validation_pattern'
> >>> '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> 'default_STARTDATE' '2011-01-01'
> >>> 'ENDDATE_validation_pattern'
> >>> '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> 'default_END_DATE' '2013-09-01'
> >>> END
> >>> VALIDATION
> >>> 'PRODUCTID' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'CONTRACTIDS' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'STARTDATE' '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> 'ENDDATE' '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> END
> >>> END
> >>> .....
> >>> LAYER
> >>> # this is the tileindex that aggregates the single image
> >>> products together and makes them accessible for the RASTER LAYER
> >>> "data_view" and "data_raw" for rendering
> >>> NAME "data_idx"
> >>> STATUS ON
> >>> DEBUG 5
> >>> UNITS dd
> >>> TYPE POLYGON
> >>> EXTENT -116.364631680367 44.1932837062986 -103.698350095065
> >>> 49.1892799088128
> >>> PROCESSING "CLOSE_CONNECTION=DEFER"
> >>> CONNECTIONTYPE POSTGIS
> >>> CONNECTION "user=***** password=***** dbname=*****
> host='*****'
> >>> port=5432"
> >>> DATA "wkb_geom FROM (SELECT t1.* FROM (SELECT re3aip.cat_id
> >>> as archived_in_archiving_identifier, ico.image_id,
> >>> ico.acquisition_date, re3aip.tile_id,
> >>> ico.product_provider_image_identifier, re3aip.ohs_order_id,
> >>> re3aip.imagetake_cat_id, ico.epsg_code, re3aip.ohs_contract_id,
> >>> ico.service_name, ico.footprint_poly::geometry as wkb_geom,
> >>> ico.image_location, ico.vrt_location FROM
> >>> ows.imagepool_catalogue_ows ico JOIN ows.re_l3a_imagepool re3aip ON
> >>> (re3aip.image_id=ico.image_id) WHERE ico.service_name in
> >>> ('LI_USA-MT') AND (((acquisition_date >= date_trunc('day',date
> >>> '%STARTDATE%') AND acquisition_date < date_trunc('day',date
> >>> '%ENDDATE%') + interval '1 day'))) AND re3aip.ohs_contract_id in
> >>> ('%CONTRACTID%')
> >>> ) t1
> >>> LEFT OUTER JOIN
> >>> (SELECT ico.image_id, ico.acquisition_date, re3aip.tile_id FROM
> >>> ows.imagepool_catalogue_ows ico JOIN ows.re_l3a_imagepool re3aip ON
> >>> (re3aip.image_id=ico.image_id) WHERE ico.service_name in
> >>> ('LI_USA-MT') AND
> >>> (((acquisition_date >=
> >>> date_trunc('day',date
> >>> '%STARTDATE%') AND
> >>> acquisition_date < date_trunc('day',date '%ENDDATE%') + interval '1
> >>> day'))) AND re3aip.ohs_contract_id in ('%CONTRACTID%')
> >>> ) t2
> >>>
> >>> ON t1.tile_id = t2.tile_id AND ((t1.acquisition_date <
> >>> t2.acquisition_date) OR (t1.acquisition_date = t2.acquisition_date
> >>> AND t1.image_id < t2.image_id)) WHERE t2.tile_id is NULL ORDER BY
> >>> t1.ohs_contract_id, t1.tile_id
> >>> ) foo USING UNIQUE image_id
> >>> USING SRID=4326"
> >>> #FILTER ( '[product_provider_image_identifier]'='%PRODUCTID%')
> >>> PROJECTION
> >>> "init=epsg:4326"
> >>> END
> >>> METADATA
> >>> "ows_title" "Data Index"
> >>> "ows_extent" "-116.364631680367 44.1932837062986
> >>> -103.698350095065 49.1892799088128"
> >>> #'PRODUCTID_validation_pattern' '^[a-zA-Z0-9\-\_\,]+$'
> >>> #'default_PRODUCTID' '1234567'
> >>> 'CONTRACTIDS_validation_pattern' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'default_CONTRACTIDS' '0000'
> >>> 'STARTDATE_validation_pattern'
> >>> '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> 'default_STARTDATE' '2011-01-01'
> >>> 'ENDDATE_validation_pattern'
> >>> '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> 'default_ENDDATE' '2013-09-01'
> >>> #disable explicitly everything that shall not be
> >>> supported for this Layer
> >>> "wms_enable_request" "!GetCapabilities !GetMap
> >>> !GetLegendgraphic"
> >>> "wcs_enable_request" "!GetCapabilities !GetCoverage
> >>> !DescribeCoverage"
> >>> "wfs_enable_request" "!GetCapabilities !GetFeature
> >>> !DescribeFeatureType"
> >>> END
> >>> VALIDATION
> >>> 'PRODUCTID' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'CONTRACT_IDS' '^[a-zA-Z0-9\-\_\,]+$'
> >>> 'STARTDATE' '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> 'END_DATE' '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
> >>> END
> >>> END
> >>> ....
> >>> END
> >>> _______________________________________________
> >>> mapserver-users mailing list
> >>> mapserver-users at lists.osgeo.org
> >>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> >
> > _______________________________________________
> > mapserver-users mailing list
> > mapserver-users at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/mapserver-users
More information about the mapserver-users
mailing list