[gdal-dev] org2org

Bo Victor Thomsen bo.victor.thomsen at gmail.com
Thu Nov 10 00:25:58 PST 2022 

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.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/20221110/b00d3699/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/20221110/b00d3699/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/20221110/b00d3699/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/20221110/b00d3699/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/20221110/b00d3699/attachment-0007.png>

More information about the gdal-dev mailing list