0

I was wondering if there was a way to send one request to a PostgreSQL server with the PostGIS extension that contained multiple points, and then find the polygons that they are located in. For example, I am currently using the code below to query to see where multiple points are located in a polygon shapefile.

from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker
from land_use_db_model import SpatialLandUse    

# Query for unique lat lon vals
for i, df_tuple in enumerate(coord_df.itertuples(index=False)):

    lat = df_tuple[0]
    lon = df_tuple[1]

    """How to query to see where the point resides"""
    query = session.query(SpatialLandUse).filter(
        func.ST_Contains(SpatialLandUse.geom, 'SRID=4326;POINT({} {})'.format(lon, lat))
    )

    for result in query:
        coord_df.iloc[i, 2] = result.gridcode

So as you can see I am using sqlalchemy to perform the queries, but I am comfortable with using psycopg2 as well if that would be easier. I just feel like I spend a lot of time in this for loop and that it might be faster to send all of the points for the server to process at once.

  • Some clarification, do you have the points in PostGIS and the polygons in shapefile? And you want to find out which points lie within which polygon, correct? Seems like you have converted your shapefile into a pandas dataframe? – Edmond Apr 8 at 20:13
  • @Edmond I have the polygons in PostGIS and the points are simply lat lon coordinates that I pass into the query (the lat and lon variables in the code). – Wade Apr 8 at 20:39
  • Can the whole spatial operation happen within python or in PostGIS? Looping through python code to construct a single query seems unwieldy. What is your limitation? – Edmond Apr 8 at 20:52
  • @Edmond it seems as though it would improve performance to send one query rather than 300+, but I may be wrong. – Wade Apr 8 at 20:59

Your Answer

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

Browse other questions tagged or ask your own question.