By
2012-09-26
OpenGovermentData, PostGIS
14 Comments

WFS to PostGIS in 3 Steps

This is a quick note on how to download features from a WFS and import them into a PostGIS database. The first line downloads a zipped Shapefile from the WFS. The second one unzips it and the last one loads the data into my existing “gis_experimental” database:

wget "http://data.wien.gv.at/daten/geoserver/ows?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:BEZIRKSGRENZEOGD&srsName=EPSG:4326&outputFormat=shape-zip" -O BEZIRKSGRENZEOGD.zip
unzip -d /tmp BEZIRKSGRENZEOGD.zip
shp2pgsql -s 4326 -I -S -c -W Latin1 "/tmp/BEZIRKSGRENZEOGD.shp" | psql gis_experimental

Now, I’d just need a loop through the WFS Capabilities to automatically fetch all offered layers … Ideas anyone?

Thanks to Tim for his post “Batch importing shapefiles into PostGIS” which was very useful here.

Update: Many readers have pointed out that ogr2ogr is a great tool for this kind of use cases and can do the above in one line. That’s true – if it works. Unfortunately, it is picky about the supported encodings, e.g. doesn’t want to parse ISO-8859-15. In such cases, the three code lines above can be a good alternative.

Advertisement
14 comments

  1. Using Python to walk through the capabilities xml document should do the trick, nah? I would probably store the layers’ ids in a text file and use bash to wget those from server.
    That’s hypothetically speaking, as I’m really bad python/bash user (newbie).

  2. hdus said:

    Why dont you use the ogr2ogr wfs driver to import the data in one step?

    • Seems like the encoding used for the WFS (ISO-8859-15) is not supported by the ogr2ogr XML parsers.

  3. Even Rouault said:

    With OGR, you can do the conversion in a one liner :

    ogr2ogr -f postgresql pg:dbname=gis_experimental “WFS:http://data.wien.gv.at/daten/geoserver/ows?” –config GML_PARSER XERCES

    • Thanks for your comment! I tried to work out ogr2ogr and got to the following command, but it fails with an encoding-related error:

      ogr2ogr -f PostgreSQL PG:”user=myuser password=mypassword dbname=wien_ogd” “WFS:http://data.wien.gv.at/daten/geoserver/ows?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:BEZIRKSGRENZEOGD&srsName=EPSG:4326” –config GML_PARSER XERCES

      ERROR 1: XML Parsing Error: unable to create converter for ‘ISO-8859-15’ encoding

      • Even Rouault said:

        Strange, I presume your Xerces build isn’t configured for encoding support with libicu.
        I’ve just pushed an improvement in GDAL trunk (2.0dev), so that ISO-8859-15 can be handled with the Expat parser, which should avoid your issue (and the need to specify –config GML_PARSER XERCES)

      • That sounds like it is exactly what I need. Looking forward to the next release :)
        Thanks!
        (Currently, I’m running GDAL 1.9.1 from default OSGeo4W installation.)

      • I’ve just testet Even’s suggestion: Download the latest GDAL dev binaries and use this command:

        ogr2ogr -f PostgreSQL PG:”host=’localhost’ user=’myuser’ password=’mypassword’ dbname=’mydatabase'” WFS:”http://data.wien.gv.at/daten/geoserver/ows?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:BEZIRKSGRENZEOGD&srsName=EPSG:4326″

        Works like a charm. No enconding errors and the PostGis table contains 23 rows and columns with strings that has a umlaut’s, o umlaut’s and duble s’s

        Nice work Even..

      • Thats great! Thanks for testing and posting here.

  4. Hi, Just had a play with extending your original example to loop through all layers in the GetCapabilities response and this does the trick for me: https://gist.github.com/3790269.

    Also agree that using ogr2ogr in place of download in SHP / shp2pgsql / psql is preferable especially as it allows use with other WFS services that don’t support zipped SHP output just GML.

    • You’re amazing! Thanks so much!

  5. Peter Parslow said:

    With the question being about WFS, I thought the answer would be about GML, not shape. I’ll keep looking – but in this thread, I’ll just ask “in what sense is it a WFS if it is returning you a shape file?”.

    • While GML is the default format used in WFS, many other formats such as GeoJSON and Shapefile can be supported too. For this use case, Shapefile makes more sense for me, because I can use shp2pgsql this way.

%d bloggers like this: