Query BrainStorm

JayKappy · ‎02-11-2011

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

JayKappy · ‎02-16-2011

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 · ‎02-16-2011

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 · ‎02-17-2011

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 · ‎02-17-2011

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.

JayKappy · ‎02-18-2011

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

JayKappy · ‎02-18-2011

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

JenniferNery · ‎02-18-2011

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 · ‎02-18-2011

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 · ‎02-18-2011

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

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

JayKappy · ‎02-22-2011

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>