[MapServer-users] Is there a way to add columns returned from a postgis connection
Seth G
sethg at geographika.co.uk
Wed Aug 14 01:22:06 PDT 2024
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