topic Re: Sync hosted feature services to postgres database in ArcGIS Online Questions

Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 08:50:26 GMT

Sync hosted feature services to postgres database

Does anyone have any experience with syncing hosted feature services with an external postgresql database? We're not using enterprise and would rather not have to go through the cost and effort of setting up and administering enterprise. We are building a web application however which will have non-gis business related data (customer info, order details etc.) which we need to relate to location data hosted on AGOL.

The only solution we can really think of is to sync/copy the gis data from AGOL to the the postgres database. Are there any potential pitfalls/issues? Is it even possible? I'm guessing we might have issues with maintaining relationships?

Re: Sync hosted feature services to postgres database

John_Spence — Thu, 17 Jun 2021 13:53:03 GMT

I ran into the same issue a while ago myself. Attached is a copy of the process I use to lift and insert. Basic gist is, I use a Pro project to designate the layer I want to deal with and put the SDE connections to be used there. I am trying to remember if I run into issues with the service extraction due to limitations on max records of the service or not, but I can't recall it at the moment.

Oh! I probably should mention that the password for the portal or AGOL user specified needs to be encoded in base64. Obviously, not secure, but good enough for the casual eye.

Re: Sync hosted feature services to postgres database

jcarlson — Thu, 17 Jun 2021 14:04:44 GMT

We have nightly scripts that sync hosted layers with both postgres and MSSQL servers, and they work great! You need to know a bit of python, but it's definitely possible.

Prep Work

If you don't have one already, develop a query statement that returns the data you want from the database and save it as its own *.sql file. Or maybe you're just copying entire tables? You may be able to skip this.

You'll also want to create the hosted layer first. You can do this by first publishing an extract from the database, or by creating an empty layer. As long as the schema is correct.

The Process

Using pandas, read your sql query (or table) into a dataframe.
Make any adjustments to the data you need
Using the ArcGIS Python API's Dataframe.spatial submodule, you can edit the hosted layer and supply the dataframe as the new / updated features.

Considerations

If you just want to keep it simple, you can truncate the hosted layer and just re-add everything fresh. Depending on the size of your table and the frequency of updates, this may or may not be feasible. If you want to go a different route than truncate/append, you'll need some way of identifying unique records in the postgres database and comparing them against the hosted layer.

An easy way to do this is to include the postgres primary key as its own field in the hosted layer. You can query the hosted layer to its own dataframe, and then compare the two dataframes to identify rows which have changed between the postgres table and the hosted layer. The result of the comparison could allow you to subset the postgres dataframe, then submit that subset as your adds/edits, joining in the hosted objectID / globalID based on the shared primary key field.

But that can get a bit complicated.

Either way, get that script scheduled to run on a machine that has access to the postgres database and you're set!

We have about 20 hosted layers that all "mirror" an external database or some query therefrom, which for whatever reason cannot be published directly from said database. Our scripts run weeknights with some added exception handling and logging, but 99% of the time they go off without a hitch.

Long story short: if you have the patience to write up and debug some python, you can do pretty much anything you want between a hosted layer and an external database.

Re: Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 14:16:07 GMT

Wow thanks! Will need some time to digest it. I assume this requires a server license in order to setup your postgres geodatabase etc.?

Re: Sync hosted feature services to postgres database

John_Spence — Thu, 17 Jun 2021 14:19:33 GMT

Yes, in my example it is working on the assumption you have a SDE database (server license needed), but in all honesty you could just change the connection type to be whatever you want and go from there.

Re: Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 14:25:46 GMT

Ok that might be the way we go but I'm working on the assumption that we won't have an SDE database. Depends on budget!

Re: Sync hosted feature services to postgres database

John_Spence — Thu, 17 Jun 2021 14:31:50 GMT

Gotchya. Well, you can add non-SDE databases in a Pro project so you could be good to go there. There is also the option of scraping the data from the service, again though, you can run into the max record count limitation.

Assuming you have to authenticate to get it at it:

def get_token():
#-------------------------------------------------------------------------------
# Name: Function -
# Purpose:
#-------------------------------------------------------------------------------

data = checklist.b64decode(AGOL_Pass)
pw = data.decode("ascii")

url = 'https://www.arcgis.com/sharing/rest/generateToken'
values = {'f': 'json',
'username': AGOL_User,
'password': pw,
'referer' : 'https://www.arcgis.com',
'expiration' : '10'}

data = urllib.parse.urlencode(values).encode("utf-8")
req = urllib.request.Request(url)

response = None
while response is None:
try:
response = urllib.request.urlopen(req,data=data)
except:
pass

the_page = response.read()

#Garbage Collection with some house building
payload_json = the_page.decode('utf8')
payload_json = json.loads(payload_json)

edit_token = payload_json['token']

return (edit_token)

You can use something like this to scrape it out. The script is to count and ID a specific item in my case, but you can swap things around easily enough to just scrape the entire dataset.

def queryCount(scriptName, edit_token):
#-------------------------------------------------------------------------------
# Name: Function -
# Purpose:
#-------------------------------------------------------------------------------

FS_service = service_StatusURL + '/query/?token={}'.format(edit_token)

where_statement = 'JobName=\'{}\''.format(scriptName)

data = urllib.parse.urlencode({'f': 'json', 'where': where_statement, 'returnCountOnly': 'true'}).encode('utf-8')

req = urllib.request.Request(FS_service)
response = urllib.request.urlopen(req,data=data)
response_payload = response.read()
response_payload = json.loads(response_payload)

item_count = response_payload['count']

if item_count > 0:
response = None
while response is None:
try:
where_statement = 'JobName=\'{}\''.format(scriptName)

data = urllib.parse.urlencode({'f': 'json', 'where': where_statement, 'outFields':'OBJECTID'}).encode('utf-8')

req = urllib.request.Request(FS_service)
response = urllib.request.urlopen(req,data=data)
response_payload = response.read()
response_payload = json.loads(response_payload)
for oid_item in response_payload['features']:
objectID = oid_item['attributes']['OBJECTID']

except:
print ('\t...Failure to count. Trying again.')

time.sleep(30)
response = None
else:
objectID = '0'

return (item_count, objectID)

Re: Sync hosted feature services to postgres database

jcarlson — Thu, 17 Jun 2021 14:49:00 GMT

from arcgis import GIS import pandas gis = GIS('your-org-url', 'user', 'password') # Alter list index as needed, if multiple layers, or use '.tables[0]' for non-spatial hosted_fl = gis.content.get('hosted-layer-itemid').layers[0] psql_df = pandas.read_sql('your sql query', 'postgresql+psycopg2://user:pw@host/db') hosted_fl.manager.truncate() hosted_fl.edit_features(adds=psql_df.spatial.to_featureset())

While @John_Spence's script is incredible, and I love a well-documented tool, I thought I'd elaborate on my my own process a bit, just in case you wanted to work outside of the Pro environment.

This sort of script will run anywhere so long as the required modules are in the python env, which can be nice if you have, say, a Linux server, or just don't need Pro on your DB machine.

I forgot to ask, but is the layer spatial or non-spatial? Pandas might not pick up a geometry column, but GeoPandas can. It only adds a couple steps, but preserves the geometry.

from arcgis import GIS, GeoAccessor import geopandas from sqlalchemy import create_engine gis = GIS('your-org-url', 'user', 'password') hosted_fl = gis.content.get('hosted-layer-itemid').layers[0] con = create_engine('postgresql+psycopg2://user:pw@host/db') gdf = geopandas.GeoDataFrame.from_postgis('your-query', con, 'geometry-column-name') sdf = GeoAccessor.from_geodataframe(gdf) hosted_fl.manager.truncate() hosted_fl.edit_features(adds=sdf.spatial.to_featureset())

Re: Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 14:55:20 GMT

Great explanation, this seems pretty feasible although I'm not sure how I feel about truncating! In any case, we could end up making changes to data on the hosted layer that we want to sync back to the posgres db so truncating/appending wouldn't be suitable and we'd need to compare for changes and sync the most recent ones. Could get complicated as you say.

When you say:

You'll also want to create the hosted layer first. You can do this by first publishing an extract from the database, or by creating an empty layer. As long as the schema is correct

Is there any simple way to do this so that we ensure the schemas match or is our only option to create the hosted feature layers manually (or with python)?

Re: Sync hosted feature services to postgres database

John_Spence — Thu, 17 Jun 2021 14:57:33 GMT

Someone had to go 🐼 here 😁! I am actually glad you did that because I usually end up working around it vs. leveraging the power of Pandas sadly. The only other option I can think of would be to drop the arcgis component entirely and just run pyodbc. Scrape and insert fantastic.

@jcarlson ...made my day! Hope you're having a good day out there.

Re: Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 14:59:13 GMT

I posted my initial reply before I seen your second post. Really helpful thanks, I'd like to work outside Pro so this is perfect. Yes, we would have some spatial data so if we can use geopandas that would be ideal.

This is a great starting point, thanks again!

Re: Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 15:03:36 GMT

Excellent! Between you and @jcarlson I've got several options! Will take me some time to get my head around it all!

Re: Sync hosted feature services to postgres database

jcarlson — Thu, 17 Jun 2021 15:08:37 GMT

On the first run, you can publish a spatial dataframe directly to a service. That would ensure that the schemas match, though the function likes to "sanitize" column names, i.e., SomeLayer becomes somelayer.

Moving edits back and forth certainly makes this trickier, and a no-go on simply truncating.

So, are you making edits on both "sides" of the hosted/db divide, and are looking to keep the latest changes in either space current with the other?

Once published, you can enable editor tracking on the hosted layer and query it based on the "last_modified" timetamp. Do you have a similar timestamp field in the postgres? I think that would probably be necessary to get this working in both directions.

Re: Sync hosted feature services to postgres database

lightpro — Thu, 17 Jun 2021 15:16:50 GMT

That looks perfect for matching the schemas 👍

I'm not 100% sure if we'll need to make edits on both sides but I think probably will. For example, we'll have a table for work orders and the thinking is to use this to generate jobs in Workforce or Survey123 so we'd need to be able to update the job status (e.g. in progress, complete etc.) and have that reflected back in the database.

Editor tracking is probably the way to go as you suggest.

Another possibility that was mentioned by our Dev was to use webhooks to keep everything synced and updated pretty much in realtime. Any thoughts on this as a solution?

Re: Sync hosted feature services to postgres database

John_Spence — Thu, 17 Jun 2021 15:27:23 GMT

Webhooks are a good way to trigger CRUD actions. GeoEvent Server, Notebooks, n8n.io, or Integromat for a few examples to make that happen with or without the use of ArcGIS Pro. n8n.io can be run on premise (low to no cost).