Re: [gdal-dev] OGR SQL CAST geometry example
Hi,
As far I know the the Spatialite functions
https://www.gaia-gis.it/gaia-sins/spatialite-sql-4.2.0.html by design work row
by row and one input row returns one output row. Therefore DissolvePoints
creates one row with a multipoint geometry, not several rows like ST_Dump does.
You can discuss about that in the spatialite-users forum
https://groups.google.com/g/spatialite-users.
It is possible to extract the points from linestrings with SpatiaLite SQL by
using NumPoints and then PointN in a loop, but such SQL is not possible to
execute with ogrinfo/ogr2ogr. It is possible to do amazing things with ogr2ogr
and SQL but it does make it the right tool for demanding ETL processes.
Typically people turn to use Python at that point.
If you have a real need to extract points from a linestring by using only GDAL
command line utilities, it should be possible to do in two steps. Create an
interim file by using the Spatialite function DissolvePoints, and then run
ogr2ogr with the -explodecollections option.
-Jukka Rahkonen-
-Alkuperäinen viesti-
Lähettäjä: gdal-dev Puolesta Dan Jacobson
via gdal-dev
Lähetetty: perjantai 31. toukokuuta 2024 8.36
Vastaanottaja: Daniel Baston
Kopio: gdal-dev@lists.osgeo.org
Aihe: Re: [gdal-dev] OGR SQL CAST geometry example
Alas, even for MULTIPOINT, as there are no facilities for extracting from
aggregates, not even ST_Dump, in the end all one can do is $ ogrinfo
0.lines.kml -al | perl -nwle \
'if(/LINESTRING.*\((.*)\)/){print for split /,/, $1}'
-100.79005679 36.38022 1000
-100.79008013 36.38531 1000
___
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
Re: [gdal-dev] OGR SQL CAST geometry example
Alas, even for MULTIPOINT, as there are no facilities for extracting
from aggregates, not even ST_Dump, in the end all one can do is
$ ogrinfo 0.lines.kml -al | perl -nwle \
'if(/LINESTRING.*\((.*)\)/){print for split /,/, $1}'
-100.79005679 36.38022 1000
-100.79008013 36.38531 1000
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL CAST geometry example
If a single MULTIPOINT record is OK, then you can do ogrinfo test.shp -q -dialect SQLITE -sql 'SELECT ST_DissolvePoints(GEOMETRY) FROM test' Dan On Thu, May 30, 2024 at 8:49 PM Dan Jacobson via gdal-dev wrote: > > $ ogrinfo 0.lines.kml -q -dialect SQLITE -sql \ > 'SELECT ST_PointN(GEOMETRY, generate_series(1, ST_NPoints(GEOMETRY))) FROM > "0"' > ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2( > no such function: generate_series > ___ > 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
Re: [gdal-dev] OGR SQL CAST geometry example
According to http://www3.sqlite.org/series.html, generate_series() is only built in the sqlite3 command line utility, not in the library itself. That said, with some work, the source at https://www.sqlite.org/src/file/ext/misc/series.c could potentially be embedded into libgdal Le 31/05/2024 à 02:49, Dan Jacobson a écrit : $ ogrinfo 0.lines.kml -q -dialect SQLITE -sql \ 'SELECT ST_PointN(GEOMETRY, generate_series(1, ST_NPoints(GEOMETRY))) FROM "0"' ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2( no such function: generate_series -- http://www.spatialys.com My software is free, but my time generally not. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL CAST geometry example
$ ogrinfo 0.lines.kml -q -dialect SQLITE -sql \ 'SELECT ST_PointN(GEOMETRY, generate_series(1, ST_NPoints(GEOMETRY))) FROM "0"' ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2( no such function: generate_series ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL CAST geometry example
Ah, I misunderstood and thought you wanted to extract points from linestrings. Given $ cat test.csv id,my_wkt 1,"POINT Z (1 2 3)" you can do: $ ogrinfo test.csv -sql "select id, cast(my_wkt as geometry(pointz, 4326)) from test" INFO: Open of `test.csv' using driver `CSV' successful. Layer name: test Geometry: 3D Point Feature Count: 1 Extent: (1.00, 2.00) - (1.00, 2.00) Layer SRS WKT: GEOGCRS["WGS 84", ENSEMBLE["World Geodetic System 1984 ensemble", MEMBER["World Geodetic System 1984 (Transit)"], MEMBER["World Geodetic System 1984 (G730)"], MEMBER["World Geodetic System 1984 (G873)"], MEMBER["World Geodetic System 1984 (G1150)"], MEMBER["World Geodetic System 1984 (G1674)"], MEMBER["World Geodetic System 1984 (G1762)"], MEMBER["World Geodetic System 1984 (G2139)"], MEMBER["World Geodetic System 1984 (G2296)"], ELLIPSOID["WGS 84",6378137,298.257223563, LENGTHUNIT["metre",1]], ENSEMBLEACCURACY[2.0]], PRIMEM["Greenwich",0, ANGLEUNIT["degree",0.0174532925199433]], CS[ellipsoidal,2], AXIS["geodetic latitude (Lat)",north, ORDER[1], ANGLEUNIT["degree",0.0174532925199433]], AXIS["geodetic longitude (Lon)",east, ORDER[2], ANGLEUNIT["degree",0.0174532925199433]], USAGE[ SCOPE["Horizontal component of 3D system."], AREA["World."], BBOX[-90,-180,90,180]], ID["EPSG",4326]] Data axis to CRS axis mapping: 2,1 Geometry Column = my_wkt id: String (0.0) OGRFeature(test):1 id (String) = 1 POINT Z (1 2 3) Note that probably counterintuitively, if instead of pointz, you try to specify point (or even linestring), that will only affect the *declared* geometry column type, but will have no consequence on the actual feature geometry, and no consistency check is done to ensure they match (contrary to PostGIS that is very strict, and would not even allow casting that POINT Z (1 2 3) as a geometry(point), but only a geometry(pointz)) Le 31/05/2024 à 01:29, Dan Jacobson a écrit : "ER" == Even Rouault writes: ER> you can't do operations on geometries with OGR SQL... OK, I'll try it. Thanks. The page should still have an example of actual use of Casting ... POINT[Z], LINESTRING[Z] Without examples people will try things like POINTZ, "POINTS[Z]" -- http://www.spatialys.com My software is free, but my time generally not. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL CAST geometry example
> "ER" == Even Rouault writes: ER> you can't do operations on geometries with OGR SQL... OK, I'll try it. Thanks. The page should still have an example of actual use of Casting ... POINT[Z], LINESTRING[Z] Without examples people will try things like POINTZ, "POINTS[Z]" ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] OGR SQL CAST geometry example
Dan, you can't do operations on geometries with OGR SQL, but selecting it. You'd typically need to use the ST_PointN() from Spatialite with the SQLite dialect Even Le 31/05/2024 à 00:32, Dan Jacobson via gdal-dev a écrit : Hi. I will add an example to https://gdal.org/user/ogr_sql_dialect.html#changing-the-type-of-the-fields showing users how to e.g., CAST LINESTRING Z to POINTS Z, e.g., to extract the points from: $ ogrinfo 0.lines.kml -q -sql 'SELECT "_ogr_geometry_" FROM "0"' Layer name: 0 OGRFeature(0):1 LINESTRING Z (-100.79005679 36.38022 1000,-100... Alas, there are no examples I can find of how to write it. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev -- http://www.spatialys.com My software is free, but my time generally not. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
[gdal-dev] OGR SQL CAST geometry example
Hi. I will add an example to https://gdal.org/user/ogr_sql_dialect.html#changing-the-type-of-the-fields showing users how to e.g., CAST LINESTRING Z to POINTS Z, e.g., to extract the points from: $ ogrinfo 0.lines.kml -q -sql 'SELECT "_ogr_geometry_" FROM "0"' Layer name: 0 OGRFeature(0):1 LINESTRING Z (-100.79005679 36.38022 1000,-100... Alas, there are no examples I can find of how to write it. ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
