Skip to content

Instantly share code, notes, and snippets.

@maptastik maptastik/pgda_nosite_update.ipynb Secret
Last active Nov 9, 2018

Embed
What would you like to do?
A script to update the Park and Greenway Damage Assessment feature layer w/ parkid and location name
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
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!")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.