1

I am looking for a way to import my data from KML (example below) to PostGIS database.

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">
<Document>
<name>Location history from 04/20/2011 to 05/20/2011</name>
<open>1</open>
<description/>
<StyleMap id="multiTrack">
<Pair>
<key>normal</key>
<styleUrl>#multiTrack_n</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#multiTrack_h</styleUrl>
</Pair>
</StyleMap>
<Style id="multiTrack_n">
<IconStyle>
<Icon>
<href>http://earth.google.com/images/kml-icons/track-directional/track-0.png</href>
</Icon>
</IconStyle>
<LineStyle>
<color>99ffac59</color>
<width>6</width>
</LineStyle>
</Style>
<Style id="multiTrack_h">
<IconStyle>
<scale>1.2</scale>
<Icon>
<href>http://earth.google.com/images/kml-icons/track-directional/track-0.png</href>
</Icon>
</IconStyle>
<LineStyle>
<color>99ffac59</color>
<width>8</width>
</LineStyle>
</Style>
<Placemark>
<name>Latitude User</name>
<description>Location history for Latitude User from 04/20/2011 to 05/20/2011</description>
<styleUrl>#multiTrack</styleUrl>
<gx:Track>
<altitudeMode>clampToGround</altitudeMode>
<when>2011-04-20T13:25:36.026-07:00</when>
<gx:coord>18.171574 53.391877 0</gx:coord>
<when>2011-04-20T13:30:31.488-07:00</when>
<gx:coord>18.171574 53.391877 0</gx:coord>
(...)
</gx:Track>
</Placemark>
</Document>
</kml>

Every time (in case of one file) I used Notepad++ (tools "Replace" to remove usless codes and organize to structure possible to import to Excel), Excel for preparing to import to ArcGIS, next used "Add XY Data" to create geometry and export to SHP, but currently I am looking for more automated solution. :)

lomasz
  • 93
  • 1
  • 2
  • 10

2 Answers2

1

Have you tried ogr2ogr? It can import KML to PostGIS. It's part of the GDAL suite of FOSS tools

Which have binaries here or you can compile your own http://trac.osgeo.org/gdal/wiki/DownloadingGdalBinaries

Example in this question: How to import KML file, with custom data, to Postgres/PostGIS database?

Community
  • 1
Regina Obe
  • 10,503
  • 1
  • 23
  • 28
  • I tried, but it didn't work correctly. ("ogr2ogr -f "PostgreSQL" PG:"host=yourhost user=youruser dbname=yourdb password=yourpass" inputfilename.kml" like this, but with my database parameters) It created table (with columns: ogc_fid, wkb_geometry, name, description) in database, but data are not imported (blank table). For me is really important to have information about geometry and date/time. – lomasz Nov 13 '14 at 11:04
  • The wkb_geometry is the geometry. Are you sure the table is blank - has no records at all? the wkb_geometry may look blank for long linestrings. To be sure you need to check SELECt count(wkb_geometry) from yourtable; – Regina Obe Nov 16 '14 at 06:03
  • table is blank - I counted records in table and result was 0. – lomasz Nov 16 '14 at 19:31
1

you can use ogr2ogr, an example would be this running ogr2ogr from the windows console.

Example: ogr2ogr.exe -f PostgreSQL "PG:dbname="NameDataBase" schemas="NameSchema" host='localhost' port='5432' user='postgres' password='pass'" blackpolygon.kml(LocationFile) -nln "test"

Camilo Alvarez
  • 31
  • 2