A script to update the Park and Greenway Damage Assessment feature layer w/ parkid and location name

pgda_nosite_update.ipynb

pgda_nosite_updater.py

from copy import deepcopy
import json

from arcgis.gis import GIS
from arcgis import features
from arcgis import geometry
import pandas as pd
import psycopg2
from shapely.geometry import Point, mapping


# Authenticate
gis = GIS(profile = "RaleighPRCR_Profile")
print("Logged in as {}".format(gis.properties.user.username))


# Get data from `nosite` view
nosite_item = gis.content.get("12ee1719e8844b548dc1d92f1c1f3dea")
print("Getting {}".format(nosite_item.name))
nosite_flayer = nosite_item.layers[0]
nosite_fset = nosite_flayer.query(out_sr=4326)
nosite_json = json.loads(nosite_fset.to_json)
if len(nosite_json["features"]) > 0:
	print("There are {} features to update".format(len(nosite_json["features"])))
else:
	print("No new features to update. Exiting process.")
	exit()

# Connect to PostGIS database
print("Connecting to database")
connection = psycopg2.connect(database="raleighprcr_pgda", user="raleighprcr", password="<PASSWORD>", host="postgresql-raleighprcr.alwaysdata.net")
cursor = connection.cursor()
print(connection.dsn)


# Add `nosite` data to **pgda_nosite** table
print("Dropping table pgda_nosite and all dependent views")
try:
    cursor.execute("DROP TABLE IF EXISTS pgda_nosite CASCADE")
except psycopg2.Error as e:
    print(e.pgerror)
finally:
    connection.commit()

print("Creating table pgda_nosite")
try:
    cursor.execute("CREATE TABLE pgda_nosite (id SERIAL PRIMARY KEY, objectid INTEGER, globalid VARCHAR, location_type VARCHAR, parkid INTEGER, location_name VARCHAR, geom GEOMETRY)")
except psycopg2.Error as e:
    print(e.pgerror)
finally:
    connection.commit()

print("Inserting data from AGOL to pgda_nosite")
try:
    for f in nosite_json["features"]:
        objectid = f["attributes"]["OBJECTID"]
        globalid = f["attributes"]['GlobalID']
        location_type = f["attributes"]["location_type"]
        p = Point(float(f["geometry"]["x"]), float(f["geometry"]["y"]))
        cursor.execute("INSERT INTO pgda_nosite (objectid, globalid, location_type, geom) VALUES ('{}', '{}', '{}', ST_GeomFromText('{}'))".format(objectid, globalid, location_type, p.wkt))
except psycopg2.Error as e:
    print(e.pgerror)
finally:
    cursor.execute("SELECT UpdateGeometrySRID('pgda_nosite', 'geom', 4326)")
    connection.commit()


# Create view **pgda_test_sited**
# This view contains the results of a query with parks and greenway trails data to provide info about site and parkid
print("Running query to assign parkid and location name to parks, location name to greenways")
pgda_siting_query = """
CREATE OR REPLACE VIEW pgda_sited as (
	WITH pda_nosite as (
		SELECT *
		FROM pgda_nosite
		WHERE location_type = 'PARK'
	), gda_nosite as (
		SELECT *
		FROM pgda_nosite
		WHERE location_type = 'GREENWAY'
	), pda_intersection as (
		SELECT pda.objectid, pda.globalid, pda.location_type, p.parkid, p.name AS location_name, pda.geom
		FROM pda_nosite as pda, parks as p
		WHERE ST_INTERSECTS(pda.geom, p.geom)
	), pda_non_intersect_pts as (
		SELECT pda.objectid, pda.globalid, pda.location_type, p.parkid, p.name AS location_name, pda.geom
		FROM pda_nosite as pda
		LEFT JOIN parks as p
		ON ST_INTERSECTS(pda.geom, p.geom)
		WHERE p.parkid IS NULL
	), pda_non_intersection as (
		SELECT pda.objectid, pda.globalid, pda.location_type, p.parkid, p.name as location_name, pda.geom
		FROM pda_non_intersect_pts as pda, parks as p
		WHERE ST_INTERSECTS(ST_BUFFER(pda.geom::geography, 100), p.geom)
	), gda_nearest as (
		SELECT gda.objectid,
			   gda.globalid,
		       gda.location_type,
			   0 as parkid,
			   (SELECT g.trail_name 
			 	FROM greenway_trails AS g 
			 	ORDER BY st_distance(gda.geom, g.geom)  
			 	LIMIT 1) as location_name,
				gda.geom
		FROM gda_nosite as gda
	)

	SELECT * FROM pda_intersection
	UNION
	SELECT * FROM pda_non_intersection
	UNION
	SELECT objectid, globalid, location_type, CAST(parkid AS int), location_name, geom FROM gda_nearest
)
"""
cursor.execute(pgda_siting_query)
connection.commit()


# Read in `no_site` view as a pandas dataframe
print("Generate pandas DataFrame of AGOL data")
nosite_df = nosite_fset.df
nosite_df.head()


# Read in **pgda_sited** as a pandas dataframe
print("Generate pandas DataFrame of pgda_sited")
pgda_sited_df = pd.read_sql("SELECT objectid, parkid, location_name FROM pgda_sited", connection)
pgda_sited_df.head()


# Merge **nosite_df** and **pgda_sited_df**
print("Joining pgda_sited_df to nosite_df")
overlap_rows = pd.merge(left = nosite_df, right = pgda_sited_df, how = "inner", left_on = "OBJECTID", right_on = "objectid")
overlap_rows.head()

print("Generating list of updates")
features_for_update = []
all_features = nosite_fset.features

for objectid in overlap_rows['OBJECTID']:
    original_feature = [f for f in all_features if f.attributes["OBJECTID"] == objectid][0]
    feature_to_be_updated = deepcopy(original_feature)
    
    print(">> original_feature")
    print(str(original_feature))
    
    matching_row = pgda_sited_df.where(pgda_sited_df["objectid"] == objectid).dropna(subset = ["location_name"])
    
    input_geometry = {"y": float(original_feature.geometry["y"]),
                      "x": float(original_feature.geometry["x"])}
    output_geometry = geometry.project(geometries = [input_geometry],
                                       in_sr = 4326,
                                       out_sr = 3857)
    
    feature_to_be_updated.geometry = output_geometry[0]
    if feature_to_be_updated.attributes["location_type"] == "PARK":
        feature_to_be_updated.attributes["parkid"] = int(matching_row["parkid"])
        feature_to_be_updated.attributes["location_name"] = matching_row["location_name"].values[0]
    else:
        feature_to_be_updated.attributes["parkid"] = None
        feature_to_be_updated.attributes["location_name"] = matching_row["location_name"].values[0]
        
    features_for_update.append(feature_to_be_updated)
    
    print(">> feature_to_be_updated")
    print(str(feature_to_be_updated))
    print("========================================================================")

print(features_for_update)

print("Updating features")
nosite_flayer.edit_features(updates = features_for_update)
print("Update complete!")