nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Vind meer berichten getagd met

Accepted answers

All comments

JayKappy

Yaaa Hoooo!!!!!
Jennifer and others....thank you very much for the start...it was a great help...I was able to get your example working and then modifed to return the results from my query...I created a little TOC on the left of my map that opens when the query is run and displays the relivant data. Thanks again for your help and patience....
If anyone has any further questions or wants more info on what I am doing please feel free to contact me...jaykappy@yahoo.com

I now look forward to tweakign the queries to retun other relivant data...

THANKS Jennifer...very apprecaited..

VB Code

    Private Sub ExecutePIDButton_Click(ByVal sender As Object, ByVal e As RoutedEventArgs)

        ' OPEN THE RESULTS PANEL TO DISPLY RESULTS
        ShowResultsPanel.Begin()

        Dim queryTaskPolice As New QueryTask("http://gis.somewhere.org/MapServer/0")
        AddHandler queryTaskPolice.ExecuteCompleted, AddressOf QueryTaskPolice_ExecuteCompletedListBox
        AddHandler queryTaskPolice.Failed, AddressOf QueryTaskPolice_FailedSearchListBox

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

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

        Dim featureSet As FeatureSet = args.FeatureSet

        ' WORK WITH THE RESULTS FROM THE QUERY FOR INCIDENTS
        ' Create a list by Incident and then talley the number of each
        Dim results = From g In featureSet
                        Group g By GroupKey = CStr(g.Attributes("Incident")) Into r = Group
                        Select New With {Key .Incident = GroupKey, Key .IncidentNums = r.Sum(Function(i) Convert.ToInt32(i.Attributes("IncidentNum")))}

        Me.Results.ItemsSource = results

    End Sub

                    <StackPanel x:Name="IdentifyResultsPanelBufferIncidents_Police" Height="300" Width="175" Orientation="Vertical" Margin="0,0,0,0" HorizontalAlignment="Center">
                        <Grid x:Name="resultsPanelBufferIncidents_Police"  Width="175"  Height="300"
                 HorizontalAlignment="Center" VerticalAlignment="Top"
                 Margin="0,0,0,0">
                            <StackPanel Orientation="Vertical">
                                <StackPanel>
                                    <Button Content="calculate total" VerticalAlignment="Top" HorizontalAlignment="Center" Click="Button_Click"/>
                                    <ListBox x:Name="Results">
                                        <ListBox.ItemTemplate>
                                            <DataTemplate>
                                                <Grid>
                                                    <Grid.ColumnDefinitions>
                                                        <ColumnDefinition Width="*"/>
                                                        <ColumnDefinition Width="*"/>
                                                        <ColumnDefinition Width="*"/>
                                                        <ColumnDefinition Width="*"/>
                                                    </Grid.ColumnDefinitions>
                                                    <TextBlock Text="{Binding Incident, StringFormat='Type: \{0\}'}" Grid.Column="0"/>
                                                    <TextBlock Text=" has " Foreground="Black" Grid.Column="1" />
                                                    <TextBlock Text="{Binding IncidentNums}" Grid.Column="2"/>
                                                    <TextBlock Text=" incident" Foreground="Black" Grid.Column="3"/>
                                            </Grid>
                                            </DataTemplate>
                                        </ListBox.ItemTemplate>
                                    </ListBox>
                                </StackPanel>
                            </StackPanel>
                        </Grid>
                    </StackPanel>

JenniferNery

Oh right I forgot you work in VB.
Add this line in your AssemblyInfo file

<Assembly: System.Runtime.CompilerServices.InternalsVisibleTo("System.Windows")>

JayKappy

Binding to anonymous types is supported in SL4 by adding this line in your AssemblyInfo.cs
[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("System.Windows")]
When you don't see elements in your ListBox, check Binding statements if they still apply to the data type of your ItemsSource.

I guess my next logical question is your last comment...

In VB????? Are you refering to an IMPORT in VB?
The closest think I could think of would be this: but I dont think that is right

Imports System.Runtime.CompilerServices.InternalsVisibleToAttribute

This is C+...dotn know where to put that in VB and syntax? I think you said that this is stoping the values from being displayed int eh listbox...

[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("System.Windows")]

THIS IS THE CODE I HAVE SO FAR.....still not working, I am hoping it is due to your last comment....Additionally I think there were a few issues with the code in the example you gave me....I think the column definitions were wrong...and yoru colegues sysntax was different than yours....so I tried to peice it all together....I think what I have is right minus yoru last comment which again has confused me....

        
        Dim results = From g In graphicCollection
              Group g By GroupKey = CStr(g.Attributes("Officer")) Into r = Group
              Select New With {Key .Officer = GroupKey, Key .Incidents = r.Sum(Function(i) Convert.ToInt32(i.Attributes("Incident")))}

        Me.ListboxResults.ItemsSource = results

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

                    <Button Content="calculate total" VerticalAlignment="Top" HorizontalAlignment="Center" Click="Button_Click"/>
                    <ListBox x:Name="ListboxResults">
                        <ListBox.ItemTemplate>
                            <DataTemplate>
                                <Grid>
                                    <Grid.ColumnDefinitions>
                                        <ColumnDefinition/>
                                        <ColumnDefinition Width="50"/>
                                        <ColumnDefinition Width="*"/>
                                        <ColumnDefinition/>
                                    </Grid.ColumnDefinitions>
                                    <TextBlock Text="{Binding Officer}" Grid.Column="0"/>
                                    <TextBlock Text="{Binding Incident}" Grid.Column="1"/>
                                </Grid>
                            </DataTemplate>
                        </ListBox.ItemTemplate>
                    </ListBox>

I really appreciate your help here....I think If I can get this working I will have a strogn base to work from....and make things more complex

THANK YOU

JenniferNery

Why would you set ListBox ItemSource inside the ForEach? The ForEach is meant to iterate through the results. results already contains the grouped graphic collection. The following line can be done before or after the ForEach but not inside it.

Results.ItemsSource = results

Also if you are updating the ItemsSource with different type of elements, you need to update your Binding statement in XAML as the following no longer apply after you set ItemsSource to results.

<TextBlock Text="{Binding Attributes[Officer]}"/>
<TextBlock Text="{Binding Attributes[Incident]}" Grid.Column="2"/>

Previously when it was set to graphicCollection, the contents are graphics with Attributes that has key Officer and Incidents. 'results' on the other hand, is formed by an Anonymous Type Select, with new properties Officer and Incidents.

<TextBlock Text="{Binding Officer}"/>
<TextBlock Text="{Binding Incidents}" Grid.Column="2"/>

Binding to anonymous types is supported in SL4 by adding this line in your AssemblyInfo.cs

[assembly: System.Runtime.CompilerServices.InternalsVisibleTo("System.Windows")]

When you don't see elements in your ListBox, check Binding statements if they still apply to the data type of your ItemsSource.

JayKappy

one LAST QUESTION REGARDING THIS I SWEAR

1. Message box works great with only 3 results returned adn the incidents calculated properly
2. Returning the results to the Listbox...while this works it returns all 5 records set up in the graphic collection. Where as the message box does the grouping...
I cant seem to ge the grouping and calculation to return to the listbox...

This returns all 5
Me.Results.ItemsSource = graphicCollection

This does not return anything.

        For Each item In results
            MessageBox.Show(String.Format("Officer {0} has {1}", item.Officer, item.Incidents))
            ' TRY 1            
            Me.Results.ItemsSource = (String.Format("Officer {0} has {1}", item.Officer, item.Incidents))
            ' TRY 2            
            Me.Results.ItemsSource = results
        Next item

Thanks

JayKappy

Thank you very much, and please pass on my thanks to your colleague. This works and gives me a really good base to start with....now its a bunch of tinkering to get more complex queries to work and to use returned query values.....

THANKS again and have a great weekend....Thanks

JenniferNery

I asked a colleague from our team who is more fluent in VB than I am and he converted the C# sample to VB. Kindly see attached. It is just a short sample that demonstrates how you can use Linq for the use case that you described.

linqOnQueryResultTest_VB.zip

JayKappy

First off I thank you for trying...and understand
I would love to learn this...but I have scoured web sites and they show examples that seem to query SQL databases etc....what I am seeing from your examples are complety different...
Even the simple examples I am shown are not working....I dont know what else to do when I am looking at a very basic example and I am gettign errors....
I cant find anything out there that will help with the syntax...

But I do thank you for trying...anyone out there that can see why the DirectCast is not working I would be appreciative....I am told this works in C+...just need to get this to work in VB...then I can start tweaking to make more complex queries...but if I cant get a simple one working I dont know where to start...

THanks again

JenniferNery

Oh okay, I think you need to follow the syntax in this sample:http://msdn.microsoft.com/en-us/vbasic/bb737904#sumgroup

Dim categories = From p In products _
Group p By p.Category Into Group _
Select Category, TotalUnitsInStock = Group.Sum(Function(p) p.UnitsInStock)

and Select - Anonymous Type 2 in this sample: http://msdn.microsoft.com/en-us/vbasic/bb737936

Dim q = From e In db.Employees _
Select Name = e.FirstName & " " & e.LastName, Phone = e.HomePhone

I code in C# so my conversions to VB could be wrong. I suggest you read up on LINQ for VB. It will be the best way to formulate your own query in the correct syntax too.

I'm sorry that I cannot help further. I will let other VB developers who are also proficient in C# take a stab on converting this to VB.
C# code:

var results = from g in graphicCollection
  group g by (string)g.Attributes["Officer"] into r
  select new { Officer = r.Key, Incidents = r.Sum(i => Convert.ToInt32(i.Attributes["Incident"])) };

JayKappy

I pasted in everything from #6 right from the forum

This is from your example:

       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")))

When I paste into Microsoft Visual Studio it puts the () after the r Automatically

        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")))

Visual Studio wont let me remove it....I agree this is what might be causing the problem with the DirectCast(g.Attributes("Officer"), String)??? But I dotn know....I cant remove it

I did this in my current project and in a brand new project....both places it wont let me remove the () after the r that it places in there....

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()".

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

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

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

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

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

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.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.