[gdal-dev] AsGeoJSON with SQLite dialect returns an empty string

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] AsGeoJSON with SQLite dialect returns an empty string

jratike80

Hi,

 

I noticed this question https://gis.stackexchange.com/questions/316776/ogr2ogr-fails-with-no-such-table and tried to study that odd shapefile by repeating the test with GDAL 2.4.0 from OSGeo4W and there happens something with ST_AsGeoJSON that I do not understand.

 

This command indeed returns an empty result

ogrinfo -dialect sqlite -sql "select AsGeoJSON(geom)  from \"geo_export_98941210-c995-4502-8ebb-93a09f29d615\" limit 1"  fix.gpkg

INFO: Open of `fix.gpkg'

      using driver `GPKG' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 1

Layer SRS WKT:

(unknown)

AsGeoJSON(geom): String (0.0)

OGRFeature(SELECT):0

  AsGeoJSON(geom) (String) = (null)

 

Other Spatialite functions (AsGML, AsKML, AsBinary, AsSVG) do return the geometry, it is only AsGeoJSON that gives an empty result.

 

However, with a trick I could get also GeoJSON out:

ogrinfo -dialect sqlite -sql "select AsGeoJSON(st_geomfromtext(st_astext(geom)))  from \"geo_export_98941210-c995-4502-8ebb-93a09f29d615\" limit 1"  fix.gpkg

INFO: Open of `fix.gpkg'

      using driver `GPKG' successful.

 

Layer name: SELECT

Geometry: None

Feature Count: 1

Layer SRS WKT:

(unknown)

AsGeoJSON(st_geomfromtext(st_astext(geom))): String (0.0)

OGRFeature(SELECT):0

  AsGeoJSON(st_geomfromtext(st_astext(geom))) (String) = {"type":"Polygon","coordinates":[[[-87.666731,41.707976],[-87.66686799999998,41.70797699999999],[-87.66686699999999,41.708063],[-87.666684,41.708064],[-87.666683,41.70799499999999],[-87.66673,41.70799499999999],[-87.666731,41.707976]]]}

 

I could only repeat the result by reading the data directly from geopackage, not by saving the feature taken from gpkg into shapefile or OpenJUMP JML format. I produced a minimal geopackage http://latuviitta.org/downloads/one_chicago.gpkg with only one feature for testing. The command to try is

 

ogrinfo -dialect sqlite -sql "select AsGeoJSON(geom)  from \"geo_export_98941210-c995-4502-8ebb-93a09f29d615\""  one_chicago.gpkg

 

-Jukka Rahkonen-

 

 

 


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: AsGeoJSON with SQLite dialect returns an empty string

Even Rouault-2
On jeudi 28 mars 2019 15:01:20 CET Rahkonen Jukka (MML) wrote:
> Hi,
>
> I noticed this question
> https://gis.stackexchange.com/questions/316776/ogr2ogr-fails-with-no-such-t
> able and tried to study that odd shapefile by repeating the test with GDAL
> 2.4.0 from OSGeo4W and there happens something with ST_AsGeoJSON that I do
> not understand.

Jukka,

This is a Spatialite bug specific of ST_AsGeoJSON + Geopackage geometry blobs.
I've sent a patch to the Spatialite maintainer to fix it.

Other workarounds:
- for no precision loss at all, you can also do:
AsGeoJSON(ST_GeomFromWKT(AsBinary(geom)))
- or you can also use -dialect indirect_sqlite that will go through the slower
indirection of using a virtual sqlite table with spatialite geometries.

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev