# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Using GeoPandas
#
# **Setting up the conda env:**
#
# ```
# conda create -n geo python=3.8
# conda activate geo
# conda install mamba -c conda-forge
# mamba install geemap geopandas descartes rtree=0.9.3 -c conda-forge
# mamba install ipython-sql sqlalchemy psycopg2 -c conda-forge
# ```
#
# **Sample dataset:**
# - [nyc_data.zip](https://github.com/giswqs/postgis/raw/master/data/nyc_data.zip) (Watch this [video](https://youtu.be/fROzLrjNDrs) to load data into PostGIS)
#
# **References**:
# - [Introduction to PostGIS](https://postgis.net/workshops/postgis-intro)
# - [Using SQL with Geodatabases](https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/sql-and-enterprise-geodatabases.htm)

# ## Connecting to the database

import os
from sqlalchemy import create_engine

host = "localhost"
database = "nyc"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')

connection_string = f"postgresql://{user}:{password}@{host}/{database}"

engine = create_engine(connection_string)

from sqlalchemy import inspect

insp = inspect(engine)
insp.get_table_names()

# ## Reading data from PostGIS

import geopandas as gpd

sql = 'SELECT * FROM nyc_neighborhoods'

gdf = gpd.read_postgis(sql, con=engine)

gdf

gdf.crs

# ## Writing files

out_dir = os.path.expanduser('~/Downloads')
if not os.path.exists(out_dir):
    os.makedirs(out_dir)

out_json = os.path.join(out_dir, 'nyc_neighborhoods.geojson')
gdf.to_file(out_json, driver="GeoJSON")

out_shp = os.path.join(out_dir, 'nyc_neighborhoods.shp')
gdf.to_file(out_shp)

gdf.crs

# ## Measuring area

gdf = gdf.set_index("name")

gdf["area"] = gdf.area
gdf["area"]

# ## Getting polygon bounary

gdf['boundary'] = gdf.boundary
gdf['boundary']

# ## Getting polygon centroid

gdf['centroid'] = gdf.centroid
gdf['centroid']

# ## Making maps

gdf.plot()

gdf.plot("area", legend=True, figsize=(10, 8))

gdf = gdf.set_geometry("centroid")
gdf.plot("area", legend=True,figsize=(10, 8))

ax = gdf["geom"].plot(figsize=(10, 8))
gdf["centroid"].plot(ax=ax, color="black")

gdf = gdf.set_geometry("geom")

# ## Reprojecting data

sql = 'SELECT * FROM nyc_neighborhoods'

gdf = gpd.read_postgis(sql, con=engine)

gdf_crs = gdf.to_crs(epsg="4326")

gdf_crs

geojson = gdf_crs.__geo_interface__

# ## Displaying data on an interative map

import geemap

m = geemap.Map(center=[40.7341, -73.9113], zoom=10, ee_initialize=False)
m

style = {
    "stroke": True,
    "color": "#000000",
    "weight": 2,
    "opacity": 1,
    "fill": True,
    "fillColor": "#0000ff",
    "fillOpacity": 0.4,
}

m.add_geojson(geojson, style=style, layer_name="nyc neighborhoods")

sql2 = 'SELECT * FROM nyc_subway_stations'

gdf_subway = gpd.read_postgis(sql2, con=engine)

gdf_subway_crs = gdf_subway.to_crs(epsg="4326")

subway_geojson = gdf_subway_crs.__geo_interface__

m.add_geojson(subway_geojson, layer_name="nyc subway stations")

StarcoderdataJupyterScriptsDedupFiltered