ogr2ogr kml to shp conversion created 184 shapefiles?! What am I missing?

Question

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.

Answer 1

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:

CREATE SCHEMA import;

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;
BEGIN
    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))
    LOOP
        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';
    END LOOP;
END$$;

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

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

DROP SCHEMA import CASCADE;

And if you are using PostGIS 1.5 or prior:

DELETE FROM geometry_columns WHERE f_table_schema='import';

Answer 2

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

or

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.