14 postgis

Adding data from a PostGIS database to the map

Setting up the conda env:

conda create -n geo python=3.8
conda activate geo
conda install geopandas
conda install mamba -c conda-forge
mamba install leafmap sqlalchemy psycopg2 -c conda-forge

Sample dataset:

• nyc_data.zip (Watch this video to load data into PostGIS)

In [1]:

# !pip install leafmap

# !pip install leafmap

Connecting to the database

In [2]:

import leafmap

import leafmap

You can directly pass in the user name and password to access the database. Alternative, you can define environment variables. The default environment variables for user and password are SQL_USER and SQL_PASSWORD, respectively.

The try...except... statements are only used for building the documentation website (https://leafmap.org) because the PostGIS database is not available on GitHub. If you are running the notebook with Jupyter installed locally and PostGIS set up properly, you don't need these try...except... statements.

In [3]:

try:
    con = leafmap.connect_postgis(
        database="nyc", host="localhost", user=None, password=None, use_env_var=True
    )
except:
    pass

try: con = leafmap.connect_postgis( database="nyc", host="localhost", user=None, password=None, use_env_var=True ) except: pass

Create a GeoDataFrame from a sql query.

In [4]:

sql = "SELECT * FROM nyc_neighborhoods"

sql = "SELECT * FROM nyc_neighborhoods"

In [5]:

try:
    gdf = leafmap.read_postgis(sql, con)
    display(gdf)
except:
    pass

try: gdf = leafmap.read_postgis(sql, con) display(gdf) except: pass

Display the GeoDataFrame on the interactive map.

In [6]:

try:
    m = leafmap.Map()
    m.add_gdf_from_postgis(
        sql, con, layer_name="NYC Neighborhoods", fill_colors=["red", "green", "blue"]
    )
    display(m)
except:
    pass

try: m = leafmap.Map() m.add_gdf_from_postgis( sql, con, layer_name="NYC Neighborhoods", fill_colors=["red", "green", "blue"] ) display(m) except: pass