Query BrainStorm

[JayKappy]

I have a dataset with Incidents....Multiple Individuals with various incidents
I can take a dataset and return the results to a listbox.  Great.
But I want to make this a bit more together.

Just looking for soem ideas and maybe a push in the right direction...

I would like to run a query that Grabs the unique individuals and then calculates on that, and then return each individuals stats...

I might have 30 individuals and 5-10 incidents...but might only report total incidents for each person....there is also a chance that not all 30 individuals will have an incident in the dataset...especially if its a dataset for specific time range

John F
   Accidents: 20
   Fires: 34
Amy
   Accidents: 20
   Fires: 34
etc

I can run this query which returns all the resords and fields...I assume that I would have to create a couple temporary place holders and then create a coupld functions to calculate the values?
I am just unsure how to tackle this...or where to start...
I assume that I need all the records as stated below with the (Outfields.Add"*")

        Dim queryPolice As New ESRI.ArcGIS.Client.Tasks.Query()
        queryPolice.OutFields.Add("*")
        queryPolice.ReturnGeometry = True
        ' Return all features
        queryPolice.Where = "1=1"
        queryPolice.OutSpatialReference = MyMap.SpatialReference
        queryTaskPolice.ExecuteAsync(queryPolice)

Maybe in here set a few variables and then count and add to variables each loop...
Dim JohnAccident....If John and Accident then set varaible JohnAccident = + 1
The thing is I will have a bunch of individuals and a bunch of incident types....any way to automate this with code instead of hardcoding everything?

Dim variables, varaibles

        For Each selectedGraphic As Graphic In args.FeatureSet.Features

              If then
                     calculate variables 
 
                     set to placeholders that will report in listbox
              End If

        Next selectedGraphic

If any of that made sense....Just looking for some ideas

[JenniferNery]

Do you have people and incidents in separate tables? I think you are talking about related records so I'll point you to try to understand this sample and see if it fits your needs: http://help.arcgis.com/en/webapi/silverlight/samples/start.htm#QueryRelatedRecords

If you have people and incidents in the same service, you can perform aggregate linq query on the results, similar to http://msdn.microsoft.com/en-us/vbasic/bb737904#sumgroup. You can first group by person's name and then get the sum of their incidents.

[JayKappy]

Thanks....I am trrying these different angles and getting errors on the (Group p BY)

Officer and Incident are String fields in the data....
OfficersName and TotalIncidents are just text that is going to be displayed?????

Confused because from what I understand with this Linq is that I will get the Officer and then a talley of the number of incidents/records for each in the table. Its not going to further break down the Incidents field right? (incidents field values, Crime, Accident, Burglary etc)

Group.Sum(Function(p) p.Incident)...Is this calling a Function?


Error: Range variable Name can be referred only from a simple or qualified name with no arguments.

        Dim categories0 = From p In args.FeatureSet.Features Group p By CStr(p.Attributes("Officer")) Into Group Select OfficersName, TotalIncidents = Group.Sum(Function(p) p.Incident)
        Dim categories1 = From p In args.FeatureSet.Features Group p By (p.Attributes("Officer")) Into Group Select OfficersName, TotalIncidents= Group.Sum(Function(p) p.Incident)
        Dim categories2 = From p In args.FeatureSet.Features Group p By (p.Attributes("Officer")) Into Group Select OfficersName, TotalIncidents= Group.Sum(Function(p) p.Incident)

Error: 'Officer' is not a member of ESRI.ArcGIS.Client.Graphic

Dim categories2 = From p In args.FeatureSet.Features Group p By p.Officer Into Group Select OfficersName, TotalIncidents= Group.Sum(Function(p) p.Incident)

        Dim queryPolice As New ESRI.ArcGIS.Client.Tasks.Query()
        queryPolice.OutFields.Add("Officer, Incident")
        queryPolice.ReturnGeometry = True
        ' Return all features
        queryPolice.Where = "1=1"
        queryPolice.OutSpatialReference = MyMap.SpatialReference
        queryTaskPolice.ExecuteAsync(queryPolice)

[JenniferNery]

I see, I thought Incidents is a number.

Group.Sum(Function(p) p.Incident)...Is this calling a Function?

If Incidents was a number, you can use anonymous function to retrieve this property and get its total.
Group.Sum(Function(p) CInt(p.Attributes["Incident"]))

You can OrderBy Officer and iterate through the sorted results while incrementing a count for each officer.

But the best thing to do is learn and explore how to use Linq before you can decide which operation best fits your need because I can advise you something that only make sense for me but may not really work with your data 😛

[JayKappy]

I apprecaite your thoughts...I was confused there and did mention that I didnt know which way to go...
1. I had an idea that I might have to itterate through the records to calculate the number if incidents per officer...
2. I had a feeling that I could peice together a query to do a count as you mentioned...

now I know that I can do both abiet seperate from one another....

I changed the Linq to a Integer field Incident_num so I can at least learn this part of it...I will worry about adding incidents per officer later....

I am trying to peice together the GROUP BY and GROUP SELECT but having syntax issues...when I run with what you mentioned I get errors around the CInt(p.Attributes["Incident"]))
Thinkign it should be this instead

"[Incident]" 

My failed attempts....I changed the [""] to "[]" although now I am gettign an error somewhere else...

IncidentNum IS and Integer

1. When I have ("[Incident_Num]")))  --- with the 3 ))) --- it errors out on the (p.Attributes("[Officer]"))

        Dim categories1 = From p In args.FeatureSet.Features Group p By (p.Attributes("[Officer]")) Into Group Select OfficerName, TotalIncidents = Group.Sum(Function(p) CInt(p.Attributes("[Incident_Num]")))

2. If I remove the last ) it tells me its missing the )

Any thoughts?

[JenniferNery]

I tested the following code in C# and converted to VB. This demonstrates how you can take advantage of Linq Group, Sum and Lambda expressions or Anonymous functions.

Dim graphicCollection As New GraphicCollection()

Dim graphic As New Graphic()
graphic.Attributes("Officer") = "c"
graphic.Attributes("Incident") = 1
graphicCollection.Add(graphic)

graphic = New Graphic()
graphic.Attributes("Officer") = "b"
graphic.Attributes("Incident") = 1
graphicCollection.Add(graphic)

graphic = New Graphic()
graphic.Attributes("Officer") = "a"
graphic.Attributes("Incident") = 1
graphicCollection.Add(graphic)

graphic = New Graphic()
graphic.Attributes("Officer") = "c"
graphic.Attributes("Incident") = 1
graphicCollection.Add(graphic)

graphic = New Graphic()
graphic.Attributes("Officer") = "a"
graphic.Attributes("Incident") = 1
graphicCollection.Add(graphic)

Dim results = From g In graphicCollection _
Group g By DirectCast(g.Attributes("Officer"), String) Into r _
Select Officer = r.Key, Incidents = r.Sum(Function(i) CInt(i.Attributes("Incident"))) 

For Each item As var In results
 MessageBox.Show(String.Format("Officer {0} has {1}", item.Officer, item.Incidents))
Next

This will first create groups by Officer and then total the number of Incidents within each group. I suggest you play with very simple data first such as the one above to get a feel of which Linq operation (if any) would give you the desired result. If Incident becomes a string, then you can no longer use Sum.

[JayKappy]

I thank you for your thoughts....I am tryign to make sense of the Linq statement as it has changed dramatically in this post alone...
In your example you have a graphic collection being defined...thats great for example of showing how this works...
But I have a feature set defined from a query.

If I do this: " From g In args.FeatureSet.Features " I get the same errors again

Error 1 Range variable name can be inferred only from a simple or qualified name with no arguments.

Error is in BLUE below

    Private Sub QueryTaskPolice_ExecuteCompletedListBox(ByVal sender As Object, ByVal args As ESRI.ArcGIS.Client.Tasks.QueryEventArgs)

        Dim featureSet As FeatureSet = args.FeatureSet

        Dim results = From g In args.FeatureSet.Features Group g By DirectCast(g.Attributes("Officer"), String) Into r() Select Officer = r.Key, Incidents = r.Sum(Function(i) CInt(i.Attributes("Incident_Num")))

'.....SNIP

[JenniferNery]

Does the attribute "Officer" exist in your query OutFields? Can it be type casted to string?

The C# code I was using was:
(string) g.Attributes["Officer"]

I'm not sure if I wrote the correct VB syntax. My previous post is meant for demonstration purposes only. You are free to use, tweak or not use it. I don't know how else I could help. You know your data more, you just need to learn how to use Linq. Start small, do short and simpler samples with different attributes and types. Once you got your code working, tweak it for your query.

[JayKappy]

Does the attribute "Officer" exist in your query OutFields? Can it be type casted to string?

The C# code I was using was:
(string) g.Attributes["Officer"]

I'm not sure if I wrote the correct VB syntax. My previous post is meant for demonstration purposes only. You are free to use, tweak or not use it. I don't know how else I could help. You know your data more, you just need to learn how to use Linq. Start small, do short and simpler samples with different attributes and types. Once you got your code working, tweak it for your query.

Thanks for your thoughts...

I pasted your code in my app....
I have been successful in grabbing the attributes from the query and defining them in the listbox xaml and then binding them to the listbox....I dont think this works the same way...
Right now I am completly eliminating the query and simply using your example

        Dim graphicCollection As New GraphicCollection()

        Dim graphic As New Graphic()
        graphic.Attributes("Officer") = "c"
        graphic.Attributes("Incident") = 1
        graphicCollection.Add(graphic)
'snip
        Dim varresults = From g In graphicCollection Group g By DirectCast(g.Attributes("Officer"), String) Into r() Select Officer = r.Key, Incidents = r.Sum(Function(i) CInt(i.Attributes("Incident")))

        MessageBox.Show(String.Format("Officer {0} has {1}", item.Officer, item.Incidents))

My query is giving 3 fields right now, but with your example I dont even need the Fields because I am setting and defining my own graphicCollection

        Dim queryPolice As New ESRI.ArcGIS.Client.Tasks.Query()
        queryPolice.OutFields.Add("Officer, Incident, Incident_Num")
        queryPolice.ReturnGeometry = True
        ' Return all features
        queryPolice.Where = "1=1"
        queryPolice.OutSpatialReference = MyMap.SpatialReference
        queryTaskPolice.ExecuteAsync(queryPolice)

Maybe the syntax is wrong...will have to look further...

I cant get past the  DirectCast(g.Attributes("Officer"), String)
Error 1 Range variable name can be inferred only from a simple or qualified name with no arguments.
That and item.officer and item.Incidents is erroring out as well....item is not declared
I understand that your are very limited on your side....and appreciate everything you are commenting on....
I dont know how to determine if the Officer can be type cast to string....its already a string...dont even know what that means...

Just trying to get the messagebox to show the results of the simple query you posted...

I thank you for your thoughts and time....I think this example you gave is rather simplistic as I am takign the query out of the equation...I guess there is something wrong with the DirectCast statement in VB?????? hmmmmm

Thanks again....

[JenniferNery]

Copy and paste the code snippet in Post# 6 to a new application. I don't think you copied it correctly.

The linq query does not have "Into r()", but "Into r" and the MessageBox.Show() is inside a For Each.

I think the range variable name error might be caused by "r()".