Selectively Update Data in a Hosted Feature Layer

[asmitashukla]

Hello - I have monitoring locations in a SQL Server database. I am creating a hosted feature layer by querying data and Python APIs using the process below-

cnxn_str = ("Driver={SQL Server Native Client 11.0};"
"Server=xxxx;"
"Database=xxxx;"
"UID=xxxx;"
"PWD=xxxx;")
cnxn = pyodbc.connect(cnxn_str)
Locationquery = """SELECT [X],[CollectionLatitude],[CollectionLongitude] FROM xxxx"""
Locations = pd.read_sql(Locationquery, cnxn)

Locations['SHAPE'] = '"spatialReference": {"wkid": 4326}, {"x":' + Locations['CollectionLongitude'].astype('str') + ', "y":' + Locations['CollectionLatitude'].astype('str') + '}'

sdf=GeoAccessor.from_xy(Locations,'CollectionLongitude','CollectionLatitude')

lyr1 = sdf.spatial.to_featurelayer("MonitoringLocations")

Every week, some locations are updated in the database and I would like to update the features in the hosted feature layer using Python APIs. The truncate() option lets me delete all features but I want to overwrite/delete only the features that are updated in the database. Here is how I am pulling the updated locations-

UpdatedLocationQuery = """SELECT [X],[CollectionLatitude],[CollectionLongitude] FROM xxxx WHERE LastUpdated >= DATEADD(day, -7, GETDATE())

UpdatedLocations = pd.read_sql(UpdatedLocationQuery, cnxn)

How can I selectively update features in the hosted feature layer? Any help is greatly appreciated.

Thank You.

[EarlMedina]

Hello.

What you are looking for is edit_features. This allows you to do adds, updates, and deletes. You can get a sense of how to use this method here: Editing Features | ArcGIS API for Python

[jcarlson · Accepted solution]

This is a process we do regularly in my organization. In order to selectively update features, you need to know the objectid from the hosted layer, so you need to do a couple steps before applying edits.

1. Get your list of edited features
2. Query the hosted layer for matching features
3. Join the objectid

I detail the process in a Jupyter Notebook which you can find here: https://github.com/jdcarls2/ilgisa-2022/blob/main/hosted-copy/hosted-copy.ipynb

But here's a short version of the code. This assumes the hosted layer is already published.

from arcgis import GIS
import pandas as pd

# portal connection and feature layer
gis = GIS('your portal url', 'user', 'password')
fl = gis.content.get('itemid of hosted layer').layers[0]

# database connection
cnxn_str = (
    "Driver={SQL Server Native Client 11.0};"
    "Server=xxxx;"
    "Database=xxxx;"
    "UID=xxxx;"
    "PWD=xxxx;"
)

cnxn = pyodbc.connect(cnxn_str)

Locationquery = """SELECT [X],[CollectionLatitude],[CollectionLongitude] FROM xxxx WHERE LastUpdated >= DATEADD(day, -7, GETDATE()"""

# query to dataframe
Locations = pd.read_sql(Locationquery, cnxn)

# reshape, convert to spatial df
Locations['SHAPE'] = '"spatialReference": {"wkid": 4326}, {"x":' + Locations['CollectionLongitude'].astype('str') + ', "y":' + Locations['CollectionLatitude'].astype('str') + '}'

sdf = GeoAccessor.from_xy(Locations,'CollectionLongitude','CollectionLatitude')

# query hosted layer
# NOTE: this assumes that your layer has some kind of unique ID in it present in both the hosted layer and the SDE table. if not, consider adding something like a GUID to both

oids = fl.query(out_fields=['objectid', 'shared_id_field'], as_df=True)

merged = sdf.merge(oids, how='inner', on='shared_id_field')

fl.edit_features(updates=merged.spatial.to_featureset())

[asmitashukla]

Thanks Josh! It worked beautifully. All I had to do was to select the updated SHAPE column after the merge.