How to import KML file, with custom data, to Postgres/PostGIS database?

Question

By custom, I don't mean a different structure of KML file, but in <description></description> section, I have data with HTML tags of this kind:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>
<Placemark>
  <name>New York City</name>
  <description><![CDATA[
    <table>
      <tr><td colspan='2'>Attributes</td></tr>
      <tr><td>CITY_ID</td><td>150335</td></tr>
      <tr><td>POPULATION</td><td>8244910</td></tr>
      <tr><td>AREA</td><td>468.48</td></tr>
    </table>]]>
  </description>
  <Point>
    <coordinates>40.712964,-74.003886,0</coordinates>
  </Point>
</Placemark>
</Document>
</kml>

I want to easily import all this data, for each placemark, to a table with corresponding columns in the database, how to do this?

Answer 1

you can use ST_GeomFromKML as this page.

SELECT ST_GeomFromKML('
        <LineString>
            <coordinates>-71.1663,42.2614 
                -71.1667,42.2616</coordinates>
        </LineString>');

or you can use ogr2ogr as following to process entire KML files;

ogr2ogr -f "PostgreSQL" PG:"host=yourhost user=youruser dbname=yourdb 
                            password=yourpass" inputfilename.kml

i hope it helps you...

Answer 2

For those happier in QGIS, drag your KML file to QGIS. Then connect to your Postgres database as documented here then add the layers your want from QGIS as new table in your schema

Answer 3

I have used this command in the shell to import a KML file into a PostgreSQL database and it has worked perfectly for me. I share my solution.

ogr2ogr --config PG_USE_COPY YES -f PGDump /vsistdout/ filename.kml | psql db_name db_user