4

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.

The .read_postgis method from GeoPandas creates a geopandas dataframe object, which (from what I understand) is exactly like a pandas dataframe, except with a spatial component (one column in the dataframe called 'geometry' which stores the spatial values of that object).

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.

all_2019_parcels = gpd.read_postgis(('SELECT * FROM public."All_Parcels_2019" '), postgres_connection)

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)

all_2019_parcels is a bunch of tax parcels in my study area. zone_1a_parcels are those tax parcels which are inside the shape of zone1a. zone_1a_parcels returns 1671 records, which is correct. Just to give you a visual, here is a screenshot from QGIS. The parcels in yellow are those inside the shape Zone 1a

enter image description here

Now, I want to create a new column in the all_2019_parcels dataframe called 'zone' and I want to label the results from my spatial query as 'Zone1a' in this new column.

One way I have attempted to do this is:

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

But it seems to me that I should not create a slice of the dataframe (zone_1a_parcels) in order to edit column values in the all_2019_parcels dataframe. What would be a simple way to change these column values based on the results of a spatial query?

Erich Purpur
  • 1,337
  • 3
  • 14
  • 34
  • 1
    Could you post a screenshot showing `all_2019_parcels.head()` and `zone_1a_parcels.head()`? My understanding is that you managed to do what you wanted, but that you are not happy with the coding, is that so? If not, I did not get what you meant by " I want to label the results from my spatial query as 'Zone1a' in this new column.". Do you simply want a boolean column in `all_2019_parcels` to indicate whether the same row is present in `zone_1a_parcels` ? – Nicolas Jul 08 '20 at 18:10
  • I also asked this question in the GIS stack exchange page and received an answer here: https://gis.stackexchange.com/questions/366352/change-geopandas-column-value-from-postgis-query – Erich Purpur Oct 08 '20 at 18:04

0 Answers0