[MapServer-users] Is there a way to add columns returned from a postgis connection

[Trygve Aspenes]

I struggle to find a good subject, but here we go.

I have a layer using a virtual sql call, returning various columns from 
a postgis db. These are used to plot the data and to style the data 
(using expressions) pending the values return from the db.

However I can not make this call filter using BBOX from the request.

The call looks like this:
#        CONNECTION "
#        <OGRVRTDataSource>
#          <OGRVRTLayer name='the_data'>
#            <SrcDataSource>PG:dbname=mapserver host=mapserver 
user=$PGSQ_USERNAME password=$PGSQ_PASSWORD port=5432</SrcDataSource>
#            <SrcSQL>SELECT EXTRACT(EPOCH FROM time::TIMESTAMP WITH TIME 
ZONE) as lxepoch,extract(epoch from '%time%'::timestamp) as 
request_time,latitude,longitude FROM the_table where time between 
'%time%'::timestamp - interval '300 seconds' and '%time%'</SrcSQL>
#            <GeometryField encoding='PointFromColumns' x='longitude' 
y='latitude'/>
#            <LayerSRS>WGS84</LayerSRS>
#            <GeometryType>wkbPoint</GeometryType>
#          </OGRVRTLayer>
#        </OGRVRTDataSource>"
#        DATA "the_data"

So I try a postgis connection as this uses BBOX as default like this:

DATA "geom from (select id,time,geom,EXTRACT(EPOCH FROM time::TIMESTAMP 
WITH TIME ZONE) as lxepoch from the_table where time between 
'%time%'::timestamp - interval '600 seconds' and '%time%') as subquery 
using unique id using srid=4326"

Now, this returns only the geom and id columns.

So my question is: Is there a way to make this call return a lxepoch 
column also?

Testing with map2img I see
msPostGISParseData: unique_column=id, srid=4326, geom_column_name=geom, 
table_name=(select id,time,geom,EXTRACT(EPOCH FROM time::TIMESTAMP WITH 
TIME ZONE) as lxepoch from the_table where time between 
'2024-07-10T10:35:00Z'::timestamp - interval '600 seconds' and 
'2024-07-10T10:35:00Z') as subquery
msPostGISBuildSQL called.
msPostGISBuildSQLItems called.
msPostGISBuildSQLItems: 0 items requested.
msPostGISBuildSQLFrom called.
msPostGISReplaceBoxToken called.
msPostGISBuildSQLWhere called.
msPostGISBuildSQLSRID called.
msPostGISBuildSQLSRID: SRID provided (4326)
msPostGISBuildSQLBox called.
msPostGISLayerWhichShapes query: SELECT ST_AsBinary(("geom"),'NDR') as 
geom,"id"::text FROM (select id,time,geom,EXTRACT(EPOCH FROM 
time::TIMESTAMP WITH TIME ZONE) as lxepoch from the_table where time 
between '2024-07-10T10:35:00Z'::timestamp - interval '600 seconds' and 
'2024-07-10T10:35:00Z') as subquery WHERE ST_Intersects("geom", 
ST_GeomFromText('POLYGON((-180 -134.887218045113,-180 
134.887218045113,180 134.887218045113,180 -134.887218045113,-180 
-134.887218045113))',4326))

Copy/pasting the last line into my postgis I get as expected:
                      geom                     | id
----------------------------------------------+----
  \x010100000066666666666624400000000000c04640 | 1
(1 row)

And if I add to the select (in the same copy/paste as above) the lxepoch 
in by db I get:
                     geom                     | id |      lxepoch
----------------------------------------------+----+-------------------
  \x010100000066666666666624400000000000c04640 | 1  | 1720607700.000000
(1 row)


So is it possible to configure the mapserver postgis data query to get 
this result?

I had a look at the code, and a see from the log this function is called 
https://github.com/MapServer/MapServer/blob/main/src/mappostgis.cpp#L1651, 
but the logs says 0 items. I can't read from the code if it is possible 
to add other items.

Any ideas?

Thanks

Trygve Aspenes