Latest Contributions by AndrewStickney1

@OwenGeo here are a couple of examples where the workflow I sketched out could be used. Use Case #1 - Missoula County Land Stewardship Award story map This is the updated story map that inspired this idea: https://storymaps.arcgis.com/collections/10728d976f504c68967a6e2031e869f3 Although manually built since this was my first time using the new story map builder and Map Tour block, I would love to migrate this to a data-driven version with the same photo attribution and alternate text options that are exhibited in the manually built version.  As you can see, the award covers multiple themes and chronogical years. Being able to use a single hosted feature service with multiple "feature views" filtered by theme or year would be very helpful in reducing the number of edits or maintenance tasks...while avoiding the complexity of doing something like this in WebApp Builder or the Experience Builder.      Use Case #2: - Missoula County Grants and Stewardship Award Story Map This classic Shortlist story map is created each year to highlight the work of our Grants and Community program. Here's the most recent version: https://gis.missoulacounty.us/caps/grantsstorymapfy20/ This story map has corrupted in the past and required a complete rebuild by both staff and myself. If I could migrate this to a data-driven map tour with hosted feature service, we could use the "collections" feature with hosted feature views to provide access to each year's iteration, similar to how I did in use case #1 above.  ... View more

@OwenGeo, thanks for asking your question. The simple answer is: automating workflows and story map maintenance.  The classic Shortlist template is very popular among the staff in my department for its ability to display a lot of data in an easy-to-read and navigate format for the public to consume. When I am able to get staff involved in creating or maintaining these story maps, they are easy to maintain, but they have a reputation for getting corrupted, which requires a complete rebuild, since the hosted feature service is maintained automatically by ArcGIS online. I have one or two of them that are data-driven and hosted locally by our GIS manager, but the maintenance of those maps then falls on me, adding to my to-do list. My goal is to always automate my workflows when possible to reduce the number of hours and steps required to maintain existing projects, leaving more time to focus on other projects.  My thought is that I can actually democratize GIS and data maintenance more by using the automated approach to the Map Tour story map when it's appropriate. As you mentioned, the data-driven Map Tour was designed to connect to a feature service generated by other apps, such as Survey123. I got excited when I saw this help document on using Survey123 with existing feature services.  Here is my sketch of the proposed workflow: 1) I migrate an existing shortlist to the new data-driven Map Tour experience by publishing an editable feature service. I already have a Python Notebook to help me semi-automate that process.  2) I develop a Survey123 form to append new entries to the existing feature service that powers the map tour story map. 3) Staff enter their own updates using Survey123 to the story map without needed to know anything about GIS and I no longer have to perform those updates myself.  Let me know if this helps clarify my goal, @OwenGeo .   ... View more

Hi Leandra, Answer Summary You can either do this by developing a custom script tool that checks the layer for you and then returns an output that you can evaluate....or by using the Calculate Value tool with a custom Python function and then check the output value of that tool with the If Value Is tool to create the split branch you had in your model above:   Configuring the Calculate Values tool Here's how to configure the Calculate Values tool. Expression Field:    CheckLayer("%Test Layer%")   Note that the "Test layer" is referring to my layer variable in my model that I am checking for existence. You would need to change in your model to:   CheckLayer("%UGB_Layer%")   Code Block field:   def CheckLayer(layer): try: import arcpy # current ArcGIS Pro project aprx = arcpy.mp.ArcGISProject("CURRENT") # current open map map = aprx.activeMap #check to see if the layer is listed in map if layer in map.listLayers(layer): return "PRESENT" else: return "NOTPRESENT" except: return "NOTPRESENT"   Make sure to set the Data type to String   Configuring the If Value Is tool Set the output of the Calculate Value to be the input Value to check. Keep the default value for the Value Test "Is equal to at least one value" Set the Comparison Type to "Case Insensitive string" Values - you only need to type in PRESENT. This is important because you only want to test for the "True" branch condition. Any other value (i.e. NOTPRESENT) would trigger the false branch.   This should do what you need. ... View more

Jacob, Was your code sample suggesting a solution along the lines of what is in this blog entry? Integrate big data with the ArcGIS System using a plug-in data source (MongoDB)? ... View more

Shannon, I think talking with an Esri consultant would be a wise move particularly dealing with the server/publishing side of data access. However, I can share a little bit of my County's ideas regarding Cloud vs. file server storage.  I work for local government and my department has been asked to move all of our documents to SharePoint/OneDrive storage. As the GIS lead for my department, here is what we've sketched out as a plan for the GIS-specific files.  Use SharePoint/OneDrive for Output PDFs or Microsoft Office documents (i.e. analysis results/reports/documentation) Use SharePoint to store published/finished map or output products, especially if stored as Microsoft Office document formats. This can include copies of any metadata for GIS datasets exported from the XML version that accompanies the documents. That is where cloud-storage makes sense - SharePoint or OneDrive make it easy to share/edit/comment on one copy of a document rather than emailing multiple copies.  Use file geodatabases on a file server or ArcGIS Enterprise for editing source GIS datasets For editing data...depending on the number of edits and whether they are concurrent or separate, we use ArcGIS enterprise/server for core data (i.e. the important datasets that everyone uses for daily operations). The advantage of using Enterprise is its support for versioned data (makes it much easier to restore a backup in case of a GIS software crash or editing error/mistake operation and concurrent editing. However, you have to have a staff person to support the administration of that server, so some organizations prefer to just use file geodatabases for data editing.  Use ArcGIS Online/ArcGIS Enterprise (Portal) or file geodatabases on a network file server to share reference data For storing source GIS datasets for reference (i.e. the stuff you view but don't regularly edit), you can either use dedicated file servers/network drives with file geodatabases or ArcGIS Online/ArcGIS Enterprise (Portal). We have started seeing the advantage of ArcGIS Online and Portal for sharing data and maps internally - it functions/benefits in much the same way for GIS data that cloud-storage works for collaborative sharing of Office documents. If you have a series of standard layers that everyone always uses, you can store those in read-only file geodatabases with layers or map templates that point to those data sources...or you can publish them to ArcGIS Online (even for internal use) and then people can add them to ArcGIS. Admittedly, this is much easier in ArcGIS Pro than in ArcMap/ArcGIS Desktop, but it does have its advantages.  These are just some thoughts I have had based on my experience migrating from the file server/disk-based world to cloud-base storage. Hope it gives you some food for thought as you reach out to Esri or talk to other consultants.  ... View more

Marco Boeringa, A colleague of mine identified the same problem with the Join condition (adding the tax year) that you did. This query as finally written works when implemented as a database view (on the database side). I did test the SQL query you posted in ArcGIS Pro 2.1 and it still produced the same error message of invalid syntax near WITH.  I'm still not sure why it doesn't pass validation in ArcGIS Pro - maybe it doesn't support WITH statements, even though the syntax is valid for SQL Server.  I appreciate you sending me in the right direction with the "WITH" statement.  That was very helpful and helped me learn something new. I haven't written straight SQL statements for 5-10 years and wasn't aware of some of the new keywords and syntax available.  I plan on upgrading to ArcGIS Pro 2.2 in the next few weeks. I'll try it with that version and see if the Make Query Layer works for that.  ... View more

Marco Boeringa, A colleague of mine identified the same problem with the Join condition (adding the tax year) that you did. This query as finally written works when implemented as a database view (on the database side). I did test the SQL query you posted in ArcGIS Pro 2.1 and it still produced the same error message of invalid syntax near WITH.  I'm still not sure why it doesn't pass validation in ArcGIS Pro - maybe it doesn't support WITH statements, even though the syntax is valid for SQL Server.  I appreciate you sending me in the right direction with the "WITH" statement.  That was very helpful and helped me learn something new. I haven't written straight SQL statements for 5-10 years and wasn't aware of some of the new keywords and syntax available.  I plan on upgrading to ArcGIS Pro 2.2 in the next few weeks. I'll try it with that version and see if the Make Query Layer works for that.  ... View more

Marco Boeringa‌, it took me quite a while to understand how to use the WITH statement, but I think I have figured it out. I  use the site SQL Fiddle to test and refine the SQL statements I needed, and here is the final code. WITH ResQuery As ( SELECT   Res.PropertyID as ResPID,   Count(Res.PropertyID) as ResCount FROM   Res WHERE Res.TaxYear = 2017 GROUP BY Res.PropertyID   ), ComQuery As ( SELECT   Com.PropertyID as ComPID,   Count(Com.PropertyID) as ComCount FROM   Com WHERE Com.TaxYear = 2017 GROUP BY Com.PropertyID   ), PropQuery As ( SELECT   Property.PropertyID as PropPID,   Property.Geocode as GCode,   Property.GeocodeSearch as GCodeSearch,   Property.TaxYear as Tax FROM   Property WHERE Property.TaxYear = 2017   ), ComboQuery as (   SELECT P.PropPID as PID, P.GCodeSearch as Code, Count(R.PropertyID) as RCount, Count(C.PropertyID) as CCount   FROM PropQuery as P LEFT JOIN Res as R ON P.PropPID = R.PropertyID LEFT JOIN Com as C ON P.PropPID = C.PropertyID   WHERE P.Tax = 2017   GROUP BY P.PropPID, P.GCodeSearch ) SELECT * From ComboQuery ;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ A direct link to the SQL Fiddle with my query and test data is here: SQL Fiddle  Unfortunately, I received an error message when copying and pasting the code into ArcGIS Pro's  Make Query Layer tool. The error message is: 000358: Invalid expression.—Help | ArcGIS Desktop  The specific underlying client error is: 'Microsoft SQL Server Native Client 11.0 - Incorrect syntax near the keyword 'WITH'] as shown in the screenshot below. Have you encountered errors when using the WITH clause in ArcGIS before? The only thing I have heard on forums is adding a ";" before the WITH clause, but otherwise, I am stumped.  Any suggestions? ... View more

Marco Boeringa‌, it took me quite a while to understand how to use the WITH statement, but I think I have figured it out. I  use the site SQL Fiddle to test and refine the SQL statements I needed, and here is the final code. WITH ResQuery As ( SELECT   Res.PropertyID as ResPID,   Count(Res.PropertyID) as ResCount FROM   Res WHERE Res.TaxYear = 2017 GROUP BY Res.PropertyID   ), ComQuery As ( SELECT   Com.PropertyID as ComPID,   Count(Com.PropertyID) as ComCount FROM   Com WHERE Com.TaxYear = 2017 GROUP BY Com.PropertyID   ), PropQuery As ( SELECT   Property.PropertyID as PropPID,   Property.Geocode as GCode,   Property.GeocodeSearch as GCodeSearch,   Property.TaxYear as Tax FROM   Property WHERE Property.TaxYear = 2017   ), ComboQuery as (   SELECT P.PropPID as PID, P.GCodeSearch as Code, Count(R.PropertyID) as RCount, Count(C.PropertyID) as CCount   FROM PropQuery as P LEFT JOIN Res as R ON P.PropPID = R.PropertyID LEFT JOIN Com as C ON P.PropPID = C.PropertyID   WHERE P.Tax = 2017   GROUP BY P.PropPID, P.GCodeSearch ) SELECT * From ComboQuery ;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ A direct link to the SQL Fiddle with my query and test data is here: SQL Fiddle  Unfortunately, I received an error message when copying and pasting the code into ArcGIS Pro's  Make Query Layer tool. The error message is: 000358: Invalid expression.—Help | ArcGIS Desktop  The specific underlying client error is: 'Microsoft SQL Server Native Client 11.0 - Incorrect syntax near the keyword 'WITH'] as shown in the screenshot below. Have you encountered errors when using the WITH clause in ArcGIS before? The only thing I have heard on forums is adding a ";" before the WITH clause, but otherwise, I am stumped.  Any suggestions? ... View more

Joshua Bixby‌, Would you be able to post an example of the SQL statement with the COUNT that worked in ArcGIS Pro (or send it to me privately)? The Esri support person who I contacted about this previously said that it could not be done in Pro (at 2.1). It would be helpful to see what you did and see whether I misunderstood the limitatons (or misread the documentation).  Thank you. ... View more

Joshua Bixby‌, Would you be able to post an example of the SQL statement with the COUNT that worked in ArcGIS Pro (or send it to me privately)? The Esri support person who I contacted about this previously said that it could not be done in Pro (at 2.1). It would be helpful to see what you did and see whether I misunderstood the limitatons (or misread the documentation).  Thank you. ... View more

Marco Boeringa‌, Joshua Bixby‌. I thought my original post was clear, but here's another example to illustrate the problem further.  If you followed the help links, you would realize that you cannot use an aggregate function (i.e. COUNT) in a primary query - like the following simple statement that counts the number of PropertyIDs in the Res associated with a geocode uniqueID and the PropertyID Join field.  SELECT Property.Geocode, Property.PropertyID, Property.TaxYear, Res.PropertyID, Count(Res.PropertyID) as IDCount FROM Property, Res LEFT JOIN Property.PropertyID = Res.PropertyID WHERE Property.TaxYear = 2017 GROUP BY Property.Geocode ;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Instead, you can only use aggregate functions within a subquery, which would look something like this.  SELECT Property.Geocode, Property.PropertyID, Property.TaxYear WHERE Property.TaxYear = 2017 AND Property.PropertyID IN      (       SELECT Res.PropertyID, Count(RES.PropertyID) as IDCount FROM PropertyID       WHERE IDCount > 0       ); ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ The problem with the second example is: 1) I don't have access to the original count of PropertyIDs...I only know which PropertyIDs have a match to the subquery. Even if I used the ANY keyword to get all records, this is a loss of data for me and not helpful to my analysis. 2) To get close to my original goal of compiling counts (or just count matches) from both Res and Com tables, I would need to make two query table versions of the second SQL example and then a third query table or layer to merge the first two together into a single view.  I hope this explains the problem more clearly.  ... View more

Marco Boeringa‌, Joshua Bixby‌. I thought my original post was clear, but here's another example to illustrate the problem further.  If you followed the help links, you would realize that you cannot use an aggregate function (i.e. COUNT) in a primary query - like the following simple statement that counts the number of PropertyIDs in the Res associated with a geocode uniqueID and the PropertyID Join field.  SELECT Property.Geocode, Property.PropertyID, Property.TaxYear, Res.PropertyID, Count(Res.PropertyID) as IDCount FROM Property, Res LEFT JOIN Property.PropertyID = Res.PropertyID WHERE Property.TaxYear = 2017 GROUP BY Property.Geocode ;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Instead, you can only use aggregate functions within a subquery, which would look something like this.  SELECT Property.Geocode, Property.PropertyID, Property.TaxYear WHERE Property.TaxYear = 2017 AND Property.PropertyID IN      (       SELECT Res.PropertyID, Count(RES.PropertyID) as IDCount FROM PropertyID       WHERE IDCount > 0       ); ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ The problem with the second example is: 1) I don't have access to the original count of PropertyIDs...I only know which PropertyIDs have a match to the subquery. Even if I used the ANY keyword to get all records, this is a loss of data for me and not helpful to my analysis. 2) To get close to my original goal of compiling counts (or just count matches) from both Res and Com tables, I would need to make two query table versions of the second SQL example and then a third query table or layer to merge the first two together into a single view.  I hope this explains the problem more clearly.  ... View more