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

Wed Aug 14 00:13:27 PDT 2024

Thank you Richard

Den 2024-08-13 23:27, skrev 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?

When debugging with map2img I see the call send to my postgis db, and 
lxepoch is not included. (The call I see was included in the original 
email)

> 
>> 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
> 

Yes, using classitem includes lxepoch. I have looked in the 
documentation, but did not understand from the documentation that 
classitem had this effect.

However, I also need another variable, and classitem can only have one. 
So I'm still stuck.

So I guess I have to find another way.

But thanks again Richard, this was useful!

Trygve Aspenes

>> 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 [1]
> 
> Links:
> ------
> [1] http://www.greenwoodmap.com