<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Aug 13, 2024 at 8:09 AM Trygve Aspenes via MapServer-users <<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>> wrote:CONNECTIONTYPE postgis<br></div>CONNECTION "user=pgselectonly password=pgSelectOnly dbname=fremontwy host=localhost port=6543"postgis<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I struggle to find a good subject, but here we go.<br>
<br>
I have a layer using a virtual sql call, returning various columns from <br>
a postgis db. These are used to plot the data and to style the data <br>
(using expressions) pending the values return from the db.<br>
<br>
However I can not make this call filter using BBOX from the request.<br>
<br>
The call looks like this:<br>
# CONNECTION "<br>
# <OGRVRTDataSource><br>
# <OGRVRTLayer name='the_data'><br>
# <SrcDataSource>PG:dbname=mapserver host=mapserver <br>
user=$PGSQ_USERNAME password=$PGSQ_PASSWORD port=5432</SrcDataSource><br>
# <SrcSQL>SELECT EXTRACT(EPOCH FROM time::TIMESTAMP WITH TIME <br>
ZONE) as lxepoch,extract(epoch from '%time%'::timestamp) as <br>
request_time,latitude,longitude FROM the_table where time between <br>
'%time%'::timestamp - interval '300 seconds' and '%time%'</SrcSQL><br>
# <GeometryField encoding='PointFromColumns' x='longitude' <br>
y='latitude'/><br>
# <LayerSRS>WGS84</LayerSRS><br>
# <GeometryType>wkbPoint</GeometryType><br>
# </OGRVRTLayer><br>
# </OGRVRTDataSource>"<br>
# DATA "the_data"<br>
<br>
So I try a postgis connection as this uses BBOX as default like this:<br>
<br>
DATA "geom from (select id,time,geom,EXTRACT(EPOCH FROM time::TIMESTAMP <br>
WITH TIME ZONE) as lxepoch from the_table where time between <br>
'%time%'::timestamp - interval '600 seconds' and '%time%') as subquery <br>
using unique id using srid=4326"<br>
<br>
Now, this returns only the geom and id columns.<br></blockquote><div> </div><div>Why don't you think lxepoch is being returned by the DATA statement above?</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
So my question is: Is there a way to make this call return a lxepoch <br>
column also?<br></blockquote><div><br></div><div>Maybe you should share your layer definition. </div><div><br></div><div>I don't understand what you're doing with the OGRVRTDataSource and the "virtual sql call". Would something like this work for you:</div><div><br></div><div>LAYER</div><div> TYPE point</div><div> VALIDATION</div><div> # appropriate validation for '%time%'</div><div> END</div><div> CONNECTIONTYPE postgis<br> CONNECTION "user= password= dbname= host=localhost"<br></div><div> DATA "geom from (select id,time,geom,EXTRACT(EPOCH FROM time::TIMESTAMP<br> WITH TIME ZONE) as lxepoch from the_table where time between<br> '%time%'::timestamp - interval '600 seconds' and '%time%') as subquery<br> using unique id using srid=4326"<br></div><div> CLASSITEM "lxepoch" # this get lxepoch so that you can use it in CLASS EXPRESSIONS</div><div> CLASS</div><div> EXPRESSION # some expression that uses lxepoch</div><div> ...</div><div> END</div><div> CLASS</div><div><div> EXPRESSION # another expression that uses lxepoch</div><div> ...</div><div> END</div></div><div>END</div><div> </div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Testing with map2img I see<br>
msPostGISParseData: unique_column=id, srid=4326, geom_column_name=geom, <br>
table_name=(select id,time,geom,EXTRACT(EPOCH FROM time::TIMESTAMP WITH <br>
TIME ZONE) as lxepoch from the_table where time between <br>
'2024-07-10T10:35:00Z'::timestamp - interval '600 seconds' and <br>
'2024-07-10T10:35:00Z') as subquery<br>
msPostGISBuildSQL called.<br>
msPostGISBuildSQLItems called.<br>
msPostGISBuildSQLItems: 0 items requested.<br>
msPostGISBuildSQLFrom called.<br>
msPostGISReplaceBoxToken called.<br>
msPostGISBuildSQLWhere called.<br>
msPostGISBuildSQLSRID called.<br>
msPostGISBuildSQLSRID: SRID provided (4326)<br>
msPostGISBuildSQLBox called.<br>
msPostGISLayerWhichShapes query: SELECT ST_AsBinary(("geom"),'NDR') as <br>
geom,"id"::text FROM (select id,time,geom,EXTRACT(EPOCH FROM <br>
time::TIMESTAMP WITH TIME ZONE) as lxepoch from the_table where time <br>
between '2024-07-10T10:35:00Z'::timestamp - interval '600 seconds' and <br>
'2024-07-10T10:35:00Z') as subquery WHERE ST_Intersects("geom", <br>
ST_GeomFromText('POLYGON((-180 -134.887218045113,-180 <br>
134.887218045113,180 134.887218045113,180 -134.887218045113,-180 <br>
-134.887218045113))',4326))<br>
<br>
Copy/pasting the last line into my postgis I get as expected:<br>
geom | id<br>
----------------------------------------------+----<br>
\x010100000066666666666624400000000000c04640 | 1<br>
(1 row)<br>
<br>
And if I add to the select (in the same copy/paste as above) the lxepoch <br>
in by db I get:<br>
geom | id | lxepoch<br>
----------------------------------------------+----+-------------------<br>
\x010100000066666666666624400000000000c04640 | 1 | 1720607700.000000<br>
(1 row)<br>
<br>
<br>
So is it possible to configure the mapserver postgis data query to get <br>
this result?<br>
<br>
I had a look at the code, and a see from the log this function is called <br>
<a href="https://github.com/MapServer/MapServer/blob/main/src/mappostgis.cpp#L1651" rel="noreferrer" target="_blank">https://github.com/MapServer/MapServer/blob/main/src/mappostgis.cpp#L1651</a>, <br>
but the logs says 0 items. I can't read from the code if it is possible <br>
to add other items.<br>
<br>
Any ideas?<br>
<br>
Thanks<br>
<br>
Trygve Aspenes<br>
_______________________________________________<br>
MapServer-users mailing list<br>
<a href="mailto:MapServer-users@lists.osgeo.org" target="_blank">MapServer-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</blockquote></div><br clear="all"><div><br></div><span class="gmail_signature_prefix">-- </span><br><div dir="ltr" class="gmail_signature"><div dir="ltr">Richard W. Greenwood<br><a href="http://www.greenwoodmap.com" target="_blank">www.greenwoodmap.com</a></div></div></div>