Summarize Meters in District

[AprilChipman]

I'm trying to create a WebApp to help my Board of Directors create new Board Districts. They want to be able to draw a new boundary on a map and see the summary of meters in that area. They would also like to keep that new district as a feature in a layer until they have come up with an agreed upon district boundary.

I've built a webapp with a layer that has every section and then a field of the total number of meters in each section. I then have a different, editable, layer that can be used to draw the new boundaries and has an attribute column for the total number of meters.

I've tried the following widgets: Screening, Analysis, Select, and Summary with no luck. I'm not sure if I'm just not configuring one of them correctly or if the widget won't do what I'm hoping it will.

I've also built an Operations Dashboard and tried to create Indicator widgets that will total the number of meters in each feature using a spatial feature, but that isn't quite working out either.

Does anyone have any suggestions for me?

[TanuHoque]

April Chipman‌

I have few questions for you:

1. Is your service a hosted feature service in arcgis online or is it running as a map/feature service in an on-premises ArcGIS Enterprise setup?
   If it is the later, then there are follow up question
2. are your data in an enterprise database such as SQL server, Oracle etc.?
3. meters are in a point dataset, right?
4. how many meters you have?
5. how many boundaries you'd anticipate your board of directors will create? just a ball park figure will do -- in magnitude of 10s, 100s, 1000s or more?

Thanks.

[AprilChipman]

The service is from a Map Image Layer shared from ArcGIS Server 10.3.1.

I have the data both as a point layer and as a polygon layer of each section with a field for the number of members in each section.

There are approximately 22,000 meters to be divided up into 7 districts, but each Board member has to live in their district so I can't automatically divide the meters evenly. They also do not want to split a section between districts.

[TanuHoque]

Thanks.

Is your data stored in a file gdb or an enterprise db? if it is file gdb, is possibility you can move that to an enterprise db? If it is already in an enterprise db, what flavor - SQL Server, Oracle, PostgreSQL?

[AprilChipman]

The data is in a SQL Server 10.1 SDE geodatabase. 

(I'm trying to install ArcGIS Enterprise 10.6.1 on a new server, but it's not going well.)

[TanuHoque]

Thanks.

Let's try the following approach to see whether it will help you:

If you had moved to 10.6.1, I'd have asked you to use ArcGIS Pro. Let's do this in ArcMap

• Open the Create New Query Layer
  help topic: Connecting to a database from the query layer interface in ArcMap—Help | ArcGIS for Desktop 
• Use the following SQL statement (of course you need to update table names) as its source Query
  

  SELECT db.*, r.Total FROM Districts db
  INNER JOIN
  (SELECT d.OBJECTID, Count(*) AS Total FROM Meters m
  INNER JOIN
  Districts d
  ON d.shape.STIntersects(m.shape) = 1
  GROUP BY d.OBJECTID) r
  ON db.OBJECTID = r.OBJECTID

• Follow the wizard to complete the process
• Now if you open the layer's attribute tables you will see an additional column showing total number of point features fell in each polygon
• You can use the new field i.e. Total to symbolize or label polygons

Since the computation happens dynamically for every zoom and pan, it should work with new boundaries.

And because of that it might be a bit slow to draw.

Please give this a try and check whether it works for your workflow from both functional and performance side.

[AprilChipman]

Thanks, Tanu!  I was able to use the tool, validate the SQL, and create a new layer. When I click Start Editing, I get an error that there are no editable layers, and when I try to open the attribute table I get the error below:

I have ArcPro 2.4.1 and am running ArcGIS Desktop 10.6.1 and have established a 10.6 enterprise geodatabase on the new server, but I'm having problems with the security certificate so can't get to Portal still. I can try running this tool through that database connection, but the other feature classes are still on the old ArcGIS and SQL servers. I'm not sure if that will help?

**Edit:

I imported my district polygon layer and my meter point layer into the new 10.6 enterprise geodatabase. Ran the tool in ArcDesktop 10.6.1 and successfully created a new layer. I am still unable to edit it and still get the same error when I try to look at the attribute table.

[TanuHoque]

Urgh... totally forgot (a) you can't perform edits on features off a query layer in ArcGIS Pro or ArcMap and (b) feature service does allow editing query layers but only when the source sql query is simple e.g. SELECT * FROM aTABLE.

with that, I can think of 2 options:

1. If you can move to ArcGIS Enterprise 10.7, then you can use Attribute Rules, that will allow you to perform the spatial count and store that value in a field for inserts and updates.
   • here is an arcade expression that I used to for this purpose
     

     var fsCities = FeatureSetByName($datastore, 'mysde.DBO.Cities', ['objectid'], true);
     return Count(Intersects(fsCities, $feature))

   • As you see in the following screenshot, when i created a new feature, it computes how many points it has within that and updates its attribute with that value (as you see that in the Attribute Editor as well as in the label). The update gets kicked in too as you modify the feature's geometry.
     
   • BTW, you should be able to publish to your standalone server without portal from Pro 2.4.
2. A not-so-good workaround:
   • Publish a map service (aka map image layer) with the query layer that I described before
   • Publish a feature service (map service with feature access enabled) with the district boundary layer (this is not the query layer) may be with no-fill symbols.
   • Add both of them in your web map
   • Edit the feature layer to create a new polygon
   • Refresh the map, the map image layer will be updated with count and new polygon