Sync hosted feature services to postgres database

[lightpro]

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?

[lightpro]

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!

[lightpro]

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)?

[jcarlson]

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.

[lightpro]

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?

[John_Spence]

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).