[gdal-dev] org2org

Bo Victor Thomsen bo.victor.thomsen at gmail.com
Wed Nov 9 12:55:25 PST 2022 

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.Leber at hensoldt.net>
> *Lähetetty:* keskiviikko 9. marraskuuta 2022 15.46
> *Vastaanottaja:* Rahkonen Jukka <jukka.rahkonen at 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.rahkonen at maanmittauslaitos.fi>
> *Gesendet:* Mittwoch, 9. November 2022 13:10
> *An:* Leber, Thorsten <Thorsten.Leber at 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.Leber at hensoldt.net>
> *Lähetetty:* keskiviikko 9. marraskuuta 2022 13.53
> *Vastaanottaja:* Rahkonen Jukka <jukka.rahkonen at 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.rahkonen at maanmittauslaitos.fi>
> *Gesendet:* Mittwoch, 9. November 2022 12:21
> *An:* Leber, Thorsten <Thorsten.Leber at hensoldt.net>; 
> gdal-dev at 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-bounces at lists.osgeo.org> *Puolesta 
> *Leber, Thorsten
> *Lähetetty:* keskiviikko 9. marraskuuta 2022 13.02
> *Vastaanottaja:* gdal-dev at 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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221109/ae1ee3f0/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 1618 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221109/ae1ee3f0/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 12636 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221109/ae1ee3f0/attachment-0005.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 3261 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221109/ae1ee3f0/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 2071 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221109/ae1ee3f0/attachment-0007.png>

More information about the gdal-dev mailing list