How to create geojson files from a Postgis Database using Python
I struggled a lot to find a way to create (good) geojson files from a Postgis database. Today I found the answer and I hope this helps other people that are trying to do the same.
First, you have to create your Postgis spatial database. Here is an excellent article by Steve Gattuso on how to set this up: https://www.stevegattuso.me/2019/05/29/postgis-reverse-geocoder.html. Now that you have your database, lets head to the main part of this article.
I used Python’s Pandas to store the query’s info and psycopg2 for the database connection. Feel free to use another Postgres database adapter.
In this example, I’ll be creating a geojson for Chile’s admin_level = 8 (Comunas). Check out admin_level information for countries in https://wiki.openstreetmap.org/wiki/Tag:boundary%3Dadministrative.
Step 1: Database connection
geo_chile = psycopg2.connect(
host=localhost,
user=username,
password=password,
database=”geo_chile”
)
Where “geo_chile” is the name I set for my Postgis database.
Step 2: Load query to Pandas Dataframe and store FeatureCollection
collection_query = “SELECT jsonb_build_object(‘type’, ‘FeatureCollection’, ‘features’, jsonb_agg(feature)) FROM (SELECT jsonb_build_object(‘type’, ‘Feature’, ‘geometry’, ST_AsGeoJSON(ST_Transform(way, 4326))::jsonb, ‘properties’, jsonb_build_object(‘name’, name)) AS feature FROM (SELECT * from planet_osm_polygon WHERE admin_level = ‘8’) inputs) features”
feature_collection = pd.read_sql(collection_query, geo_chile)
feature_collection_dict = feature_collection.iloc[0][‘jsonb_build_object’]
Big and complex query. This query gives us a FeatureCollection object, which is basically what we need to create the geojson file. For more details on this query, take a look on https://gis.stackexchange.com/questions/112057/sql-query-to-have-a-complete-geojson-feature-from-postgis. The second command reads the query from the database and stores it feature_collection, which is a Dataframe. Given that we´re getting a FeatureCollection object right away, our Dataframe has only 1 row. This is why we get the geojson dictionary by calling iloc[0] on the jsonb_build_object column. I’m sure Pandas has better ways to deal with 1 row queries but this is the way I did it.
Step 3: Write our json file
import json
with open(“data/chile_comunas.json”, “w”) as outfile:
json.dump(feature_collection_dict, outfile)
Using json.dump we save our geojson dict into a file so we can use it in Tableau e.g.
That’s all! In 3 easy steps we can create good geojson files from a Postgis database. I hope this article was useful for you.
