I'm trying to convert this KML dataset of ice arenas to a shapefile using ogr2ogr (GDAL 1.8). After some initial troubleshooting to get the script running, it's creating 184 different shapefiles (736 unique files including required siblings)!

My ogr2ogr script is essentially this, with some extra flags. The -append and -skipfailures flags are necessary (not sure if precision or geometry are doing anything, but I'm pretty sure they don't hurt). I'd prefer not to use -skipfailures, as clearly it means losing data, but without it the script won't finish:

ogr2ogr -f "ESRI Shapefile" "E:\4_GIS\HockeyArenas\shp\ice_rinks.shp" "E:\4_GIS\HockeyArenas\doc.kml" -lco PRECISION=false -nlt "geometry" -append -skipfailures

Ultimately I want to move this data into PostgreSQL/PostGIS, but if it's making 184 shapefiles, I don't want to litter my PostGIS db with 184 tables..

Anyone know how to get one shapefile, preferably without using -skipfailures?

Thanks, community.

2 Answers 2


Unfortunately, OGR sees the KML file as having 247 layers (ogrinfo doc.kml), which is why you see the mess.

I would do a direct KML -> PostGIS conversion. You can keep a clean PostGIS DB by using a schema to contain the mess temporarily. From your SQL:


Now from your shell (I'm using OSGeo4W Shell):

$ ogr2ogr -append -f PostgreSQL PG:"dbname='your_db' user='postgres' password='secret'" \
    doc.kml -lco SCHEMA=import -lco EXTRACT_SCHEMA_FROM_LAYER_NAME=NO \
            -lco OVERWRITE=YES -lco LAUNDER=NO

(No errors!)

Next, you need to stack your tables into one table in the public schema (assuming you have at least PostgreSQL 9.0 for the DO):

-- Create new table for all features
CREATE TABLE ice_rinks (gid serial PRIMARY KEY);
SELECT AddGeometryColumn('public', 'ice_rinks', 'geom', 4326, 'POINT', 3);
ALTER TABLE ice_rinks ADD COLUMN division text NOT NULL;
ALTER TABLE ice_rinks ADD COLUMN name text;
ALTER TABLE ice_rinks ADD COLUMN description text;

-- Combine them
DO $$DECLARE r record;
    FOR r IN (SELECT relname, trim(relname) AS division
              FROM pg_class c
              JOIN pg_namespace n ON n.oid=c.relnamespace
              WHERE n.nspname='import' AND relkind='r'
              ORDER BY trim(relname))
        EXECUTE 'INSERT INTO ice_rinks(geom, division, "name", description) ' ||
            'SELECT wkb_geometry, ' || quote_literal(r.division) || ', "Name", "Description" ' ||
            'FROM import.' || quote_ident(r.relname) || ' ORDER BY ogc_fid';

I have 1265 features .. hopefully they're all there.

Lastly, cleanup. You can remove your import schema if the data looks good:


And if you are using PostGIS 1.5 or prior:

DELETE FROM geometry_columns WHERE f_table_schema='import';
  • Hmm, I think -lco OVERWRITE=YES is doing some damage, as there are multiple division names .. thus only the last-most division remains. KML files can have a complicated hierarchy that client programs have difficulty streamlining into a simple model.
    – Mike T
    Commented Feb 23, 2012 at 21:56
  • I would recommend using this option because outputting to shapefile will create problems and truncate your field contents to <80 chars! This problem is no longer present in the development version of GDAL v>2.0
    – SaultDon
    Commented Feb 23, 2012 at 22:00
  • Yup, 1675 rows from SaultDon's solution and 1265 from the above solution. I'm not sure how easy it is to avoid -lco OVERWRITE=YES due to non-unique layer names. One option is to control the conversion via python and from osgeo import ogr
    – Mike T
    Commented Feb 23, 2012 at 22:32
  • Hey @MikeToews do you think having -loc OVERWRITE=YES may essentially swap-in for my -skipfailures? I wonder if you're losing data from overwrite while I'm losing it from, well, not writing? I was curious if someone would suggest the push-into-throwaway-db-merge-then-drop route. I'm not against it, necessarily. :)
    – elrobis
    Commented Feb 24, 2012 at 1:56
  • I think that OVERWRITE=YES is more specific than -skipfailures, but it is overwriting non-unique layers. You can get a better idea of this by looking at the layer names with ogrinfo doc.kml | cut -d ":" -f 2 | sort (from an OSGeo4w shell). E.g., there are 6 "Central Division", but only one of those will be imported.
    – Mike T
    Commented Feb 24, 2012 at 3:31

You can output to a shapefile with ogr2ogr (1.8) but will get truncated field contents or missing information.

For gdal <2.0 use:

ogr2ogr --config SHAPE_ENCODING UTF-8 -update -append output.shp /vsizip/vsicurl/http://www.hockeyarenas.net/hockeyarenas/downloads/kmz/World_Hockey_Arenas.zip/doc.kml -nln output -nlt GEOMETRY

For gdal >2.0 use:

ogr2ogr --config SHAPE_ENCODING UTF-8 -update -append output.shp -sql "RESIZE output" /vsizip/vsicurl/http://www.hockeyarenas.net/hockeyarenas/downloads/kmz/World_Hockey_Arenas.zip/doc.kml -nln output -nlt GEOMETRY


ogr2ogr --config SHAPE_ENCODING UTF-8 -update -append output.shp /vsizip/vsicurl/http://www.hockeyarenas.net/hockeyarenas/downloads/kmz/World_Hockey_Arenas.zip/doc.kml -nln output -nlt GEOMETRY -lco RESIZE=yes

The "--config SHAPE_ENCODING UTF-8" parameter is used to ensure that this specific dataset retains its DBF encoding so that none of the characters are translated to your systems language encoding, like ISO-8891-1... etc. Otherwords, it prevents characters with things like umlauts from being lost during the conversion.

The two options:

  • -sql "RESIZE tablehere"
  • -lco RESIZE=yes

    Do the same thing! They prevent any field contents from being truncated/shortened (losing information). In GDAL >2.0, this default truncating is supposedly no longer there, but have included the parameters for reference. From what I understand in GDAL <=1.9, there is no real way around this unless you access GDAL from C/C++ as there are some config options available there that aren't in the command line utilities like ogr2ogr.
  • I like what you're doing with the /vsizip/vsicurl/../doc.kml. I've not seen that trick before. What's the significance of --config SHAPE_ENCODING UTF-8 and particularly, RESIZE=yes?
    – elrobis
    Commented Feb 24, 2012 at 2:00
  • 1
    @elrobis The VSI capabilities of GDAL have saved me tonnes of disk space and time! I've updated the answer to explain the other options used.
    – SaultDon
    Commented Feb 24, 2012 at 15:07
  • Thanks for filling in some extra details. I realize going to shapefile has it's cons, but your approach handled it without lost points. Curiously, when I use Notepad++ to count the occurances of "placemark" it shows 3356. That has me wondering if the KML is still holding something back. However, it seems this isn't the same dataset they use on their map (hockeyarenas.net/?page=0301), as it's missing some South America rinks (such as Puerto Montt), and most painfully, the rink here where I live in Springfield, Missouri. :/
    – elrobis
    Commented Feb 24, 2012 at 15:52
  • The significant option in the command is -nln (new layer name). This creates one layer file instead of one file per layer. Commented Mar 8, 2015 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.