topic Re: Selectively Update Data in a Hosted Feature Layer in ArcGIS API for Python Questions

Selectively Update Data in a Hosted Feature Layer

asmitashukla — Thu, 07 Sep 2023 21:36:45 GMT

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.

Re: Selectively Update Data in a Hosted Feature Layer

EarlMedina — Fri, 08 Sep 2023 00:55:45 GMT

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

Re: Selectively Update Data in a Hosted Feature Layer

jcarlson — Fri, 08 Sep 2023 13:55:05 GMT

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.

Get your list of edited features
Query the hosted layer for matching features
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())

Re: Selectively Update Data in a Hosted Feature Layer

asmitashukla — Fri, 08 Sep 2023 23:59:37 GMT

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