On Mon, Apr 5, 2021 at 5:06 PM Peter Schmitt <[email protected]> wrote:
> On Wed, Mar 24, 2021 at 1:47 AM Rahkonen Jukka (MML) < > [email protected]> 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
_______________________________________________ mapserver-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/mapserver-users
