<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.Shkpostityyli19
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">We are using the ”layer-as-a-tileindex” method right now with a bit older Mapserver version and with native POSTGIS connection type. In the orthophoto layer we use<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">LAYER<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> GROUP "ortokuva"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> NAME "ortokuva_r16"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">
</span><span style="mso-fareast-language:EN-US">STATUS ON<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"> TILEINDEX "ortokuva_r16_postgis_tileindex"<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"> </span><span lang="EN-US" style="mso-fareast-language:EN-US">TILESRS "crs"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">and in the "ortokuva_r16_postgis_tileindex" layer
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">LAYER<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> NAME "ortokuva_r16_postgis_tileindex"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> STATUS ON<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> TYPE POLYGON<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> UNITS METERS<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> SIZEUNITS PIXELS<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> CONNECTIONTYPE POSTGIS<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> CONNECTION "dbname=db user=user password=password"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> DATA "the_geom from (SELECT fid, '/beginning/of/path/'||location as location, crs, resolution, the_geom FROM ortokuva_16m) as subquery using unique fid"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">I hope that this still works with Mapserver 8. It would be nice if you could make a test and compare OGR vs POSTGIS connection types.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b>Lähettäjä:</b> Peter Schmitt <pschmitt@gmail.com> <br>
<b>Lähetetty:</b> torstai 8. joulukuuta 2022 2.32<br>
<b>Vastaanottaja:</b> Even Rouault <even.rouault@spatialys.com><br>
<b>Kopio:</b> Rahkonen Jukka <jukka.rahkonen@maanmittauslaitos.fi>; Mapserver-Users (mapserver-users@lists.osgeo.org) <mapserver-users@lists.osgeo.org><br>
<b>Aihe:</b> Re: [MapServer-users] [EXTERNAL] Vector tileindex with connectiontype OGR<o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Wed, Dec 7, 2022 at 4:17 PM Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>> wrote:<o:p></o:p></p>
</div>
<div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<div>
<p class="MsoNormal">How do I specify such a query using the OGR PG driver as a Mapserver layer to be used as a TILEINDEX of OGR vector files?
<o:p></o:p></p>
</div>
</div>
</div>
</blockquote>
<p class="MsoNormal" style="margin-bottom:12.0pt">A potential solution would be to create a OGR VRT file that contains your SQL request, and reference that VRT file in the TILEINDEX<o:p></o:p></p>
</div>
</blockquote>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Brilliant! This indeed works:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"> LAYER<br>
NAME "vector_via_vrt"<br>
TYPE POLYGON<br>
TILEINDEX "<br>
<OGRVRTDataSource><br>
<OGRVRTLayer name='layer'><br>
<SrcDataSource>PG:host='localhost' user='postgres' dbname='mydatabase' password='mypwd'</SrcDataSource><br>
<SrcSQL>SELECT * FROM testtable</SrcSQL><br>
</OGRVRTLayer><br>
</OGRVRTDataSource>"<br>
CONNECTIONTYPE OGR<br>
STATUS OFF<o:p></o:p></p>
<div>
<p class="MsoNormal"> END <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">and now requests like <a href="http://localhost:8000/wfs?service=WFS&VERSION=1.0.0&REQUEST=getfeature&map=/app/mapfiles/test.map&typename=vector_via_vrt&OUTPUTFORMAT=geojson">
http://localhost:8000/wfs?service=WFS&VERSION=1.0.0&REQUEST=getfeature&map=/app/mapfiles/test.map&typename=vector_via_vrt&OUTPUTFORMAT=geojson</a> return the vectors pointed by the vector tileindex of vectors. I will attempt to submit a PR to add this example
to the docs when I can.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">VRTs: Is there anything they can't do!?<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Cheers,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Pete<o:p></o:p></p>
</div>
</div>
</div>
</div>
</body>
</html>