Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
from sqlalchemy import create_engine
import psycopg2
conn = psycopg2.connect("postgres://postgres:mypassword@db:5432/postgres")
sql = """with q as (
select q.id, q.Q_socio, st_setsrid(q.wkb_geometry, 4326) geom from quartiers_sociologiques q
), p as (
select st_setsrid(wkb_geometry, 4326) geom, price from sqfeet_properties as p
)
select q.id, q.Q_socio, q.geom, sum(p.price) sum_price from q, p
where q.geom && p.geom and st_intersects(q.geom, p.geom)
group by q.id, q.Q_socio, q.geom
"""
df = geopandas.GeoDataFrame.from_postgis(sql, conn, geom_col="geom")
df.plot("sum_price", legend="True")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment