topic Re: Dynamic Map genration from SQL data for web application using ArcGIS Pro in ArcGIS Pro Questions

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

IanLouw — Wed, 13 Mar 2019 18:56:34 GMT

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!!

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

Anonymous User — Wed, 13 Mar 2019 19:33:00 GMT

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.

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

MichaelVolz — Wed, 13 Mar 2019 19:41:38 GMT

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

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

IanLouw — Wed, 13 Mar 2019 19:41:56 GMT

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

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

IanLouw — Wed, 13 Mar 2019 19:44:33 GMT

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.

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

MichaelVolz — Wed, 13 Mar 2019 19:56:43 GMT

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.

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

Anonymous User — Wed, 13 Mar 2019 20:04:33 GMT

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.

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

IanLouw — Wed, 13 Mar 2019 20:06:59 GMT

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!!

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

Anonymous User — Wed, 13 Mar 2019 20:16:02 GMT

Python for Everyone provides fundamentals on Python:

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

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

IanLouw — Wed, 13 Mar 2019 20:23:19 GMT

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

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

Anonymous User — Wed, 13 Mar 2019 20:48:53 GMT

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

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

MichaelVolz — Wed, 13 Mar 2019 21:27:02 GMT

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.

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

IanLouw — Wed, 13 Mar 2019 21:54:58 GMT

Hi Douglas,

created a model as below -

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

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

IanLouw — Wed, 13 Mar 2019 21:56:37 GMT

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.

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

MichaelVolz — Wed, 13 Mar 2019 22:12:48 GMT

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.

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

Anonymous User — Wed, 13 Mar 2019 22:45:52 GMT

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>