<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Apr 5, 2021 at 5:06 PM Peter Schmitt <<a href="mailto:pschmitt@gmail.com">pschmitt@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div dir="ltr">On Wed, Mar 24, 2021 at 1:47 AM Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<br></div><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="FI">
<div>
<p class="MsoNormal">perhaps OGR connection to PostGIS would work better than the native one with tileindex.</p></div></div></blockquote><div><br></div><div>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:</div><div><br></div><div>ogrinfo -ro PG:"host='db' user='test' dbname='testdb' password='123'" -sql "SELECT id, geom FROM boundaries LIMIT 1"<br></div><div><br></div></div></div></blockquote><div><br></div><div>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.</div><div><br></div><div>You can specify tile index layers with either connection OGR or POSTGIS like so:</div><div><br></div><div># <a href="http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=ogr_example&srsname=EPSG:4326">http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=ogr_example&srsname=EPSG:4326</a></div><div>LAYER<br> NAME ogr_example<br> TYPE POLYGON<br> METADATA<br> "gml_include_items" "all"<br> END<br> STATUS OFF<br> CONNECTIONTYPE OGR<br><br> CONNECTION "PG:host=db user=testuser dbname=testdb password=123 tables=aois(geom)"<br> FILTER ("[id]"='8')<br>END<br><br># <a href="http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=postgis_example&srsname=EPSG:4326">http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=postgis_example&srsname=EPSG:4326</a><br>LAYER<br> NAME postgis_example<br> TYPE POLYGON<br> METADATA<br> "gml_include_items" "all"<br> END<br> STATUS OFF<br> CONNECTIONTYPE POSTGIS<br> CONNECTION "host=db user=testuser dbname=testdb password=123"<br> 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"<br>END<br><br>and the vector layer served by a tile index, like so:<br><br># <a href="http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=broken&srsname=EPSG:4326">http://localhost/mapserv?service=WFS&VERSION=1.1.0&REQUEST=getfeature&typename=broken&srsname=EPSG:4326</a><br>LAYER<br> NAME broken<br> TYPE POLYGON<br> TILEINDEX postgis_example<br>END<br></div><div><br></div><div>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 <a href="https://github.com/MapServer/MapServer/blob/main/mapshape.c#L1958-L1961">https://github.com/MapServer/MapServer/blob/main/mapshape.c#L1958-L1961</a></div><div><br></div><div>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 <a href="https://www.mapserver.org/mapfile/join.html#example-2-join-from-shape-dataset-to-postgresql-table">https://www.mapserver.org/mapfile/join.html#example-2-join-from-shape-dataset-to-postgresql-table</a>, but perhaps that is for joining SHP file data with a Postgres table and not joining two different Postgres tables in a single database.</div><div><br></div><div>Anyway, hope this helps someone in the future trying to use Postgis tile indexes for vector files.</div><div><br></div><div>Cheers,</div><div>Pete</div><div><br></div><div><br></div><div><br></div></div></div>