Import CSV and OSM data into PostgreSQL using ogr2ogr

ogr2ogr is the swiss knife for vector geometry conversion. You can import CSV with latitude and longitude columns as Point geometry into PostgreSQL. This tool also makes it easy to import OSM data to be imported into PostgreSQL with a lot of flexibility.

1. Insert CSV to PostgreSQL

Our CSV contains information about retail food stores including cafes, restaurants, grocery information with the location and name. Download the data here

image

We first read the metadata of the CSV using ogrinfo

ogrinfo -so filter_all_cat_data.csv filter_all_cat_data

Assuming you have a database already (postgres here), we run the following command to create postgis extension for postgres database. The connection string is of the format as described here

psql -c "create extension postgis;" "postgresql://postgres:1234@localhost:5432/postgres"

Finally, we insert the CSV into PostgreSQL table named cat_data_copy and assign CRS of EPSG:4326.

ogr2ogr -f PostgreSQL PG:"host=localhost user=postgres dbname=postgres password=1234" filter_all_cat_data.csv -oo X_POSSIBLE_NAMES=long_url -oo Y_POSSIBLE_NAMES=lat_url -nlt POINT -nln "cat_data_copy" -sql "select name,city,lat_url,long_url,type from filter_all_cat_data" -a_srs "EPSG:4326”

The following explains few of the flags

• -oo: X_POSSIBLE_NAMES and Y_POSSIBLE_NAMES allows us to specify geometry columns from CSV

• -nlt: Define the geometry type for the table

• -nln: alternate Table name (defaults to name of the file)

• -sql: write SQL to insert only selected columns into the table

---

2. Insert OSM data to PostgreSQL

Our OSM data is of Bahamas downloaded from geofabrik. You can download it from here

We first read the metadata of the OSM data using ogrinfo

ogrinfo -so bahamas-latest.osm.pbf multipolygons

We find about the geometry column, CRS and columns in the data. This will be used when inserting the data into the database.

Next we create postgis and hstore extensions in our postgres database.

psql -c "create extension hstore; create extension postgis" "postgresql://postgres:1234@localhost:5432/postgres"

Finally we insert the data into PostgreSQL with table name as bahamas_mpoly with only multipolygons. We convert the other_tags column into hstore and insert only those rows where the name column does not contain a null value. We also clip our data to a bounding box and promote our polygons to multipolygons to avoid error.

ogr2ogr -f PostgreSQL PG:"dbname=postgres host=localhost port=5432 user=postgres password=1234" bahamas-latest.osm.pbf multipolygons -nln bahamas_mpoly -lco COLUMN_TYPES=other_tags=hstore -overwrite -skipfailures -where "name is not null" -clipsrc -78 23 -73 27 -nlt PROMOTE_TO_MULTI

---

Video version of the blog can be found here