Basics of reading and writing from geobase in geopandas

This guide demonstrates how to work with geospatial data using pandas, geopandas, and SQLAlchemy, and how to interact with a “geobase” database. Follow the steps below to replicate the process.

Import Libraries and Install Required Packages

First, import the necessary libraries:


import pandas as pd
import geopandas
from sqlalchemy import create_engine
 
pd.options.display.max_rows = 100
pd.options.display.max_columns = 300

Install geoalchemy2 for working with geospatial data in SQL databases:


pip install geoalchemy2

Download and Extract Geospatial Data

Download the Natural Earth dataset for countries and extract the files:


!wget https://naciscdn.org/naturalearth/10m/cultural/ne_10m_admin_0_countries.zip
!unzip ./content/ne_10m_admin_0_countries.zip

Load Geospatial Data

Load the extracted shapefile into a GeoDataFrame:


countries = geopandas.read_file("./content/ne_10m_admin_0_countries.shp")

Database Connection

Set up the connection to the geobase database using SQLAlchemy:


db_connection_url = "postgresql://username:password@hostaddress.geobase.app:port/database"
con = create_engine(db_connection_url)

connection string

Writing Data to Database

Write the GeoDataFrame to the geobase database:


countries.to_postgis("countries_test", con, if_exists="replace")

Reading Data from Database

Read geospatial data from the database:


sql = """
SELECT * FROM parks
"""
 
df = geopandas.read_postgis(sql, con, geom_col="location")

countries_test