<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Sorry to pollute this list (again !) with non GDAL related
information, however -</p>
<p>I made a mistake in my answer about the SQL statement and the
nerd part of my brain just won't let go. So:</p>
<ul>
<li><span lang="EN-US">The SQL where-part contains a combination
of st_intersects (geom, <i>..some geometry resulting
function..</i>) and not st_touches (geom, </span><span
lang="EN-US"><span lang="EN-US"><i>..same geometry resulting
function..</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><font
face="monospace"><span lang="EN-US">))</span></font></li>
</ul>
<p><span lang="EN-US">The above is <i>not</i> correct. The
equivalent to "intersects and not touches" is "within or
overlaps". The where statement should be: <br>
</span></p>
<p><b><font face="monospace"><span lang="EN-US">WHERE
ST_Within(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')</span></font></b><font
face="monospace"><span lang="EN-US">)) <b>OR</b> <br>
</span></font><b><font face="monospace"><span
lang="EN-US">ST_Overlaps(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')</span></font></b><font
face="monospace"><span lang="EN-US">))</span></font></p>
<p><span lang="EN-US">Which is marginally better than the original
where.</span></p>
<p><span lang="EN-US">But if you read the entire SQL statement, I
would assume the purpose would be something like: "<i>Fetch all
gridcells that is wihtin a 10 km. distance from a set of
countries"</i><i>.</i></span></p>
<p><span lang="EN-US">In that case the where part could be
simplified (and running considerably faster) by using
ST_DWithin:</span></p>
<p><b><font face="monospace"><span lang="EN-US">SELECT <br>
a.name, <br>
a.left, <br>
a.top,<br>
a.right, <br>
a.bottom, <br>
a.geom <br>
FROM grids.grid_1_25grad a<br>
JOIN countries.table_with_countries b ON ST_DWIthin (a.geom,
b.geom, 10000.0)</span></font></b></p>
<p><span lang="EN-US">If the 2 tables involved has the same SRID
(using meters as distance unit) and the spatial columns is of
type geometry <br>
</span><b><span lang="EN-US"></span><font face="monospace"><span
lang="EN-US"></span></font></b></p>
<span lang="EN-US"> <br>
</span><b><span lang="EN-US"> </span></b>
<p></p>
<p><br>
</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. 21:55 skrev Bo
Victor Thomsen:<br>
</div>
<blockquote type="cite"
cite="mid:c644eedf-518e-4947-1916-4c2c8a814d59@gmail.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<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"
moz-do-not-send="true"><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"
moz-do-not-send="true"><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.Fdy5GKOd.2Jr00PdF@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.N861cwTM.K010iHUc@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.Djmf0jVx.LqAXAXml@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.h6ly2507.lENHyRL6@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 moz-txt-link-freetext" href="mailto:gdal-dev@lists.osgeo.org" moz-do-not-send="true">gdal-dev@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
</blockquote>
</body>
</html>