Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.
share|improve this answer
    
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 Feb 24 '12 at 2:00
    
@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 Feb 24 '12 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 Feb 24 '12 at 15:52

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';
share|improve this answer
    
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 Feb 23 '12 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 Feb 23 '12 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 Feb 23 '12 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 Feb 24 '12 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 Feb 24 '12 at 3:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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