[mapserver-users] data parameter in tileindex

[Peter Schmitt]

On Mon, Apr 5, 2021 at 5:06 PM Peter Schmitt <pschmitt at gmail.com> wrote:

> On Wed, Mar 24, 2021 at 1:47 AM Rahkonen Jukka (MML) <
> jukka.rahkonen at maanmittauslaitos.fi> wrote:
>
>> perhaps OGR connection to PostGIS would work better than the native one
>> with tileindex.
>>
>
> I am trying to do this as a proof-of-concept tile index of vector files
> where the geom is stored in a Postgis database.  Does anyone have
> experience with the Postgres driver and a MapServer OGR layer?  I can get
> OGR to read my database with something like this:
>
> ogrinfo -ro PG:"host='db' user='test' dbname='testdb' password='123'" -sql
> "SELECT id, geom FROM boundaries LIMIT 1"
>
>
I've gotten a little bit further and wanted to share my results. The
Postgis tile index works great for raster layers, but I could not get it
working for layers of other types (i.e. polygon).  I do not think it is
possible to have a vector file tile index of vectors which is not an ESRI
Shapefile with MapServer without modification.

You can specify tile index layers with either connection OGR or POSTGIS
like so:

#
http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=ogr_example&srsname=EPSG:4326
LAYER
    NAME ogr_example
    TYPE POLYGON
    METADATA
      "gml_include_items"     "all"
    END
    STATUS OFF
    CONNECTIONTYPE OGR

    CONNECTION "PG:host=db user=testuser dbname=testdb password=123
tables=aois(geom)"
    FILTER ("[id]"='8')
END

#
http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=postgis_example&srsname=EPSG:4326
LAYER
    NAME postgis_example
    TYPE POLYGON
    METADATA
      "gml_include_items"     "all"
    END
    STATUS OFF
    CONNECTIONTYPE POSTGIS
    CONNECTION "host=db user=testuser dbname=testdb password=123"
    DATA "geom FROM (SELECT id, geom, 'path_to_valid_vectorfile.gpkg' AS
location FROM aois WHERE id=8) AS subquery USING UNIQUE id USING SRID=4326"
END

and the vector layer served by a tile index, like so:

#
http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=broken&srsname=EPSG:4326
LAYER
   NAME broken
   TYPE POLYGON
   TILEINDEX postgis_example
END

returns "msTiledSHPOpenFile(): Shapefile error. Tileindex layer must be a
shapefile" whether you're using the postgis_example or ogr_example in the
tile index. Looks like the vector file of tile indexes is handled here
https://github.com/MapServer/MapServer/blob/main/mapshape.c#L1958-L1961

One note while experimenting with this: I found advanced queries difficult
to do with OGR connection type. POSTGIS connection type is nice because you
can write arbitrarily complex queries. The OGR type evaluates some code
in mapogr.cpp and seems to generate the SQL query for you.  For example I
could not get Postgres JOIN statements in a single database. I found the
JOIN keyword documented here
https://www.mapserver.org/mapfile/join.html#example-2-join-from-shape-dataset-to-postgresql-table,
but perhaps that is for joining SHP file data with a Postgres table and not
joining two different Postgres tables in a single database.

Anyway, hope this helps someone in the future trying to use Postgis tile
indexes for vector files.

Cheers,
Pete
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20210406/2a102f8d/attachment.html>