<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Thorsten - <br>
</p>
<p>It seems that your workstation has QGIS installed (The picture of
file explorer shows a QGIS icon for one of the shape files). Why
dont you use the DBManager function in QGIS to test your SQL ? <br>
</p>
<p><span lang="EN-US">Sanitized version of the select:<br>
</span></p>
<p><span lang="EN-US"></span><b><font face="monospace"><span
lang="EN-US">--<br>
<br>
SELECT <br>
name, <br>
left, <br>
top,<br>
right, <br>
bottom, <br>
geom <br>
FROM grids.grid_1_25grad <br>
WHERE <br>
ST_Intersects(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and'))) AND NOT <br>
ST_Touches (geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')))</span></font></b></p>
<p>--<br>
</p>
<ul>
<li>You are selecting features from table <span lang="EN-US"><i>grids.grid_1_25grad</i>.
The name of the table indicates it contains grid cells - like
the ones you show in the picture.<br>
<br>
</span></li>
<li><span lang="EN-US">The SQL where-part contains a combination
of st_intersects (geom, <i>..some geometry resulting
funtion..</i>) and not st_touches (geom, </span><span
lang="EN-US"><span lang="EN-US"><i>..same geometry resulting
funtion..</i>)</span></span><br>
"intersects" and "not touches" is equivalent to "within", so you
can probably rewrite the where part to: <b><font
face="monospace"><span lang="EN-US"><br>
<br>
WHERE ST_Within(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')))</span></font></b></li>
</ul>
<p><b><font face="monospace"><span lang="EN-US"><br>
</span></font></b></p>
<pre class="moz-signature" cols="72">Med venlig hilsen / Best regards
Bo Victor Thomsen
</pre>
<div class="moz-cite-prefix">Den 09-11-2022 kl. 14:53 skrev Rahkonen
Jukka:<br>
</div>
<blockquote type="cite"
cite="mid:AM0PR09MB3219EAD83D5330CD8BD2E03FFD3E9@AM0PR09MB3219.eurprd09.prod.outlook.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style>@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;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}span.Shkpostityyli20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}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]-->
<div class="WordSection1">
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">You are selecting data
from table </span>
<span lang="EN-US">grids.grid_1_25grad. I suppose that table
contains grid polygons. By your image you seem to want some
buffered geometry. Spend some time for thinking about what
data do you want and where to get it. You can test your
queries with for example pgAdmin that can preview the
geometries. Or use OpenJUMP that is an excellent tool for
visualizing PostGIS query results.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-</span><span
lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="mso-fareast-language:FI">Lähettäjä:</span></b><span
style="mso-fareast-language:FI"> Leber, Thorsten
<a class="moz-txt-link-rfc2396E" href="mailto:Thorsten.Leber@hensoldt.net"><Thorsten.Leber@hensoldt.net></a>
<br>
<b>Lähetetty:</b> keskiviikko 9. marraskuuta 2022 15.46<br>
<b>Vastaanottaja:</b> Rahkonen Jukka
<a class="moz-txt-link-rfc2396E" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><jukka.rahkonen@maanmittauslaitos.fi></a><br>
<b>Aihe:</b> AW: org2org<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="DE">Hi Jukka,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="DE"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">with geometry al 5 files
are created<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogr2ogr -f "ESRI
Shapefile" C:\RenderTest\raster_clipper_and.shp
PG:"host=10.49.20.42 port=5432 user=tilemill password=test
dbname=nextgen" -sql "SELECT \""name\"", \""left\"", top,
\""right\"", bottom, geom FROM grids.grid_1_25grad WHERE
ST_Intersects(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and'))) AND NOT
ST_Touches(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')))" -s_srs
"EPSG:4326" -t_srs "EPSG:4326" -overwrite -lco ENCODING=utf8<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">but it looks strange<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img
style="width:3.0486in;height:3.7569in" id="Grafik_x0020_3"
src="cid:part1.S9Fs80IP.bFT0d9cx@gmail.com" class=""
width="293" height="361"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I would expect this<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img
style="width:3.5833in;height:3.5416in" id="Grafik_x0020_4"
src="cid:part2.WjTG6gpg.uPVvrjCA@gmail.com" class=""
width="344" height="340"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Thorsten<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="mso-fareast-language:DE" lang="DE">Von:</span></b><span
style="mso-fareast-language:DE" lang="DE"> Rahkonen
Jukka <<a
href="mailto:jukka.rahkonen@maanmittauslaitos.fi"
moz-do-not-send="true" class="moz-txt-link-freetext">jukka.rahkonen@maanmittauslaitos.fi</a>>
<br>
<b>Gesendet:</b> Mittwoch, 9. November 2022 13:10<br>
<b>An:</b> Leber, Thorsten <<a
href="mailto:Thorsten.Leber@hensoldt.net"
moz-do-not-send="true" class="moz-txt-link-freetext">Thorsten.Leber@hensoldt.net</a>><br>
<b>Betreff:</b> Re: org2org<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="DE"><o:p> </o:p></span></p>
<table class="MsoNormalTable" style="width:87.0%;border:solid
red 1.5pt" width="87%" cellspacing="3" cellpadding="0"
border="1">
<tbody>
<tr>
<td style="border:none;padding:.75pt .75pt .75pt .75pt">
<p><span
style="font-size:9.0pt;font-family:"Arial",sans-serif;color:black">This
message was sent from outside of HENSOLDT. Please do
not click on links or open attachments unless you
validate the source of this email and know the
content is safe.
<o:p></o:p></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span style="mso-fareast-language:DE"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">Try to add a geometry
into the selection -sql "SELECT \""name\"", \""left\"",
top, \""right\"", bottom FROM..<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="mso-fareast-language:FI">Lähettäjä:</span></b><span
style="mso-fareast-language:FI"> Leber, Thorsten <<a
href="mailto:Thorsten.Leber@hensoldt.net"
moz-do-not-send="true" class="moz-txt-link-freetext">Thorsten.Leber@hensoldt.net</a>>
<br>
<b>Lähetetty:</b> keskiviikko 9. marraskuuta 2022
13.53<br>
<b>Vastaanottaja:</b> Rahkonen Jukka <<a
href="mailto:jukka.rahkonen@maanmittauslaitos.fi"
moz-do-not-send="true" class="moz-txt-link-freetext">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Aihe:</b> AW: org2org<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">ogr2ogr -f "ESRI
Shapefile" C:\RenderTest\raster_clipper_and.shp
PG:"host=10.49.20.42 port=5432 user=tilemill password=test
dbname=nextgen" -sql "SELECT \""name\"", \""left\"", top,
\""right\"", bottom FROM grids.grid_1_25grad WHERE
ST_Intersects(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and'))) AND NOT
ST_Touches(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')))" -s_srs
EPSG:4326 -t_srs EPSG:4326 -overwrite -lco ENCODING=utf8<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">this works now without
any warning but still only 3 files in output folder<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="mso-fareast-language:DE" lang="DE">Von:</span></b><span
style="mso-fareast-language:DE" lang="DE"> Rahkonen
Jukka <<a
href="mailto:jukka.rahkonen@maanmittauslaitos.fi"
moz-do-not-send="true" class="moz-txt-link-freetext">jukka.rahkonen@maanmittauslaitos.fi</a>>
<br>
<b>Gesendet:</b> Mittwoch, 9. November 2022 12:21<br>
<b>An:</b> Leber, Thorsten <<a
href="mailto:Thorsten.Leber@hensoldt.net"
moz-do-not-send="true" class="moz-txt-link-freetext">Thorsten.Leber@hensoldt.net</a>>;
<a href="mailto:gdal-dev@lists.osgeo.org"
moz-do-not-send="true" class="moz-txt-link-freetext">gdal-dev@lists.osgeo.org</a><br>
<b>Betreff:</b> Re: org2org<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="DE"><o:p> </o:p></span></p>
<table class="MsoNormalTable" style="width:87.0%;border:solid
red 1.5pt" width="87%" cellspacing="3" cellpadding="0"
border="1">
<tbody>
<tr>
<td style="border:none;padding:.75pt .75pt .75pt .75pt">
<p><span
style="font-size:9.0pt;font-family:"Arial",sans-serif;color:black">This
message was sent from outside of HENSOLDT. Please
do not click on links or open attachments unless
you validate the source of this email and know the
content is safe.
<o:p></o:p></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span style="mso-fareast-language:DE"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">Does the SQL part
work with a tool like pgAdmin? Notice that the inner
double quotes must be escaped as \” in the ogr2ogr
command or otherwise they will truncate the -sql
parameter. And it seems that you did not select the
geometry field. Generally I would suggest to start with
a simple SQL and add complexity once you have gotten a
good result. Suggestion includes testing with easy table
names first before forwarding to names like
countries.geometries_boundary_buffer_10km('and').<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="mso-fareast-language:FI">Lähettäjä:</span></b><span
style="mso-fareast-language:FI"> gdal-dev <<a
href="mailto:gdal-dev-bounces@lists.osgeo.org"
moz-do-not-send="true"
class="moz-txt-link-freetext">gdal-dev-bounces@lists.osgeo.org</a>>
<b>Puolesta </b>Leber, Thorsten<br>
<b>Lähetetty:</b> keskiviikko 9. marraskuuta 2022
13.02<br>
<b>Vastaanottaja:</b> <a
href="mailto:gdal-dev@lists.osgeo.org"
moz-do-not-send="true"
class="moz-txt-link-freetext">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> [gdal-dev] org2org<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">Hi All, <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I am trying to use
org2org with the following command:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogr2ogr -f "ESRI
Shapefile" C:\RenderTest\raster_clipper_and.shp
PG:"host=10.49.20.42 port=5432 user=tilemill
password=test dbname=nextgen" -sql "SELECT "name",
"left", top, "right", bottom FROM grids.grid_1_25grad
WHERE ST_Intersects(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and'))) AND
NOT ST_Touches(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')))"
-s_srs EPSG:4326 -t_srs EPSG:4326 -overwrite -lco
ENCODING=utf8<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I expect in the
output folder 5 files<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img
style="width:1.868in;height:1.2083in"
id="Grafik_x0020_1"
src="cid:part3.jpaCOImf.LWSc5iNK@gmail.com" class=""
width="179" height="116" border="0"></span><span
lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">But I receive a
warning:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Warning 1: layer
names ignored in combination with -sql.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">And get only 3 files<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img
style="width:1.6666in;height:.75in"
id="Grafik_x0020_2"
src="cid:part4.Y5tFzctg.2Fpho932@gmail.com" class=""
width="160" height="72" border="0"></span><span
lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I am missing the
.shp and the .shx file. Is there something wrong in my
command?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">KR<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Thorsten<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
gdal-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
</body>
</html>