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

Wed Aug 14 01:37:14 PDT 2024

Ahhhhhhh

Thanks Seth!

Now I understand. Great. I have put my various styles in include files, 
and I skipped those when testing. So mapserver was not aware I needed 
those variables used in my expressions.

Adding in my include files give exactly what I need. Not need to use 
classitem then.

Thanks again Seth

Trygve Aspenes

Den 2024-08-14 10:22, skrev Seth G:
> Hi,
> 
> MapServer will only request the fields from the database that it needs 
> to render the map or required by the request.
> If you aren't using lxepoch for labels, classitems, filters etc. then 
> it won't be requested by MapServer. If you make a GetFeatureInfo or WFS 
> request and ask for all items it will be requested.
> What do you need the lxepoch field for?
> 
> Seth
> 
> --
> web:https://geographika.net & https://mapserverstudio.net
> twitter: @geographika
> 
> On Wed, Aug 14, 2024, at 9:13 AM, Trygve Aspenes via MapServer-users 
> wrote:
>> 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
>> _______________________________________________
>> MapServer-users mailing list
>> MapServer-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/mapserver-users