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

[Richard Greenwood]

On Tue, Aug 13, 2024 at 8:09 AM Trygve Aspenes via MapServer-users <
mapserver-users at lists.osgeo.org> wrote:CONNECTIONTYPE postgis
CONNECTION "user=pgselectonly password=pgSelectOnly dbname=fremontwy
host=localhost  port=6543"postgis
>
> 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.
>

Why don't you think lxepoch is being returned by the DATA statement above?


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

Maybe you should share your layer definition.

I don't understand what you're doing with the OGRVRTDataSource and the
"virtual sql call". Would something like this work for you:

LAYER
  TYPE point
  VALIDATION
      # appropriate validation for  '%time%'
  END
  CONNECTIONTYPE postgis
  CONNECTION "user= password= dbname= host=localhost"
  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"
  CLASSITEM "lxepoch" # this get lxepoch so that you can use it in CLASS
EXPRESSIONS
  CLASS
       EXPRESSION # some expression that uses lxepoch
       ...
  END
  CLASS
       EXPRESSION # another expression that uses lxepoch
       ...
  END
END



> 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
> _______________________________________________
> MapServer-users mailing list
> MapServer-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>


-- 
Richard W. Greenwood
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240813/54325c7b/attachment.htm>