85 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 geemap sqlalchemy psycopg2 -c conda-forge

Sample dataset:

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

Connecting to the database

In [ ]:

import geemap
from IPython.display import display

import geemap from IPython.display import display

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://geemap.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 [ ]:

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

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

Create a GeoDataFrame from a sql query.

In [ ]:

sql = 'SELECT * FROM nyc_neighborhoods'

sql = 'SELECT * FROM nyc_neighborhoods'

In [ ]:

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

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

Convert gdf to ee.FeatureCollection

In [ ]:

try:
    m = geemap.Map()
    fc = geemap.gdf_to_ee(gdf)
    m.addLayer(fc, {}, "NYC EE")
    m.centerObject(fc)
    display(m)
except:
    pass

try: m = geemap.Map() fc = geemap.gdf_to_ee(gdf) m.addLayer(fc, {}, "NYC EE") m.centerObject(fc) display(m) except: pass

Display the GeoDataFrame on the interactive map.

In [ ]:

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

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

---

Last update: 2023-03-19
Created: 2021-11-28