[MapServer-users] Is there a way to add columns returned from a postgis connection
Seth G
sethg at geographika.co.uk
Wed Aug 14 02:48:05 PDT 2024
No problem Trygve.
WMS requests have had this optimisation for a while, but it was further expanded in MapServer 8.0.1 with https://github.com/MapServer/MapServer/pull/6786 and also applied to WFS in https://github.com/MapServer/MapServer/pull/6785.
Some MapServer layers could have 20-30 fields available for various styles, info requests etc. but by only bringing back the fields/data that would actually be used to handle the request there were big performance improvements in certain cases.
Seth
--
web:https://geographika.net & https://mapserverstudio.net
twitter: @geographika
On Wed, Aug 14, 2024, at 10:37 AM, Trygve Aspenes wrote:
> 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
More information about the MapServer-users
mailing list