21

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?

Improve this question
1
  • 4
    There are many ways to accomplish this. Can you tell us more about what tools you are familiar with? Are you looking for a programming-oriented solution (what language do you like?) or a tool-based solution (e.g., ArgGIS, QGIS)? Commented Sep 10, 2012 at 0:14

3 Answers 3

Reset to default
22

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...

Improve this answer
2
  • 1
    ogr2ogr: you're saying go from KML to SHP then to PG. Why he can't use from KML to PG with one step? Commented Sep 10, 2012 at 12:04
  • It works! and for reference, I want to add some comments: I've found ogr2ogr in /Library/Frameworks/GDAL.framework/Programs/ (on a Mac) and there's warnings if content of <description> section contains more than 80 caracters. Thank you! Commented Sep 11, 2012 at 0:50
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

Improve this answer
1
  • That's what I would do! Commented Nov 15, 2018 at 17:22
0

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.