Dynamic Map genration from SQL data for web application using ArcGIS Pro

[IanLouw]

Hi All..

I am very new to GIS in terms of dynamic map generation. I have only option to use ArcGIS Pro and SQL server. I have a situation like below and need a guidance urgently. Please please please help!!!

I have one poly line layer with columns - ID, Name, Length & Diameter in ArcGIS Pro. I also have SQL database tables for same layer with columns - ID and Alert (value : 0 - 5). Now I have to generate a Map having alert value in it and based on alert value I need to symbolize the lines either in RED (0 - 2), Green (3 - 4), Amber = 5. After that I have to embed that map in one dot net web application. The SQL table alert values will get chnage in every one hour and based on new data available for each poly line in SQL table, my published embedded web map should reflect the same.

What I have done so far:

1. Open the layer in ArcGIS.

2. Create a database connection to my SQL DB and bring the table as a standalone table in content panel of ArcGIS Pro.

3. Made a Join between feature layer and standalone table based on ID to get alert value added in layer attribute.
4. Try to share that map as web map from ArcGIS pro sharing option. This gives me an error like "In-memory join can not be published".

My plan and thought was like above way I will publish the map with a;alert value in ArcGIS online and from there will open it in map viewer and then will take the embedded HTML code by clicking on share and use that HTML in web application placeholder.

But now I am stuck in publishing the map as web map due to having in memory join between feature layer and SQL table. And also I am doubting that after this will I be able to get my embedded map refreshed when SQL data changes as it is already published.

So, first of all I need help on how to publish the map with SQL data (here alert for lines) by overcoming in memory join.
And then need a guidance if at all with this approach my published map will get refreshed in web application when there is a chnage in SQL alert value. If not then how to achieve this as that is the utmost goal.

I am eagerly waiting for some help here to survive..

Thank You!!

[Anonymous User]

For resolution, right click on your layer with the joined data->then go to data-> the copy features pane opens->name the output-> and then click Run to add the new layer to your map. Remove the other layers used to create your join and then you will be able to publish.

[MichaelVolz]

Is this solution a snapshot of the data from SQL database table so this process would need to be continually re-run?

[IanLouw]

Hi Michael, yes it should run continuously. Whenever the map is opened from web page (where it is embedded), it should look into SQL data and based on that modify the map with colour range symbology.

[MichaelVolz]

My suggestion would be to script out this process in python where a feature class registered with ArcGIS Server would have its contents truncated (records deleted) and then new records would be appended from an in-memory join between the original feature class joined to the SQL database table.

Hopefully you have access to ArcMap where you can create a model to perform this process and then export out to python in 2.7 format.  From there you would just need to make some minor changes to make the python 3.x compliant for working with Pro (ESRI is working on being able to export a model from Pro, but it is not there yet - It had been removed, but enduser clamoring has gotten ESRI to say that it will be put back into Pro at a later version).

How many records are in the feature class?  I ask because I found that over time the truncate and append process slows down.  As you get more python experience you can try an update cursor instead of a truncate and append process which might solve the problem of the update slowing down over time.

[IanLouw]

Hi Michael, I have around 7000 lines in my layer. It will be great help if you can give me some useful links where it shows how to do this type of task using Python so that I can follow that and build my module as new to Python too. My bad!!

[Anonymous User]

Python for Everyone provides fundamentals on Python:

https://www.esri.com/training/catalog/57630436851d31e02a43f13c/python-for-everyone/ 

[IanLouw]

@Douglas, thanks for your suggestions. With that approach I can make it. But then my next question can you please help -

If I will create a copy from the joined data layer then next time when SQL data will get refresh with new value, will those updated value come to this new output? As I need to update my layer in real time under web application in sync with SQL data.

[Anonymous User]

Within ArcGIS Pro, another suggestion is to use Model Builder since your example is a continuous workflow.  Your input layer will be a parameter since it will be getting updated--the model will need to be executed after every update.  As suggested by Ian Louw‌, Python is also a option depending on size of data and update frequency.

[IanLouw]

@Douglas, can you please elaborate these steps..What I understood is using Model Builder of ArcGIS pro I should create a model by taking my layer as input parameter. Which layer should I take here? Feature layer or the joined layer (Feature + SQL). Once Model will be ready how I will call that to be executed automatically..