Skip to content

Instantly share code, notes, and snippets.

@maptastik

maptastik/pgda_nosite_update.ipynb Secret

Last active November 9, 2018 16:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save maptastik/cbf71f47c370329aec4696941a19177a to your computer and use it in GitHub Desktop.
Save maptastik/cbf71f47c370329aec4696941a19177a to your computer and use it in GitHub Desktop.
Download ZIP
A script to update the Park and Greenway Damage Assessment feature layer w/ parkid and location name
Display the source blob
Display the rendered blob
Raw
pgda_nosite_update.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Parks and Greenway Damage Assessment Updater Test\n",
"\n",
"Scripting out a proces whereby AGOL data is pulled into PostGIS, enriched with location name and park ID, and then used to update the AGOL data."
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"from copy import deepcopy\n",
"import json\n",
"\n",
"from arcgis.gis import GIS\n",
"from arcgis import features\n",
"from arcgis import geometry\n",
"import pandas as pd\n",
"import psycopg2\n",
"from shapely.geometry import Point, mapping"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Authenticate"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"gis = GIS(profile = \"RaleighPRCR_Profile\")\n",
"print(\"Logged in as {}\".format(gis.properties.user.username))"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Get data from `nosite` view"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"nosite_item = gis.content.get(\"e989ae7806354de68d2385081f74b6c2\")\n",
"print(\"Getting {}\".format(nosite_item.name))\n",
"nosite_flayer = nosite_item.layers[0]\n",
"nosite_fset = nosite_flayer.query(out_sr=4326)\n",
"nosite_json = json.loads(nosite_fset.to_json)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Connect to PostGIS database"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Connecting to database\")\n",
"connection = psycopg2.connect(database=\"raleighprcr_pgda\", user=\"raleighprcr\", password=\"<PASSWORD\">, host=\"postgresql-raleighprcr.alwaysdata.net\")\n",
"cursor = connection.cursor()\n",
"print(connection.dsn)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Add `nosite` data to **pgda_nosite** table"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Dropping table pgda_nosite and all dependent views\")\n",
"try:\n",
" cursor.execute(\"DROP TABLE IF EXISTS pgda_nosite CASCADE\")\n",
"except psycopg2.Error as e:\n",
" print(e.pgerror)\n",
"finally:\n",
" connection.commit()\n",
"\n",
"print(\"Creating table pgda_nosite\")\n",
"try:\n",
" cursor.execute(\"CREATE TABLE pgda_nosite (id SERIAL PRIMARY KEY, objectid INTEGER, globalid VARCHAR, location_type VARCHAR, parkid INTEGER, location_name VARCHAR, geom GEOMETRY)\")\n",
"except psycopg2.Error as e:\n",
" print(e.pgerror)\n",
"finally:\n",
" connection.commit()\n",
"\n",
"print(\"Inserting data from AGOL to pgda_nosite\")\n",
"try:\n",
" for f in nosite_json[\"features\"]:\n",
" objectid = f[\"attributes\"][\"OBJECTID\"]\n",
" globalid = f[\"attributes\"]['GlobalID']\n",
" location_type = f[\"attributes\"][\"location_type\"]\n",
" p = Point(float(f[\"geometry\"][\"x\"]), float(f[\"geometry\"][\"y\"]))\n",
" cursor.execute(\"INSERT INTO pgda_nosite (objectid, globalid, location_type, geom) VALUES ('{}', '{}', '{}', ST_GeomFromText('{}'))\".format(objectid, globalid, location_type, p.wkt))\n",
"except psycopg2.Error as e:\n",
" print(e.pgerror)\n",
"finally:\n",
" cursor.execute(\"SELECT UpdateGeometrySRID('pgda_nosite', 'geom', 4326)\")\n",
" connection.commit()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Create view **pgda_test_sited**\n",
"\n",
"This view contains the results of a query with parks and greenway trails data to provide info about site and parkid"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Running query to assign parkid and location name to parks, location name to greenways\")\n",
"pgda_siting_query = \"\"\"\n",
"CREATE OR REPLACE VIEW pgda_sited as (\n",
"\tWITH pda_nosite as (\n",
"\t\tSELECT *\n",
"\t\tFROM pgda_nosite\n",
"\t\tWHERE location_type = 'PARK'\n",
"\t), gda_nosite as (\n",
"\t\tSELECT *\n",
"\t\tFROM pgda_nosite\n",
"\t\tWHERE location_type = 'GREENWAY'\n",
"\t), pda_intersection as (\n",
"\t\tSELECT pda.objectid, pda.globalid, pda.location_type, p.parkid, p.name AS location_name, pda.geom\n",
"\t\tFROM pda_nosite as pda, parks as p\n",
"\t\tWHERE ST_INTERSECTS(pda.geom, p.geom)\n",
"\t), pda_non_intersect_pts as (\n",
"\t\tSELECT pda.objectid, pda.globalid, pda.location_type, p.parkid, p.name AS location_name, pda.geom\n",
"\t\tFROM pda_nosite as pda\n",
"\t\tLEFT JOIN parks as p\n",
"\t\tON ST_INTERSECTS(pda.geom, p.geom)\n",
"\t\tWHERE p.parkid IS NULL\n",
"\t), pda_non_intersection as (\n",
"\t\tSELECT pda.objectid, pda.globalid, pda.location_type, p.parkid, p.name as location_name, pda.geom\n",
"\t\tFROM pda_non_intersect_pts as pda, parks as p\n",
"\t\tWHERE ST_INTERSECTS(ST_BUFFER(pda.geom::geography, 100), p.geom)\n",
"\t), gda_nearest as (\n",
"\t\tSELECT gda.objectid,\n",
"\t\t\t gda.globalid,\n",
"\t\t gda.location_type,\n",
"\t\t\t 0 as parkid,\n",
"\t\t\t (SELECT g.trail_name \n",
"\t\t\t \tFROM greenway_trails AS g \n",
"\t\t\t \tORDER BY st_distance(gda.geom, g.geom) \n",
"\t\t\t \tLIMIT 1) as location_name,\n",
"\t\t\t\tgda.geom\n",
"\t\tFROM gda_nosite as gda\n",
"\t)\n",
"\n",
"\tSELECT * FROM pda_intersection\n",
"\tUNION\n",
"\tSELECT * FROM pda_non_intersection\n",
"\tUNION\n",
"\tSELECT objectid, globalid, location_type, CAST(parkid AS int), location_name, geom FROM gda_nearest\n",
")\n",
"\"\"\"\n",
"cursor.execute(pgda_siting_query)\n",
"connection.commit()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Read in `no_site` view as a pandas dataframe"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Generate pandas DataFrame of AGOL data\")\n",
"nosite_df = nosite_fset.df\n",
"nosite_df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Read in **pgda_sited** as a pandas dataframe"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Generate pandas DataFrame of pgda_sited\")\n",
"pgda_sited_df = pd.read_sql(\"SELECT objectid, parkid, location_name FROM pgda_sited\", connection)\n",
"pgda_sited_df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Merge **nosite_df** and **pgda_sited_df**"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Joining pgda_sited_df to nosite_df\")\n",
"overlap_rows = pd.merge(left = nosite_df, right = pgda_sited_df, how = \"inner\", left_on = \"OBJECTID\", right_on = \"objectid\")\n",
"overlap_rows.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Generating list of updates\")\n",
"features_for_update = []\n",
"all_features = nosite_fset.features\n",
"\n",
"for objectid in overlap_rows['OBJECTID']:\n",
" original_feature = [f for f in all_features if f.attributes[\"OBJECTID\"] == objectid][0]\n",
" feature_to_be_updated = deepcopy(original_feature)\n",
" \n",
" print(\">> original_feature\")\n",
" print(str(original_feature))\n",
" \n",
" matching_row = pgda_sited_df.where(pgda_sited_df[\"objectid\"] == objectid).dropna(subset = [\"location_name\"])\n",
" \n",
" input_geometry = {\"y\": float(original_feature.geometry[\"y\"]),\n",
" \"x\": float(original_feature.geometry[\"x\"])}\n",
" output_geometry = geometry.project(geometries = [input_geometry],\n",
" in_sr = 4326,\n",
" out_sr = 3857)\n",
" \n",
" feature_to_be_updated.geometry = output_geometry[0]\n",
" if feature_to_be_updated.attributes[\"location_type\"] == \"PARK\":\n",
" feature_to_be_updated.attributes[\"parkid\"] = int(matching_row[\"parkid\"])\n",
" feature_to_be_updated.attributes[\"location_name\"] = matching_row[\"location_name\"].values[0]\n",
" else:\n",
" feature_to_be_updated.attributes[\"parkid\"] = None\n",
" feature_to_be_updated.attributes[\"location_name\"] = matching_row[\"location_name\"].values[0]\n",
" \n",
" features_for_update.append(feature_to_be_updated)\n",
" \n",
" print(\">> feature_to_be_updated\")\n",
" print(str(feature_to_be_updated))\n",
" print(\"========================================================================\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"features_for_update"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Updating features\")\n",
"nosite_flayer.edit_features(updates = features_for_update)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"print(\"Update complete!\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.6"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Raw
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!")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment