Month: May 2014

  • Importing Ordnance Survey Open Data in to PostgreSQL with PostGIS

    Some time ago, I looked at some uses for Ordnance Survey Open Data, coming to the conclusion that a sensible way to work with it would be to import it in to a geospatial-enabled database.
    Each set of data is provided in ESRI Shapefile format, and has four files:

    • shp – shape format
    • shx – shape index format
    • dbf – attribute format in dBase IV format
    • prj – projection format

    The shp2pgsql command converts SHP files in to a set of SQL commands which will effectively import the data in to PostgreSQL. Here’s a ridiculously simple guide to importing a file:
    createdb os_opendata
    psql -d os_opendata -c "CREATE EXTENSION POSTGIS"
    shp2pgsql <filename>.shp <table_name> | psql -d os_opendata
    Depending on the speed of your machine, in a few seconds you’ll find a new table in your database with all the data included.
    And finally, what if you just want to import all of the data at once? Try this:
    find Data/ -name "*.shp" | xargs -I % -n1 shp2pgsql % | psql -d os_opendata