How do you handle the situation where there are several attribute records (rows) for the same feature?
  
How do you create a query that uses attributes that are in separate records?
  
The following article illustrates a way to answer these questions.


Introduction

An excellent example of the type of data that illustrates the problem is the analytical lab data from site sampling.  Typically, each sample location has many records that each contain one lab result for a single parameter (chemical).   Each sample location could also have additional records with lab results for several sampling depths and sampling events(dates).

To keep things simple we will not consider depths or dates; however,  the same technique can be used.
 
In our example we will consider just two(2) parameters, lead (PB) and mercury (HG).    We are tasked to find all of the sample locations on our simulated site where lead has been detected with a concentration of 200 mg/kg or greater, AND mercury has been detected with a concentration of 0.5 mg/kg or greater.
  
Our example Avenue script below uses ArcView  bitmaps to help solve the problem.

Background On ArcView's Bitmaps

A bitmap is an ArcView object that contains an "on-off" switch, or bit, for each record of an ArcView VTab.   Since VTabs are the basis for ArcView table documents, tables have bitmaps.   Theme's that are derived from feature tables (FTabs) have bitmaps because FTabs are a sub-class of the VTab.   VTabs and bitmaps can also be created independently using Avenue.   They do not need an ArcView table document to exist.


An individual bit is "set" when the row of table document appears in the system's selection color.   When a bitmap is associated with a theme (FTheme),   both the row in the attribute table and the feature on the map display appear in the system's selection color.


In addition to ArcView's standard GUI methods of selection, the bits of a bitmap can be set and manipulated using Avenue.   We will be using Avenue's "AND" operator for bitmaps.   When two bitmaps are "ANDed" the resulting bitmap contains bits that are "set" where both of the "ANDed" bitmaps have a bit "set" for the same VTab record.


Overview of Our Sample Avenue Script

A query string for each parameter (chemical) of interest is added to an ArcView list.    Each query string in the list is individually applied to the VTab for the lab results.   As each query is processed, the bitmap that contains the results of the query is saved in a separate list.


  Figure 1 - Lab results table where  mercury is >= 0.5 mg/kg


Next, we established a link from the lab results VTab (many) to the corresponding sample locations (one) using the sample location field (locid).   When we select a record in the lab results VTab, the corresponding sample location is automatically selected in the sample location VTab.


We can now determine which sample locations are selected because of an individual query by setting the lab results bitmap for each query string.   The "link"   to the sample locations immediately produces a selected set of sample locations.    If we "AND"  the sample location's bitmap with  the previous location's bitmap as we go, only the  locations that satisfy all of our queries are set in the final sample location bitmap.


Figure 2 - Sample locations table where lead is >= 200 mg/kg and mercury is >= 0.5 mg/kg

Sample Avenue Code

''
'' Example of how to handle ArcView coverages with
'' multiple attribute records per feature.      
''
'' GeoSnap Software - www.GeoSnap.com
''
'' Notes:  "bchres - (Many)"  VTab
''
''         "Parlabel" contains the parameter(chemical) name code
''         "Parval"   contains the detected amount
''         "Units"     contains the units of measure
''
''

'--- get the theme that contains the sample locations (points) ---'

aView = av.GetProject.FindDoc("View1")
aTheme = aView.FindTheme("bchldi")


'--- make a list to hold the query strings ---'
aListOfQueries = List.Make


'--- make the query string (1) ---'
'--- Lead (PB) >= 200 Mg/Kg ---'

aQuery = "([ParLabel] = " + "PB".Quote + ")"
aQuery = aQuery + " and " + "([Units].Trim = "+ "MG/KG".Quote + ")"
aQuery = aQuery + " and " + "([ParVal].AsNumber >= 200)"

aListOfQueries.Add( aQuery )


'--- make the query string (1) ---'
'--- Mercury (HG) >= 0.5 Mg/Kg ---'

aQuery = "([ParLabel] = " + "HG".Quote + ")"
aQuery = aQuery + " and " + "([Units].Trim = "+ "MG/KG".Quote + ")"
aQuery = aQuery + " and " + "([ParVal].AsNumber >= 0.5)"

aListOfQueries.Add( aQuery )


'--- get the "Many" Vtab (sample results) ---'
aTable = av.GetProject.FindDoc("bchres - ( Many )")
aVtab_Many = aTable.GetVtab


'--- Get the selection bitmap ---'
aBitMap = aVTab_Many.GetLastSelection


'--- make a list to hold the bitmap results for each query ---'
aList = List.Make


'--- Run each query and save the selection bitmaps. ---'

For Each aQueryString In aListOfQueries

   '--- run the query ---'
   aVTab_Many.Query (aQueryString, aBitMap, #VTAB_SELTYPE_NEW )

   '--- save the bitmap results of a query ---'
   aList.Add( aBitMap.Clone )

End '-- For Each aQuery In aListOfQueries


'--- link the "Many" VTab (sample results) to the "One" VTab (sample locations) ---'

aVtab_One = aTheme.GetFtab


aField_Many = aVtab_Many.FindField("Locid")
aField_One = aVTab_One.FindField("Locid")


aVTab_Many.Link(aField_Many, aVTab_One, aField_One)


'--- initialize by pre-selecting all of the sample locations ---'

aVtab_One.GetSelection.SetAll
SaveBit = aVtab_One.GetSelection.Clone


'--- Loop through and set each selection bitmap from the queries above.
' Sample locations are selected by the link as each bitmap is set. ---'

'--- "AND" the bitmap of currenly selected locations with the bitmap of
' locations that were selected by the previous query. ---'


For Each aBit In aList

   '--- set the bitmap of the selected results for a query ---'
   aVTab_Many.SetSelection( aBit )


   '--- "AND" with the previous bitmap ---'
   SaveBit.And( aVTab_One.GetSelection)

End 'For Each aBit In aList

'--- The "SaveBit" bitmap now contains only the sample locations
' that are set for all of the queries ---'

aVtab_one.SetSelection( SaveBit )

MsgBox.Info("Done!","")

Return True

  
Where do we go from here ?

Better Query Definition GUI

Obviously, a production version of a tool for quering analytical lab results data needs a much more sophisticated GUI.  A nice Visual Basic interface
is the next step.


Routine Queries

Site sampling databases are usually very large and queries can be very slow.    It would be nice if we didn't have to process queries that have been done before.   Next time we will look at a way to do just that.

Please watch for future Avenue "Quick Tips".

We are interested your comments and welcome any suggestions for future
"Quick  Tip" topics.
e-mail us

Copyright © 2000   GeoSnap Software

ArcView,  Avenue, and MapObjects  are trademarks of Environmental Systems Research Institute, Inc.

Click  here  to start at the  GeoSnap home page.   -  www.geosnap.com