[gdal-dev] Unable to extract features from KML

[Michael Z Freeman]

Hi Even Rouault,

Thanks. I had not found those SQL dialect docs for some reason so no
wonder it was not making sense.

I am now using ...

ogr2ogr -overwrite -f PostgreSQL PG:"dbname=original_hirise"
Beyer_Hirise_Areas_KML/MC-01-0-0-0.kml -nln gdal_hirise_from_kml -a_srs
"EPSG:4326" -dialect SQLite -sql "SELECT ST_CollectionExtract(geometry, 1)
AS postgis_point, ST_CollectionExtract(geometry, 3) AS postgis_polygon, *
FROM 'MC-1, Mare Boreum (North Pole), 0-0-0'"

I can access the database using QGIS and all the Polygons and Markers
appear in the correct locations. Now to import the entire HiRise feature
set :)

Michael Z Freeman

On Thu, November 24, 2016 5:03 pm, Even Rouault wrote:
> 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","[...]","0101000020E61000000AD7A3703D6A3EC0744694F60
>> 6035
>> 640"
>> 2,"ESP_018108_2680","[...]","0103000020E61000000100000005000000A4703D0AD
>> 723
>>
>>
> 3CC079E9263108FC55400000000000C03FC0F4FDD478E9FA5540F0A7C64B377940C0992A1
>  895
>
>>
> D4095640FCA9F1D24DE23CC002BC0512140B5640A4703D0AD7233CC079E9263108FC5540
> "
>
>> 3,"ESP_018318_2675","[...]","0101000020E6100000A245B6F3FD543EC0728A8EE4
>> F2DF
>> 5540"
>> 4,"ESP_018318_2675","[...]","0103000020E610000001000000050000004260E5D02
>> 23B
>>
>>
> 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-feature
>>> s-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