2

I have some QGIS layers and I am performing some spatial queries with them using GeoPandas. I'm using psycopg2 to make a connection to a local PostgreSQL/PostGIS database. Then I use the read_postgis() function from GeoPandas to make a spatial query with my layers. Here is my code to get started.

import psycopg2
import geopandas as gpd

postgres_connection = psycopg2.connect(host="localhost", port=5432, database="BRE_2019_Test", user="my_username", password="my_password")

Now I do a few spatial queries, this all works correctly.

#this is a bunch of tax parcels in my study area
all_2019_parcels = gpd.read_postgis(('SELECT * FROM public."All_Parcels_2019" '), postgres_connection)

#these are parcels from the All_Parcels_2019 table which are spatially within the shape of Zone 1a
zone_1a_parcels = gpd.read_postgis(('SELECT ap.* FROM public."Zone1a" AS z1a, public."All_Parcels_2019" AS ap WHERE st_intersects(z1a.geom, ap.geom)'), postgres_connection)

zone_1a_parcels returns 1671 records, which is correct. Just to give you a visual, here is a screenshot of the same operation in QGIS using 'select by location'

enter image description here

Now that I have all_2019_parcels as a GeoPandas dataframe, I want to make a new column called 'Zone' and update the values for these selected parcels as 'Zone1a'.

Here is my best attempt so far and it works, sort of.

all_2019_parcels['Zone'] = zone_1a_parcels['parno'].isin(all_2019_parcels['parno'])
all_2019_parcels.loc[all_2019_parcels['Zone'] == True, 'Zone'] = 'Zone1a'

This labels the correct number of parcels as 'Zone1a'. However I have other zones (1b, 1c, and so on). When I run the same lines to select all parcels within Zone 1b for example, then the 'Zone1a' values in that column are overwritten.

I feel like I am going about this incorrectly. There must be a way to make a SQL query and label values in the 'zone' column of all_2019_parcels without creating the zone_1a_parcels object.

2

The problem is that by assigning to all_2019_parcels['Zone'] you are overwriting everything each run.

Actually, I wonder why this works, because zone_1a_parcels should have a smaller length than all_2019_parcels and the assignment should fail due to dimension mismatch, maybe you used this instead:

all_2019_parcels['Zone'] = all_2019_parcels['parno'].isin(zone_1a_parcels['parno'])

The solution is simply to use the Boolean indexing directly, instead of first storing it to a column (indexing only touches the lines where the index is True):

all_2019_parcels.loc[all_2019_parcels.parno.isin(zone_1a_parcels.parno), 'Zone'] = 'Zone1a'

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.