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 <[email protected]>
*Lähetetty:* keskiviikko 9. marraskuuta 2022 15.46
*Vastaanottaja:* Rahkonen Jukka <[email protected]>
*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 <[email protected]>
*Gesendet:* Mittwoch, 9. November 2022 13:10
*An:* Leber, Thorsten <[email protected]>
*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 <[email protected]>
*Lähetetty:* keskiviikko 9. marraskuuta 2022 13.53
*Vastaanottaja:* Rahkonen Jukka <[email protected]>
*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 <[email protected]>
*Gesendet:* Mittwoch, 9. November 2022 12:21
*An:* Leber, Thorsten <[email protected]>;
[email protected]
*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 <[email protected]> *Puolesta
*Leber, Thorsten
*Lähetetty:* keskiviikko 9. marraskuuta 2022 13.02
*Vastaanottaja:* [email protected]
*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
[email protected]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
