Import multiple KML files into single table with preserving layer name as (additional) column

Question

I have bunch (1000+) of KML files like this:

<Document>
  <name>Shape 10</name>
  ...
  <Placemark>
    <name></name>
    <MultiGeometry>
     ...
   </MultiGeometry>
  </Placemark>
</Document>

which need to be imported into PostgreSQL database.

I can import all files using ogr2ogr:

find data -name '*.kml' | \
  xargs -n1 ogr2ogr -f "PostgreSQL" -dim 2 -nln shapes -append PG:dbname=${db}

but:

• importing w/o -nln options creates one table per KML file with name from /Document/name element ("Shape 10") which is not ideal
• importing w/ -nln creates single table but shape name ("Shape 10") is lost (not recorded in table - column name is empty probably because Document/Placemark/name is empty

How I could import 1000's of KML files into single table with preserving / storing layer name in output table?

Answer 1

After careful reading of GDAL manual pages for ogr2ogr and KML/LIBKML drivers I believe that there is not straightforward way how to do this.

The simplest way is to use different programming language or XML command-line tools to create modified KMLs and import those. For example, using xmlstarlet:

find . -name '*.kml' | while read fn; do
  shape_id=$(xmlstarlet sel -t -m '/Document' -v name ${fn})
  xmlstarlet ed -u '//Placemark/name' -v "${shape_id}" ${fn} > a.kml
  ogr2ogr -f "PostgreSQL" -dim 2 -nln ${table} -append PG:dbname=${tb} a.kml
done