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

[Scott]

Your connectiontype is ogr, you need postgis for a query


CONNECTIONTYPE POSTGIS

On 12/6/22 10:39, Peter Schmitt wrote:
> 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 
> <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 
> <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
> 
> _______________________________________________
> MapServer-users mailing list
> MapServer-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users