[MapServer-users] How do I use an SQL query in a Postgis tileindex of vector files?

[Peter Schmitt]

Hi,

How do I get a tileindex of vector files working from data in a Postgis
database and accessed via an SQL query of my choosing?

I would like to make a vector tile index as described at
https://mapserver.org/optimization/vector.html#index-your-data using data
stored in a Postgis database and a SQL query of my choosing.  Here is one
approach that works:

1.  create a test table in postgres database:
     > create table testtable (id int, location text, geom
geometry(Polygon,4326));

2.  insert data into the table:

     insert into testtable (id, location, geom) values (1,
'/test_data/test.geojson,0', 'POLYGON((122.01377475333321
29.96355712017804,122.01424752183958 29.915620560607106,122.06927507957919
29.91602043815968,122.0688286928125 29.963957768165944,122.01377475333321
29.96355712017804))'::geometry);

3. Define a layer using the OGR connection:

LAYER
  NAME "vector"
  TYPE POLYGON
  CONNECTIONTYPE OGR
  CONNECTION "PG:\"host=localhost user=postgres dbname=mydatabase
password=mypwd tables=testtable(geom)\""
  STATUS OFF
  CLASS
    STYLE
      WIDTH 1
      OUTLINECOLOR 255 105 180
    END
  END
END

4. Issue a request:

    mapserv
QUERY_STRING='SERVICE=WMS&map=/tmpp/mapfile.map&VERSION=1.3.0&REQUEST=GetMap&FORMAT=image/png&layers=vector&width=256&height=256&crs=EPSG:3857&bbox=13580108.193257555,3487974.474709164,13585000.163067807,3492866.4445194146'

and I get an image of my vectors back.

How do I use a more complex query than `testtable(geom)`?  I tried
specifying a query via a DATA block:

LAYER
    NAME "vector"
    TYPE POLYGON
    CONNECTIONTYPE OGR
    CONNECTION "PG:\"host=localhost user=postgres dbname=mydatabase
password=mypwd\""
    DATA "geom FROM (SELECT
        id,
        geom,
        location
      FROM testtable
    ) AS subquery USING unique id USING SRID=4326"
    PROJECTION
      "init=epsg:4326"
    END
    STATUS OFF
    CLASS
      STYLE
        WIDTH 1
        OUTLINECOLOR 255 105 180
      END
    END
  END

However, the same query generates an error:

mapserv
QUERY_STRING='SERVICE=WMS&map=/tmpp/mapfile.map&VERSION=1.3.0&REQUEST=GetMap&FORMAT=image/png&layers=vector&width=256&height=256&crs=EPSG:3857&bbox=13580108.193257555,3487974.474709164,13585000.163067807,3492866.4445194146'

"msOGRFileOpen(): OGR error. Open failed for OGR connection in layer
`vector'.  File not found or unsupported format. Check server logs.
msDrawMap(): Image handling error. Failed to draw layer named 'vector'"

I tried using a postgis connectiontype, but could not get that working
either https://mapserver.org/mapfile/layer.html#mapfile-layer-connectiontype

How do I get a tileindex of vector files working from data in a Postgis
database and accessed via an SQL query of my choosing?

Thanks!
Pete
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20221206/911913b0/attachment.htm>