In an earlier post, I showed you how to use the shp2pgsql tool to import shapefiles into PostGIS. But what if you need to import other geodata formats (e.g. GeoJSON, MapInfo, KML, CSV, etc.)?

ogr2ogr – ETL for PostGIS

Shrek

Lucky for you, the BigSQL PostGIS distribution comes bundled with GDAL and the ogr2ogr (or as my geogeek friends like to call it “ogre to ogre”) command line tool.

From gdal.org/ogr2gr:

This program can be used to convert simple features data between file formats performing various operations during the process such as spatial or attribute selections, reducing the set of attributes, setting the output coordinate system or even reprojecting the features during translation.

In other words, ogr2ogr is your go to ETL tool for importing and exporting PostGIS data.

Exercise: Intro to ogr2ogr

In this exercise, we will import and export GeoJSON files to and from our postgis_sample database using ogr2ogr commands.

Exercise Prerequisites

The ogr2ogr command line tool is packaged with BigSQL’s PostgreSQL installers. If you have not installed the BigSQL PostgreSQL distribution and setup your PostGIS database, start here.

GeoJSON

GeoJSON is an extension of JSON open standard format and designed for representing simple geographical features, along with their non-spatial attributes.

Example:

    {
        "type": "Feature",
        "geometry": {
            "type": "Point",
            "coordinates": [125.6, 10.1]
        },
        "properties": {
            "name": "Dinagat Islands"
        }
    }

Getting to know ogr2ogr Command Line

Open your command line utility and enter the this help command to see ogr2ogr parameters and usage:

ogr2ogr --long-usage

You should see the following parameter and under it a list of all the data formats your installation of ogr2ogr supports:

-f format_name

Make sure that the these two formats are included in the list:

-f "GeoJSON"
-f "PostgreSQL"

Import GeoJSON to PostGIS

For this exercise, I downloaded the NYC Wi-Fi Hotspot Locations GeoJSON file available at the New York City Open Data site.

To download this data click here: nyc_hotspots.geojson

To import a GeoJSON file into your PostGIS database, use the following command:

ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.json" -nln new_table_name

If you want to append the new data to already existing records, use the -append flag:

ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.geojson" -nln destination_table -append

So, for our exercise run the following commands:

cd <location of nyc_hotspots.geojson>
ogr2ogr -f "PostgreSQL" PG:"dbname=postgis_sample user=postgres" "nyc_hotspots.geojson" -nln nyc_hotspots

Now, run the following sql query on your postgis_sample database to see the table’s attributes:

select * from public.nyc_hotspots;

Export from PostGIS to GeoJSON

To generate a geojson file from one of your PostGIS tables, run the following command.

ogr2ogr -f "GeoJson" out.geojson PG:"host=localhost dbname=postgis_sample user=postgres password=<your_password>" \  -sql "SELECT * from nyc_hotspots"

*Note: Although you can use ogr2ogr to import and export shapefile format to and from PostGIS, it is recommended you use the shp2pgsql tool I discussed in an earlier post.