2

I'm using Psycopg2 and PostgreSQL to try and isolate points from within polygons for ArcGIS. I generate a spatial condition from coordinates and can then pull all the points within that spatial condition using ST_Within.

If I have two spatial conditions and try and run this SQL query:

# Combine both spatial conditions using AND in the SQL query combined_spatial_condition = f"{catch_cond} AND {spatial_condition}" # Construct the SQL query sql_query = f"SELECT \"Date\", lai, lai2, lai3, fcover, \"Latitude\", \"Longitude\" FROM sdplus_good WHERE {combined_spatial_condition};"

I get all the points within {spatial_condition} what want is all the point that are in both {catch_cond} AND {spatial_condition}, and only those points.

If i run

 sql_query2 = f"SELECT \"Date\", lai , lai2, lai3, fcover ,\"Latitude\", \"Longitude\" FROM sdplus_good WHERE {spatial_condition};" sql_query3 = f"SELECT \"Date\", lai , lai2, lai3, fcover ,\"Latitude\", \"Longitude\" FROM sdplus_good WHERE {catch_cond};"

independently I get the correct set of points, so how do I run one SQL query to give the correct set?

Improve this question
7
  • 3
    What exactly are those conditions? Can you add an actual query from the PG server logs that is getting executed, including the conditions at query time? Commented Jan 2, 2024 at 17:35
  • If you don't provide the conditions, we can't tell you what's wrong. You state that you are running this for ArcGIS, but ArcGIS uses DA SearchCursor, not Psycopg2. Commented Jan 2, 2024 at 17:45
  • @geozelot the conditions are strings of length 32203 and 206535 characters each, created by a two functions, the first of which converts a shapefile to coordinates, and the second converts the coordinates to a spatial condition. I cant provide PG server logs because i'm running this in python. Commented Jan 2, 2024 at 18:04
  • @Vince I aqpologise, I'm not running it IN arcgis, but to generate data for arcgis. my scripts run in python using psycopg2. I can successfully extract the data for each individual condition, so I assume that the conditions are correct. all i want is to extract the data that appears in BOTH conditions Commented Jan 2, 2024 at 18:08
  • 3
    It's impossible to tell without seeing an example of the conditions. You could try f"({catch_cond}) AND ({spatial_condition})" Commented Jan 2, 2024 at 19:21

1 Answer 1

Reset to default
0

Even though all the comments seemed to think that the conditions were the problem (I had noted that the conditions worked correctly individually) the problem has been solved by @jbalk 's comment.

If I could mark it as an answer I would :)

The issue was with the placement of the brackets in the building of the sql query.

Thankyou @jbalk, the query now runs perfectly.

Improve this answer
6
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. Commented Jan 3, 2024 at 15:45
  • Looks like you misread the comments Commented Jan 3, 2024 at 16:44
  • Glad I could help. Your conditions must be something like x OR y OR z, so you have to wrap those in parenthesis to do an AND comparison of the condition sets. Commented Jan 3, 2024 at 20:36
  • @jbalk yes, the conditions are a set of polygons (which is why they're very long and not suited to being posted on here) and I wanted to extract items from a 26 million row database that are in both polygons. I'm also ignoring the bot 😂 Commented Jan 4, 2024 at 16:51
  • 2
    This is exactly what all comments about conditions were hinting at... Commented Jan 8, 2024 at 7:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.