Chapter 16

Attribute Data and Dynamic Queries

Data in GeoMedia consists of spatial (geometric) elements and nongraphic attributes. A county boundary is an example of a spatial element —the "where" of a warehouse. Characteristics of the county such as its population, number of employed persons, percentage with a college education, and so on are attributes — the "what" of a warehouse. A typical example of an attribute query is "Create a map showing all counties in the United States that have more than 10,000 residents" or "Identify all parcels with an assessed value greater than $50,000."

If the attribute data were all in an Access warehouse, you would not need GeoMedia to conduct these queries, or to produce a report, or to output the data to a word processor or spreadsheet. This could all be done in Access. (See Chapters 25 and 38.) GeoMedia goes far beyond a simple database query system, however, in three ways. First, it easily creates maps that show you where the spatial entities are that meet the criteria. Second, it allows you to ask such questions across a wide range of source data in different warehouses. Third and most importantly, it allows you to mix these attribute queries with spatial queries.

This chapter deals strictly with attribute queries, including the ways in which you can query the attribute elements of a warehouse to identify the various spatial entities that have the attributes you desire. The following chapter explores spatial queries. An example of a spatial query is "Display all parcels within 1 mile of the new school".

Dynamic Attribute Data
and Dynamic Queries

When you create a query in many older GIS or desktop mapping systems, the answer you get is described as the query set, which is a subset of all records in the database. That is, it is just those records that meet selection criteria. A query set is usually a "static" entity. It does not change after the query is processed, even if the underlying database has changed. Suppose, for example, you create the query "Identify all parcels where the amount of gas being consumed is greater than 1,000 cf". The system would go to the database, find all records where the value in the column gas_used is greater than 1000, and output all the information in the qualifying records to a new file, report, or some other structure.

So far so good —if the data is static. However, what happens if the database constantly changes? In this example, gas usage could go up or down each day. If the results are a file or other fixed structure, the query you really asked was "Identify all parcels where the amount of gas being used is greater than 1,000 cf. at the moment I asked the question."

GeoMedia uses a new approach to queries. When you apply selection criteria to a warehouse, GeoMedia creates what is referred to as a "pipe." All of the data in the warehouse is plugged into the pipe and the selection criterion "gas_used greater than 1000" is a filter, allowing only certain records to flow through the pipe to the other end. If the source of the pipe (the database) changes, the output automatically changes. As a result, GeoMedia queries are dynamic. The answers change as the data changes. The workflows in this chapter will demonstrate this process.

2 NOTE 1: Attribute-based queries will update automatically. Spatially based queries will update only when the display is refreshed. Spatially based queries are discussed in the next chapter.

2 NOTE 2: It is possible to base a query on a query. This is discussed in detail later in this chapter.

If you are working with essentially static data, all of this will have no real impact. However, if you are a GeoMedia user who is working with large, dynamic enterprise-type databases, this capability will be very important, even essential. When you need to take a static snapshot of the database, this is easily done. The results of a query can be saved permanently in a feature class using the Warehouse > Output to Feature Class command. Examples of this capability are presented later in the chapter.

The dynamic nature of GeoMedia applies to Access warehouses. When the GeoMedia warehouse is Access-based, a change in the warehouse is instantly reflected in the current GeoMedia map or data window. If you exit GeoMedia today and start using the same workspace tomorrow, queries will be reapplied to the data when the workspace reconnects to the warehouse. For other types of warehouses, the query is reprocessed whenever you reconnect or when you execute the Warehouse > Refresh with Warehouse Changes command. If you want to determine if a non-Access warehouse has been changed during a GeoMedia session, it is necessary to contact the warehouse and ask it if any updates have occurred. This is also done with the Warehouse > Refresh with Warehouse Changes operation on the GeoMedia Main Menu bar.

Attribute Filter Queries

Attribute filter queries are created with Analysis > New Query from the Main Menu bar. When the operation is selected, the initial New Query dialog, shown in the illustration at left, pops up.

The first step is to determine what data you want to query. The "Select Feature in" drop-down, shown in the illustration to the left, contains a list all of the warehouse connections and all existing queries. If you click on any of the warehouse connection names, all its feature classes are displayed.

2 NOTE: Clicking on the Options button will bring up the Query tab of the Tools > Options dialog. See Chapter 10 for details. Once you select one of the feature classes, the Filter button is accessible. Clicking on it brings up the Filter dialog, which allows you to create the attribute-based query. You do not have to use the Filter button to create a query. You can simply type the SQL statemetn in the Filer dialog window in the New Query box. However, it is very easy to mistype the query string. Therefore, it is probably easier to use the Filter button and respond to the prompts and help. There are some exceptions to this general rule that are covered later in the chapter. The States filter of the Query Builder dialog is shown in the following illustration.

There are four key components to the New Query dialog. In the upper left, attributes associated with the currently selected feature class are displayed. There is a slider bar to the right of the box so that you can scroll up or down through large lists. The Attribute field names are displayed in alphabetical order. If you do not see the expected names, you have probably selected the incorrect feature class. To remedy this, click on Cancel and select the correct feature class. Remember that the feature class resides in a warehouse and you may have feature classes with the same name in different warehouses, perhaps with different attributes.

To move a specific attribute from the Attribute dialog to the filter you are building, you may either click on the attribute twice with the mouse or click once and then click on the down arrow beneath the Attribute dialog. The selected entry is placed at the current cursor location in the Filter dialog area. When an entry in the Attribute dialog is highlighted (even before you have moved it to the Filter area), you can click on the Show Values button to see what the actual attribute values in the warehouse are. You may get a warning box that says "Processing will take longer for large datasets. Do you want to continue?" You have the option to answer Yes, which will display the values, or No, which will abort the process.

2 NOTE: You can determine if this dialog is to be displayed each time you click on the Show Values button with the "Confirm show value" operations flag in the Options dialog. The Options dialog can be accessed either by clicking on Options on the New Query dialog or via the Query tab on the Tools > Options dialog from the main GeoMedia menu bar. The "Show Values’ button is very helpful when you do not know the range of the data values. It is almost a must when constructing queries on character type data. Character field length and uppercase or lowercase letters are critical factors when matching on a character field. The data values are displayed in increasing numeric or alphabetical order. Clicking twice on a value will place it in the Filter window, as will clicking once and then clicking on the down arrow below the Show Values panel. The value will be placed at the current location of the cursor. 2 NOTE: You can always move the mouse to the Filter area and place the cursor in a desired location, and at any point you can simply type in the lower Filter box.

The final section of the Filter dialog is called Operators. The specific operators are presented in two ways. Operators available for all warehouses are shown as a series of buttons. These are:

=	Exactly equal to
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to
>	Greater than
<	Less than
( )	For setting up sub-clauses
and	The logical and operator
or	The logical or operator

Other operators might be available, which are accessed by clicking on the down arrow at the bottom of the Operators area. Whether or not operators are available, and which operators are available, will depend on the specifics of the warehouse connection. That is, different warehouse types have different SQL operators. These special operators will be covered in a later section of this chapter.

When you apply the various attributes, operators, and values you are creating a structured query language (SQL) statement. If you look at the tab at the top left of the filter designer, you will see the text

From #### where

where the pound signs will be replaced by the feature class you selected earlier. In the previous illustration, the tab reads

From States where

The query you are building will be concatenated to this so that the resulting query will be something like

From States where ANNULRAIN > 15

Some examples of query filters are:

ANNULRAIN > 15

ANNULRAIN <= 15

(ANNULRAIN < 15) OR (ANNULRAIN > 40)

(ANNULRAIN < 15) AND (ANNULSNOW < 10)

The last two of the previous examples use the parentheses to specifically delimit two subordinate clauses that are linked. In the first of these, the query is to find all states where the annual rain is either less than 15 or greater than 40. In the second, the query is to find all states where the annual rainfall is less than 15 and the annual snowfall is less than 10. You particularly need to be clear on the use of AND. At first glance, a statement such as

(ANNUALRAIN < 15) AND (ANNULRAIN > 50)

might seem reasonable. However, it is not. It actually asks for all states where the field ANNULRAIN is simultaneously below 15 and above 50. This is impossible! AND operations should be used to connect different attributes, not when referencing the same one.

When you have completed your query string, it will be displayed in the Filter panel, as shown in the following illustration. You should review it a final time for errors and then click on OK.

Fig. 22-4

2 NOTE: If you are totally dissatisfied with the query, you can click on the Clear button and all text will be cleared. Or you can use the mouse to wipe over text and delete it, insert new text, and so on. You will be returned to the original New Query dialog, with the Filter text displayed. At this point you can simply click on OK and the filter will be applied to the data set. Before you do, however, it is a very good idea to type in a useful name in the Name field. GeoMedia will assign the default name Query# to each query you create, where the pound sign is a sequential number starting at 1. Queries can be difficult to keep track of, and a good name is essential. When the result of the query is displayed in a map window, the name you create here will be the legend entry. You can use characters and blanks to create the query name, as shown in the illustration at left. There is also space for a text description of the query. Displaying the Result
of Attribute Queries

Replace this graphic with hgm16-1.tif

Once you have created a query, you will need to decide how to deal with the result. You can immediately display results in your current map window, in a new map window, in a data window, or in any combination of the three. You can define how you want the query handled by clicking on the Options button on the New Query dialog (see previous illustration), which makes the Query tab of the Options dialog available (see the illustration at left).

2 NOTE: This dialog can also be accessed through Tools > Options from the main GeoMedia menu. If you select the active window, and the window that was active when you began the New Query dialog was the map window, the query will be added at the top of the current window legend and drawn in the map window. If a data window was active, the records that meet the query selection criteria will replace its contents. In the other options, new windows; map, data, or both, will be created and the results displayed.

Selected Special Operators

In addition to the basic math and equality operators, there are a number of other possible operators that may be available to you in creating queries. The drop-down field under the operator buttons in the New Query > Filter dialog will be different, depending on the type of warehouse the selected feature resides in. Some of the common operators follow. You need to refer to the documentation on the specifics of the SQL implementation supported by specific warehouses.

• % ANSI standard "zero or more" character match. (See discussion of character fields in material that follows for more examples.)

• Example: From States where STATE_NAME LIKE ‘Ar%’

2 NOTE: This operator does not work with Access warehouses. • * Serves both as the multiplication operator and as "zero or more" character match. (See discussion of character fields in material that follows for more examples.)

• Example: From States where STATE_NAME LIKE ‘Ar*’

2 NOTE: You should use this for the global match wild card character with Access warehouses. • + Addition

• - Subtraction

• _ Underscore means match a single character. (See discussion of character fields in material that follows for examples.)

• Example: From Interstates where SUB_REGION LIKE ‘E _ Central"

2 NOTE: This operator does not work with Access warehouses. • BETWEEN Determines if the values fall within a designated range.

• Example: From States where ANNULRAIN BETWEEN 5 and 10

• IN Takes a list of values.

• Example: STATE_NAME IN ( 'Kentucky ' , 'Louisiana ' , 'Maine ' )

• NOT Negation of operation.

• Example: STATE_NAME NOT IN ( 'Kentucky ' , 'Louisiana ' , 'Maine ' )

• Example: From States where ANNULRAIN NOT BETWEEN 5 and 10

Just because an operator is not available in the drop-down list does not mean it is not available for use with that warehouse. For example, there are no special operators in the list for Arc/Info or ArcView warehouses; however, the LIKE, BETWEEN, and IN operators are available. The wildcard operators (%,*,_) are not available. With these warehouses, the NOT operator works with IN but not with BETWEEN, and so on. Test out your specific warehouse connection on some known data with various operators to determine which ones are available.

Overlay Operators for MGE Segment Manager Warehouses

If you are creating a query on an MGE Segment Manager warehouse, you will have additional operators available to you. They can be accessed through the Add Overlay button that appears on the query builder dialog when you are querying an MGE Segment Manager warehouse. This button is only available when you are connected to an MGE Segment Manager (MGSM) warehouse. Selecting this button opens the Add Overlay dialog, where you can specify overlay operators and an MGSM distributed attribute table for the query. After specifying this information, a new tab is added to the Filter dialog. These operators are discussed in more detail later in this chapter.

Character Fields

Character fields are common in GIS and desktop mapping and can present problems to the unwary. However, they can also be manipulated in many powerful ways using a variety of special characters and the LIKE operator.

Using LIKE in Character Fields

The LIKE operator is a very useful operator when the field under evaluation has a character data type. Suppose you wish to create a query set of just the state of Arkansas from the Access warehouse USSampleData.mdb. In this database, the state names are in the column STATE_NAME. One approach might be to create the following query string

STATE_NAME = ‘Arkansas’

Unfortunately, this query string will not find any matches because the equals operator checks to make sure that the criteria exactly matches the field’s value. In this case, the field is 20 characters and the text typed in the field is not. You would have to type

STATE_NAME = ‘ArkansasBBBBBBBBBBBB’ where each B represents a blank.

You might suspect that you could use the "match all" operator, the asterisk (*), as follows:

STATE_NAME = ‘Arkansas*’

However, this does not work either, and remember, this wildcard operator is not available for all warehouses. Only a perfect character-by-character match will work when you are using the normal equal operator. The LIKE operator is designed for this situation. The query string

STATE_NAME LIKE ‘Arkansas’

will work whether the character length is 10, 20, or whatever. You should refer to a thorough source of documentation on SQL operators to find out more about the capabilities possible with LIKE when used with other string operations. If you are working with an Access warehouse, the Access help can be used.

Special Characters Used in Character Fields

You can also couple LIKE with many other special operations and manipulate character fields in complex ways. How this works will vary, depending on the warehouse you are using. The drop-down list of special operators in the dialog used to create a query will be different for different warehouses because GeoMedia automatically adjusts them. When you are connected to an Access warehouse, the special wildcard characters that appear on the drop-down include the following.

• % Matches zero or more characters in the ANSI version of SQL.

• _ The underscore matches any single character.

• * Matches "zero or more" characters in the Microsoft Access operations.

In practice, however, neither the % nor underscore operates with an Access warehouse. The asterisk works.

Depending on the operators available to you, you can perform many complex query operations on character fields. For example, suppose you want all the states that start with the letter A. The query string would be

STATE_NAME LIKE ‘A*’

where you are using the special "match all" character of the asterisk. Creating the same string with the ANSI wild card

STATE_NAME LIKE ‘A%’

will not return any records when used with an Access warehouse but will not return an error condition. Even more complex options are possible. Suppose you want all states except those that start with the letters D through Z. The following would generate the desired result:

STATE_NAME LIKE '[!D-Z]*'

Here, the character string wild card structure, double brackets, and the negation character (exclamation point) are used together. You would need to type in the square bracket because it is not available from the drop-down but does work with an Access warehouse.

There are many other possibilities. Because of the variation between warehouses, you should spend some time working through alternatives to determine what will and will not work. Use a small data set you are familiar with so that you will know what to expect for the query results.

Combining Operators for Complex Queries

It is possible to combine operators in many ways to create complex queries. Suppose, for example, you are interested in selecting those states where the number of residents with a high school education is greater than 1/5 of the total population:

From States WHERE EDHS > (POP / 5)

Or where the total of those with some college, college graduates, and those with graduate degrees constitute more than 1/3 of the state’s population:

From States WHERE (EDCOL + EDSOMCOL + EDGRAD) > (POP / 3)

Or where the population density is less than five people per square mile:

From States WHERE (POP / SQMILES) < 5

Error Messages

If you create an incorrect query in the query builder, when you click on OK on the New Query dialog, the following message appears.

The query cannot be run or refreshed. Possible causes of the error include network problems or modifications to the feature class definition.

This is a general message and it is usually due to a simple error in the query, not really network problems, though that is possible. To see the specifics of the error you should always click on the More button on the box. Some messages associated with typical errors follow. If you mistype/misspell an attribute name, the error message is:

Too few parameters. Expected1.

If you leave out an operator, the message is:

Syntax error (missing operator) in query expression ‘ANNULRAIN 10’

If you use aggregate SQL operators (such as AVG and SUM) improperly, the message is:

Can’t have aggregate function in WHERE clause (ANNULRAIN < AVG (ANNULRAIN))

After you have viewed the error message and click on OK on the dialog, you are returned to the New Query dialog. You can simply type in the correction in the Filter window or click on the Filter button to go back to the query builder dialog.

SQL Variations

The query structure created is in a standard SQL form. However, there might be slight differences, depending on the warehouse to which the query is applied. In general, GeoMedia will automatically make the minor alterations necessary. However, there are two exceptions. When entering date or time queries to an Access-based warehouse, the date and time values will require a pound sign (#) delimiter around the values. Suppose you wished to identify all valves made after January 1, 1960. The delimiter would look as it does in the following.

"From Valves WHERE Manu_date >= #1-1-60#"

An MGSM warehouse connection would require that the keyword TIMESTAMP be followed by a single quote delimiter. You might receive the following message.

Queries cannot be applied to an Oracle based warehouse that only contains time values. Values that combine date and time (timestamp) or just date work correctly.

If you are connecting to an Arc/Info warehouse, GeoMedia cannot query any coverage that has a reserved SQL keyword as the coverage name. It is always wise to avoid naming anything with such reserved words. Reserved SQL keywords that cannot be used as Arc/Info coverage names are:

ALL, AND, ANY, ASC, AVG, BETWEEN, BY, COMMIT, COUNT, DELETE, DESC, DISTINCT, EXISTS, FLOAT, FROM, GROUP, HAVING, IN, INSERT, INTO, IS, LIKE, MAX, MIN, NOT, NULL, ON, ORORDER, ROLLBACK, SELECT, SET, SOME, SUM, UNION, UPDATE, VALUES, WHERE, and WITH

A double pipe "||" character sequence cannot be used in the attribute field of any data record.

Queries to ODBC Connections

Warehouses that are being accessed through an ODBC connection (MGE and MGDM) may have special requirements for the format of some aspects of query strings (e.g., date and time queries). You should refer to your ODBC documentation for any special instructions on limitations the particular driver enforces. Where there are special restrictions, it will be necessary to manually edit the query string in the Filter text window of the New Query dialog before clicking on OK and submitting the query.

Building Queries Based
on Other Queries

It is quite simple in GeoMedia to create a "dependent" query. This is a query that uses as its base the results of a existing query. Consider the following simple example, where Query 1 is

WHERE ANNULRAIN > 20

based on the States feature class in the USSampleData Access warehouse provided with GeoMedia. Suppose you are also interested in states that have this amount of rainfall and that have a population greater than 1,000,000. Query 2 would be based on Query 1 and look like

Where POP > 1000000

2 NOTE: It would have been easy to create a single query to accomplish this, such as "Select States where (POP > 1000000) AND (ANNULRAIN > 20)." Only those records that were in the Query 1 record set would be processed through the filter of Query 2.

Changing and Deleting Existing Queries

Once you have created a query it can be altered or deleted. You can access existing queries from the Main Menu bar with Analysis > Queries. This will bring up the Queries dialog, shown in the illustration at left.

All queries you have created in this workspace will be shown in the list. To delete a query, you simply click on it and press the Delete key.

2 NOTE: Queries can be built on other queries. Therefore, exercise caution in deleting a query that may be serving as the basis for another. A more flexible option is to click on Edit to change the query. You will be presented with the Edit Query dialog, shown in the illustration to the left. This operation is essentially identical to the original query building dialog except that it is operating on a predefined query. Essentially everything can be changed, making the Edit Query module a very powerful "what if" engine. In the example shown in the illustration at left, states with an annual snowfall of 75 or more inches have been selected and (if the "show in active map window" display option has been chosen) you would see the five states with these attributes.

What would the map of states with 100 inches look like? Simply edit the query by replacing the 75 with 100 and clicking on OK. Only the states of Maine and New Hampshire are then displayed. If you create a query that has no records that match it, the map window will (of course) become blank.

2 NOTE: If you have fitted the map window to one set of results (as in the example, the states of New Hampshire and Maine) and the new results have a greater geographic extent, the geometries outside the current map window extent will not be visible unless you use Fit All. Editing and Deleting Dependent Queries

As previously discussed, it is possible to create queries that are dependent on earlier queries. When you edit one of the earlier queries, the resulting changes in the record set are passed on to the dependent queries. In an earlier example, Query 1 was defined as

WHERE ANNULRAIN > 20

and Query 2 was

WHERE POP > 1000000

This query found the wetter states with populations greater than 1 million. If you changed Query 1 to read

WHERE ANNULRAIN < 20

the resulting map for the POP > 100000 query would switch from showing the eastern, larger-population states to displaying only California.

When you edit a query, GeoMedia does not immediately process the query unless the query is currently displayed in a map or data window. This allows you to save time if you have a slow connection or a very large data set in the warehouse. If you delete a query, it will not be removed from the map or data windows. You will need to delete the query from these individually. If you delete a query another query is dependent on, the dependent query is still functional. You will not be able to edit the dependent query, but it will still operate.