0

I am really new to PostGIS (and PostgreSQL, for that matter) and I am having trouble figuring out how to query an existing table in a PG database with a polygon. I think I have identified the appropriate PG statement to use (ST_Contains) but it is not clear to me how I create the arguments for the statement from a geopandas dataframe. I'll walk you through a simple example of what I have thus far:

First, I load the relevant libraries:

from sqlalchemy import create_engine
import geopandas as gpd

Then I load the geodataframe I want to use to query the db:

poly = gpd.read_file('polygon.shp')

I have a database called 'my_db' and I create the connection:

dbname='my_db'
engine = create_engine(f'postgresql://user:password@localhost:5432/{dbname}')

This is where I get stuck.

How do I enter the geopandas geometries from "poly" into a format that PostGIS will understand?

sql = f'SELECT * FROM {dbname} WHERE ST_contains(poly[????], poly[????])'
queried_gdf = gpd.read_postgis(sql, engine, geom_col='geometry')
1
  • As per the help center please do not include chit chat like thanks and other statements of appreciation within your posts.
    – PolyGeo
    Nov 16, 2021 at 22:19

1 Answer 1

3

You're mixing and matching in a weird way. You either want to store your spatial data in PostGIS or as a GeoDataFrame. If you want to store the data as a PostGIS table, you can use GeoPandas to import the data into PostGIS, and at that point you have the entire PostgreSQL/PostGIS querying engine at your disposal:

poly = gpd.read_file('polygon.shp')
engine = create_engine(f'postgresql://user:password@localhost:5432/{dbname}')
poly.to_postgis('my_table',engine)

At this stage, you have the data stored natively in a PostGIS table. Your next step depends on what you're trying to do. ST_Contains kind of suggests you have a bounding box or some other spatial feature in mind, so you could store that info in another table (say, 'bbox') and do:

sql = 'SELECT * FROM my_table a INNER JOIN bbox b ON ST_Contains(b.geom,a.geom)'
queried_gdf = gpd.read_postgis(sql, engine)
7
  • I think you might have misunderstood my question. I have a PostGIS database ("my_db") set up and want to query it. I only want the values from my_db that fall within "poly". I edited the original question to provide more clarity.
    – jtam
    Nov 16, 2021 at 17:19
  • geopandas.org/en/stable/docs/reference/api/… you have the correct function. Are you sure you want to query an entire database, rather than just a table?
    – Encomium
    Nov 16, 2021 at 17:24
  • Ha! I told you I was a newbie. Yes, I meant a table. I'll correct that and will try your suggestion above!
    – jtam
    Nov 16, 2021 at 17:26
  • You'd want to have 2 different tables - 1 has the data you're trying to query, say points in a country, and the other one has the actual boundary you want to limit your query to, say a polygon layer of country boundaries. Does that more or less match your data and what you're trying to accomplish?
    – Encomium
    Nov 16, 2021 at 17:29
  • It is not a bounding box but many hexagonal polygons. Maybe I should perform a unary_union before putting into postgis? Although, this takes it out of geopandas DF and into shapely.
    – jtam
    Nov 16, 2021 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.