[gdal-dev] Unable to extract features from KML

[Even Rouault]

On jeudi 24 novembre 2016 16:54:31 CET Michael Z Freeman wrote:
> Hi,
> 
> Still having trouble with this after updating to ogr2ogr/gdal 2.1.1.
> 
> Some of it is undoubtedly my unfamiliarity with GDAL. I'm on the steep
> part of the learning curve ! But fun.
> 
> "ogr2ogr -f PostgreSQL PG:"dbname=original_hirise" -a_srs "EPSG:4326" -lco
> OVERWRITE=YES -explodecollections -nlt geometry
> Beyer_Hirise_Areas_KML/MC-01-0-0-0.kml"
> 
> ... creates (an edited CSV exported from a PGAdmin4 query) ...
> 
> "ogc_fid","name","description","wkb_geometry"
> 1,"ESP_018108_2680","[...]","0101000020E61000000AD7A3703D6A3EC0744694F606035
> 640"
> 2,"ESP_018108_2680","[...]","0103000020E61000000100000005000000A4703D0AD723
> 
3CC079E9263108FC55400000000000C03FC0F4FDD478E9FA5540F0A7C64B377940C0992A1
895
> 
D4095640FCA9F1D24DE23CC002BC0512140B5640A4703D0AD7233CC079E9263108FC5540
"
> 3,"ESP_018318_2675","[...]","0101000020E6100000A245B6F3FD543EC0728A8EE4F2DF
> 5540"
> 4,"ESP_018318_2675","[...]","0103000020E610000001000000050000004260E5D0223B
> 
3BC00C022B8716D95540AAF1D24D62503DC0FED478E926D55540448B6CE7FBD940C0C36
4AA60
> 
54E6554039B4C876BE7F3FC06D567DAEB6EA55404260E5D0223B3BC00C022B8716D95540
"
> 
> As you can see the geometries from the KML are exploded ... the short WKB
> is a point, the longer one POLYGON geometry. However two database entries
> are created for each feature ("name"). I could just merge these but I feel
> that OGR can create two geometry columns each for POINT and POLYGON, in
> fact this seems to be explicitly stated that it can do this in the news
> release for GDAL version 2.

What you observe is the intended behaviour of -explodecollections, but not what you want.

> 
> Should I be using some kind of SQL statement on the OGR command line to
> make it do this ?

You can indeed use Spatialite SQL to do what you want if you specify the SQLite dialect 
(http://gdal.org/ogr_sql_sqlite.html). If you look at http://www.gaia-gis.it/gaia-sins/
spatialite-sql-4.3.0.html, you can see there's a ST_CollectionExtract function as well.

So something like

ogr2ogr -overwrite PG:xxxxx your.kml -dialect SQLite -sql "SELECT 
ST_CollectionExtract(geometry, 1) AS the_point, ST_COLLECTIONExtract(geometry, 3) AS 
the_polygon, * FROM the_layer"

Even

> Michael Z Freeman
> 
> On Mon, November 21, 2016 7:30 am, jratike80 wrote:
> > Hi,
> > 
> > 
> > The main problem is that KML support such data models which do not suit
> > well with the simple feature model of GDAL. You can get some information
> > about that by reading http://www.gdal.org/drv_libkml.html.
> > 
> > In your case each feature has two geometries: one point and one polygon.
> > Best that can be done with the simple feature model it to push them both
> > into one geometry which in that case must be a geometry collection. Here
> > is one example from your data
> > 
> > GEOMETRYCOLLECTION (POINT (-1.282 87.1064),POLYGON ((3.02457
> > 87.0792,2.22294
> > 86.9806,-5.655 87.1168,-5.098 87.2201,3.02457 87.0792)))
> > 
> > 
> > You can save geometry collections into PostGIS if you use ogr2ogr with
> > switch "-nlt geometry". However, saving geometry collections into PostGIS
> > may not solve your problem because only few GIS programs can handle
> > geometry collections well.
> > 
> > A better option for you might be to explode the collections and save
> > points and polygons as separate features into PostGIS. The ogr2ogr command
> > to use is like
> > 
> > ogr2ogr -f PGDump output_pgdump -nlt geometry -explodecollections
> > Beyer_Hirise_Areas_KML/MC-01-0-0-0.kml
> > 
> > 
> > If you want to have a quick look at your data, use JML as outputformat
> > and open the result with OpenJUMP. It shows both the geometry collections
> > and exploded collections fine for me with names and descriptions.
> > 
> > -Jukka Rahkonen-
> > 
> > 
> > 
> > 
> > --
> > View this message in context:
> > http://osgeo-org.1560.x6.nabble.com/gdal-dev-Unable-to-extract-features-f
> > rom-KML-tp5296670p5296708.html Sent from the GDAL - Dev mailing list
> > archive at Nabble.com. _______________________________________________
> > gdal-dev mailing list gdal-dev at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/gdal-dev
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev


-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20161124/9e8ab001/attachment-0001.html>