Sorry to pollute this list (again !) with non GDAL related information,
however -
I made a mistake in my answer about the SQL statement and the nerd part
of my brain just won't let go. So:
* The SQL where-part contains a combination of st_intersects (geom,
/..some geometry resulting function../) and not st_touches (geom,
/..same geometry resulting function../)
"intersects" and "not touches" is equivalent to "within", so you can
probably rewrite the where part to: *
WHERE ST_Within(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')*))
The above is /not/ correct. The equivalent to "intersects and not
touches" is "within or overlaps". The where statement should be:
*WHERE ST_Within(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')*)) *OR*
*ST_Overlaps(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')*))
Which is marginally better than the original where.
But if you read the entire SQL statement, I would assume the purpose
would be something like: "/Fetch all gridcells that is wihtin a 10 km.
distance from a set of countries"//./
In that case the where part could be simplified (and running
considerably faster) by using ST_DWithin:
*SELECT
a.name,
a.left,
a.top,
a.right,
a.bottom,
a.geom
FROM grids.grid_1_25grad a
JOIN countries.table_with_countries b ON ST_DWIthin (a.geom, b.geom,
10000.0)*
If the 2 tables involved has the same SRID (using meters as distance
unit) and the spatial columns is of type geometry
**
**
Med venlig hilsen / Best regards
Bo Victor Thomsen
Den 09-11-2022 kl. 21:55 skrev Bo Victor Thomsen:
Thorsten -
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 ?
Sanitized version of the select:
*--
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')))*
--
* You are selecting features from table /grids.grid_1_25grad/. The
name of the table indicates it contains grid cells - like the ones
you show in the picture.
* The SQL where-part contains a combination of st_intersects (geom,
/..some geometry resulting funtion../) and not st_touches (geom,
/..same geometry resulting funtion../)
"intersects" and "not touches" is equivalent to "within", so you
can probably rewrite the where part to: *
WHERE ST_Within(geom, (SELECT geom FROM
countries.geometries_boundary_buffer_10km('and')))*
*
*
Med venlig hilsen / Best regards
Bo Victor Thomsen
Den 09-11-2022 kl. 14:53 skrev Rahkonen Jukka:
Hi,
You are selecting data from table 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.
-Jukka Rahkonen-
*Lähettäjä:*Leber, Thorsten <thorsten.le...@hensoldt.net>
*Lähetetty:* keskiviikko 9. marraskuuta 2022 15.46
*Vastaanottaja:* Rahkonen Jukka <jukka.rahko...@maanmittauslaitos.fi>
*Aihe:* AW: org2org
Hi Jukka,
with geometry al 5 files are created
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
but it looks strange
I would expect this
Thorsten
*Von:*Rahkonen Jukka <jukka.rahko...@maanmittauslaitos.fi>
*Gesendet:* Mittwoch, 9. November 2022 13:10
*An:* Leber, Thorsten <thorsten.le...@hensoldt.net>
*Betreff:* Re: org2org
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.
Hi,
Try to add a geometry into the selection -sql "SELECT \""name\"",
\""left\"", top, \""right\"", bottom FROM..
-Jukka-
*Lähettäjä:*Leber, Thorsten <thorsten.le...@hensoldt.net>
*Lähetetty:* keskiviikko 9. marraskuuta 2022 13.53
*Vastaanottaja:* Rahkonen Jukka <jukka.rahko...@maanmittauslaitos.fi>
*Aihe:* AW: org2org
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
this works now without any warning but still only 3 files in output
folder
*Von:*Rahkonen Jukka <jukka.rahko...@maanmittauslaitos.fi>
*Gesendet:* Mittwoch, 9. November 2022 12:21
*An:* Leber, Thorsten <thorsten.le...@hensoldt.net>;
gdal-dev@lists.osgeo.org
*Betreff:* Re: org2org
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.
Hi,
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').
-Jukka Rahkonen-
*Lähettäjä:*gdal-dev <gdal-dev-boun...@lists.osgeo.org> *Puolesta
*Leber, Thorsten
*Lähetetty:* keskiviikko 9. marraskuuta 2022 13.02
*Vastaanottaja:* gdal-dev@lists.osgeo.org
*Aihe:* [gdal-dev] org2org
Hi All,
I am trying to use org2org with the following command:
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
I expect in the output folder 5 files
But I receive a warning:
Warning 1: layer names ignored in combination with -sql.
And get only 3 files
I am missing the .shp and the .shx file. Is there something wrong in
my command?
KR
Thorsten
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
