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]

Try a similar model as above--make sure to add your output to display.

[IanLouw]

Hi Douglas,

created a model as below -

while running am getting the below error - any guess? please !!

[Anonymous User]

Ian, it looks like it is the path you are trying to save to is causing the problem. Try to save it in an accessible folder or even better would be a file gdb.

Get Outlook for Android<https://aka.ms/ghei36>

[MichaelVolz]

What is the data source of your feature class (e.g. file gdb, SDE database, shapefile)?  If it's a file gdb, truncating and appending 7000 records every hour will probably slow down over time.  I have this scenario, so every so often I set the OBJECTID back to 1 by importing the feature class from an empty schema in another file gdb (The ArcGIS Server service would need to be temporarily stopped for this maintenance task).  You would only need to do this if you find the performance of the file gdb degrading badly over time which may not occur for you.

[IanLouw]

Its .gdb file only. And table data is from SQL DB. Can you please share me the Python code if you have this type scenario. One more thing is that I dont have ArcGIS server.

[MichaelVolz]

You can try a search of GeoNet to get python code for publishing up to AGOL.  Since the data is going to a PostGres DB on AGOL you don't need to worry about the update process slowing down over time.  That only applied to my scenario because I am using a file gdb registered with ArcGIS Server.