Latest Contributions by AndrewStickney1

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