Getting Started

Building and Using Queries

Q
U
E
R
I
E
S

Building andUsing Queries

with

TNTmips®
TNTedit™
page 1

TNTview®

Building and Using Queries

Before Getting Started
This booklet introduces you to the use of database queries in the TNT products
and shows you how to construct queries to utilize the attribute information attached
to your vector, CAD, and TIN objects. The query process may seem complex atfirst, but this series of step-by-step exercises leads you through the required
structure and syntax of queries, progressing from simple one-line examples toqueries with multiple condition statements and processing loops.
Prerequisite Skills This booklet assumes that you have completed the exercisesin Getting Started: Displaying Geospatial Data and Getting Started: Navigating.
Those exercises introduce essential concepts and skills that are not covered again
here. Please consult those booklets and the TNTmips reference manual for any
review you need.
Sample Data The exercises presented in this booklet use sample data that is

distributed with the TNT products. If you do not have access to a TNT products
CD, you can download the data from MicroImages’ web site. In particular, thisbooklet uses sample files in the QUERY data collection. Make sure that you have a
copy of the sample data on your hard drive so changes can be saved when you use
the objects in these files.
More Documentation This booklet is intended only as an introduction to the useof database queries. Consult the section entitled “Display Vector, CAD, and TIN
by Query” in the Display volume of the TNTmips reference manual for more
information on the Query Editor, and Appendix 2: Database Queries for a complete
reference on query commands and functions.
TNTmips® and TNTlite® TNTmips comes in two versions: the professional version

and the free TNTlite version. This booklet refers to both versions as “TNTmips.”
If you did not purchase the professional version (which requires a hardware key),
TNTmips operates in TNTlite mode, which limits object size and does not allow
export.
Database queries can also be used to control the display of geospatial objects in
TNTview, and to select elements for editing in TNTedit. All exercises in thisbooklet can be completed in TNTlite using the sample geodata provided.
Randall B. Smith, Ph.D., 17 September 2001
It may be difficult to identify the important points in some illustrations without acolor copy of this booklet. You can print or read this booklet in color fromMicroImages’ web site. The web site is also your source for the newest Getting
Started booklets on other topics. You can download an installation guide, sample
data, and the latest version of TNTlite.
http://www.microimages.com
page 2

Building and Using Queries

Welcome to Building and Using Queries
TNTmips gives you great flexibility to use thedatabase attributes of vector, CAD, and TIN objects
to control the display and printing of the object, orto select elements for use in various processes.
Database queries provide the most complete andversatile means to utilize this attribute information.
A database query is a set of instructions defining
attribute criteria that are used to select records froma database. The specific spatial elements (such aslines or polygons) to which those records are attached
are then automatically selected for the current
process. A query applies to a specific element type,and you can simultaneously use separate queries fordifferent element types in an object. In Spatial Data
Display, the Style By Script option allows you to
specify display parameters for selected elements on
the basis of attributes. The attribute information you
refer to in queries can be qualitative (such as a classname), or quantitative (such as crop yield values).
Queries must use a standard “grammar and syntax”
that TNTmips understands. The query language used
is a subset of the TNTmips Spatial Manipulation
Language (SML). You compose queries in the Query
Editor window, which has menus that simplify
construction of valid queries by letting you choose
fields from the available database tables and insert
symbols and functions from list windows that outline
the correct syntax. The Query Editor also provides
a syntax checker to help you find errors before
applying the query.
The exercises in this booklet use queries to select orstyle elements in a vector object for display. Keepin mind, though, that queries can be used in any
process that selects component elements in vector,
CAD, or TIN objects. In addition, you can use
queries in some raster processes to select cell values
for processing.

page 3

STEPS! make sure that you havea copy of the sample
data in the QUERY datacollection on your harddrive
! launch TNTmips! launch the Spatial Data
Display process andselect New 2D Group
from the toolbar

The exercises on pages 4-9
introduce the structure of
simple query statements,
comparison operators, andsome useful tools forbuilding and checking
queries. Pages 10-17 coverquery structures involving
compound statements, theuse of variables andcomments, and using
queries to check databaserecord attachments. Stylingby Script is introduced on
pages 18-19, along with theuse of conditional “if-thenelse” constructions.
Examples of queries basedon the spatial andtopological attributes ofvector objects are found on
pages 20-23. Pages 24-25
show scripts to createcomputed fields in databasetables, while pages 26-27
provide examples of queries
that are useful in editing
vector objects.

Building and Using Queries

Select by Querying a Single Field
STEPSþ click the Add
Vector icon buttonand select Add Vector
Layer from the dropdownmenuþ select the CBSOILS_LITE
object from the CB_SOILQ
Project File in the QUERY
data collection
þ in the Vector Object
Display Controls window,
check that the Selectoption on the Lines
tabbed panel is set to Allþ set the Select option on
the Labels panel to Noneþ check that the Style
option on the Polygons
panel is set to By
Attribute

The simplest form of query selects a specific type of
spatial element (such as polygons, lines, or points ina vector object) on the basis of the values for a single
database attribute. In this exercise you enter a simple
query that selects soil map polygons in a vector object
for display. Each soil type has associated values for
maximum potential yield for several crops. The queryselects polygons for which the potential crop yield
for wheat is greater than 35 bushels per acre. The
query statement has the form:
Attribute

Comparison
OperatorValue

The query must specify which database table contains the attribute information, and in which field it
is found. This “attribute location” information must
be entered in the form TABLE.FIELD. The value inthis example is a simple numeric value,
and the comparison operator is the“Greater than” operator ( > ).

þ set the Select option on
the Polygons panel to By
Query and click on theadjacent Specify button
þ in the Query Editor
window, type thefollowing text exactly(including capitalization):
YIELD.WHEAT > 35
þ click [OK] in the Query
Editor windowþ click [OK] in the Vector
Object Display Controls
window to accept thedisplay settings anddisplay the vector object

Since line selection was set to All, the outlines of all soil
polygons are drawn. The polygons selected by the query
are filled with colors and fill patterns that are based on
the soil type and that were previously set up for display
By Attribute. Several soil types meet the wheat yield
selection criterion. Unselected polygons remain unfilled.

page 4

Building and Using Queries

Using the Insert Operator Option
The previous query selected soil polygons belonging
to several soil type classes. Let’s refine the selection
criterion so that the query selects only those polygons
with a potential wheat yield of exactly 38 bushels
per acre. To specify this selection criterion, use the“Equal to” operator (==, double equal sign) in thequery statement. The Insert / Operator menu option
in the Query Editor opens the Insert Operator
window, which lets you choose the operator from a
scrolled list and insert it into the query statement at
the current cursor location.
Choose Operator fromthe Insert menu to
open the Insert
Operator window...
...which lists theavailable query
operators.

Select the “Equal to”
operator.

STEPS! click on the Vector
icon in the Layer
icon row to open the
Vector Object Display
Controls window! click on Select: [Specify]
in the Polygon Options
panel! highlight “> 35” in theQuery Editor window andpress <Delete>! open the Insert menu
and select Operator! in the Insert Operator
window, scroll down andselect the “==” operator;
click [Insert], then
[Close]! in the Query Editor
window, type 38 to theright of the operator,
then click [OK]! click [OK] in the Vector
Object Display Controls
window to accept thedisplay settings anddisplay the vector object

The text field describes
the function of theselected operator.

Click [Insert]...
...to enter the operator in
the query statement.
Type thevalue to bematched.

Fewer soil classes meet the more
restrictive selection criterion in the revised
query statement.

page 5

Building and Using Queries

Using the Insert Field Option
STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! select YIELD.WHEAT in
the existing query andpress <Delete>! open the Insert Menu
and select Field! in the Insert Field
window that opens, clickon YIELD in the Tablelist! click on OATS in the
Field list, then click[Insert]! click [Close] on
the Insert Field
window! change thevalue on theright side of thequery statement
to 43, then click[OK]! click [OK] in the
Vector Object Display
Controls window

You can also use the Insert/Field menu option in theQuery Editor to help construct or modify queries.
This option opens the Insert Field window, fromwhich you can choose the Table and Field andautomatically insert the attribute location information
into your query statement in the correct form.
Choose Field from theInsert menu to open
the Insert Field
window...
...which lists all
available databasetables for the selected
element type.
Choosing a Table also
displays a list of its fields.
Choosing a field...

...creates the
TABLE.FIELD
entry.

This query selects several of
the same soil classes selectedby the first query (page 4).

Click the Insert button...
... to insert the TABLE.FIELD entry
into your query statement.

Change the value on the right side of
the statement to 43.

page 6

Building and Using Queries

Querying a String Field
The query language used in TNTmips is casesensitive. If the table CLASS contains a field called
Class, the TABLE.FIELD entry must read
CLASS.Class; if you enter it as CLASS.CLASS, thequery process will not find the field and will indicate
there is an error in the query. Using the Insert Field
procedure helps you avoid this type of problem.
The database fields you have used in your queries
so far have contained numeric data. The YIELD
table for CBSOILS_LITE also contains a field named
SYMBOL with soil type symbols in String format.
The term “string” is short for “character string,”
which means that the field is not evaluatednumerically, and can contain text and othernonnumeric characters. String fields may containnumerals (for example, CLASS1), but they are readas characters rather than as numbers. String values
in query statements must be enclosed in double
quotes, and are also case-sensitive.

STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! select YIELD.OATS in
the existing query andpress <Delete>! use the Insert Field
procedure to insert
YIELD.SYMBOL on theleft side of the query
statement! change the value on theright side of the query
statement to "KaB"(including the double
quotes), then click [OK]! click [OK] in the Vector
Object Display Controls
windowThe SYMBOL field contains string values.
Enclose a string value in double quotes.

Selected soil polygons
belonging to class KaB.

page 7

Building and Using Queries

Checking Query Syntax
STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! manually change the left
side of the existing
query statement to
CLASS.CLASS (allcapitals)! choose Check from the
Syntax menu
! note the error message,then click [OK] on the
Message window.
! change the left side of
the query statement to
CLASS.Class! choose Check from the
Syntax menu
! note the “Syntax OK”
message
In this example the cursor isplaced at the end of theinvalid TABLE.FIELD entry.

The rules concerning capitalization and use of quotes
for string values are examples of the syntax of the
TNTmips query language. Query syntax is checked
automatically when you click [OK] to execute thequery, and when you choose Close from the Filemenu to close the Query Editor. (If the querycontains a syntax error when you try to close theQuery Editor, the window remains open and an error
message is displayed.)
You can check the syntax of a query
before executing it by choosing Checkfrom the Syntax menu in the Query Editor.
The process can find misspellings,
missing parentheses or other symbols, orreferences to nonexistent database fields.
The process starts checking at the
beginning of the query. If no syntax errors areencountered, the message line at the bottom of theQuery Editor window reads “Syntax OK.” If a syntax
error is detected, the text cursor is placed at the endof the first component of the statement that thecomputer could not interpret, and an error message
appears in the message line. In this example, thequery checker detected that there is no database field
named CLASS in table CLASS, so the cursor isplaced at the end of the CLASS.CLASS entry. Aftercorrecting a syntax error, you can use the Syntax
option again to check for errors in the remainder ofthe query.

In more complex queries, the syntax error may
actually be the result of something missing after
the point where the cursor is placed, such as aclosing parenthesis or the ‘end’ statement of abegin / end loop.

page 8The “Syntax OK”
message appears if noerrors are found.

Building and Using Queries

Using Calculations in Queries
The value on the right side of a query statement can STEPS
also be provided by a database field or a calculation ! choose New from theFile menu
involving a database field. Calculations in queries ! use the Insert
can use standard arithmetic operations: addition (+),
procedures and / ormanual entry to create
subtraction (–), multiplication (*), and division (/).
the following query
You can enter the operation symbols from the Insert
statement:
Operator window if you wish. The Insert
YIELD.OATS == YIELD.WHEAT + 5/ Function option provides access totrigonometric and other mathematical functions that ! choose Save As fromthe File menu, thencan also be used in query statements. The sample*.QRY File from thequery for this exercise selects soil polygons for which
menu that opens
the potential yield for oats is exactly 5 bushels per ! use the standard File
Selection window toacre greater than the yield for wheat.
By now you have probably noticed that the last queryused for a particular object and element type is
automatically stored with the object and is opened
the next time you select the same By Query option.
If you wish to store several queries for the same
object for future use, you can use the Save and Save
As options on the Query Editor’s File menu. These
options allow you to save the query currently
displayed in the Query Editor window as a text filewith a .QRY file extension, or as a text object in a
Project File. To recall a stored query, use the Open
option on the File menu.

name a new file tocontain the query! click [OK] in the Query
Editor window! click [OK] in the Vector
Object Display Controls
window

Usually a query will work
only with a specific object
because of a reference to aunique database field. If
you have a series of objects
with identical databaseformats, or the query refers
only to fields in standard
tables created by TNTmips,
then you can use the same
query for any of the objects.

Choose RVC Object to store the query
as a text object in a Project File.

page 9Building and Using QueriesCompound Queries
STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

Each of the queries used in the previous exercises
employs a single selection comparison to choose
polygons for display. In many cases you may needto select elements using a combination of several
criteria. A series of selection comparisons in a query
statement must be related to each other by one ormore logical operators from set theory, such as “and,”“or,” and “not.” These operators must be entered in
all lowercase letters. You can insert logical operators
YIELD.WHEAT > 34 and YIELD.OATS > 40 in query statements using theInsert Operator procedure.

! click [OK] in the Query
Editor window and in the
Vector Object Display
Controls window! repeat above steps but
substitute “or” for “and”
in the query statement

When two comparisons are linked by the logical“and” operator, both comparisons must be true inorder to make the entire query statement true andselect the element. When two comparisons arelinked by the logical “or” operator, the query
statement is true if either of the individualcomparisons is true. Elements meeting either
criterion are selected.
A long query statement may continue onto
additional lines, though you may wish to indent
subsequent lines to make it clear that they are partof one statement.

Polygons for which potential wheat yield isover 34 bushels per acre, and potential oat
yield is over 40 bushels per acre.

Polygons for which either potential wheatyield is over 34 bushels per acre, or
potential oat yield is over 40 bushels per
acre.

page 10

Building and Using Queries

Using the “not equal to” Operator
Most of the soil types in the Crow Butte area have ahigher potential yield for oats than for wheat, but
wheat usually brings a higher price than oats when
the crop is sold. Let’s assume the crop prices per
bushel are $3.25 for oats and $4.00 for wheat. This
example query is used to identify soil types for which
the total potential crop price per acre for oats(potential yield in bushels per acre times price per
bushel) is greater than or equal to that for wheat.

STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

This query is complicated by YIELD.WHEAT <> 0 and
YIELD.OATS * 3.25 >= YIELD.WHEAT * 4.00
the fact that the potential crop
yield for soil types that cannot be cultivated is 0, ! click [OK] in the Query
Editor windowand such soil types would satisfy the selection! click [OK] in the Vector
comparison in the second line of the query. The
Object Display Controls
first line of the query excludes the zero-yield soil
windowtypes, and illustrates the use of the “not equal to”
operator ( <> or != ). This statement selects polygons
for which the potential yield for wheat is not equal
to 0. Only these polygons are subjected to the pricecomparison in the second line.

Polygons for which a crop of oats would
bring a higher price per acre than wheat,assuming the potential crop yields and thestated prices per bushel.

page 11

Building and Using Queries

Using Comments and Variables
STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

You can enhance the readability and later usefulnessof queries by including comments. A comment
begins with the “#” symbol, and may be on a line byitself or at the end of a statement. You can use
comments within the query to explain individualstatements, and an introductory comment to provide
an explanation of the intended use of the query andwhat object it applies to.

The TNTmips query process
also allows you to name andassign values to variables foruse in a query. This example# select polygons based on crop price query selects soil polygons thatYIELD.OATS * 3.25 > dollars or
exceed a required potential crop
YIELD.WHEAT * 4.00 > dollars
price per acre for either oats or
wheat. The first line of the query is an assignment! click [OK] in the Query
Editor window
statement that defines a numeric variable called! click [OK] in the Vector“dollars” to store the required price, and gives it a
Object Display Controls
value of 129. The “=” symbol is used to assign awindow
value to a variable (which is why “==” must be used
for the “Equal to” operator).
dollars = 129# define variable for# required crop
# price per acre

Variables are useful when the samevalue is used more than once in thequery. If you want to run this query
again with a different required price,
you only need to change the single value
assigned to the variable “dollars”. If
the query were written without thevariable, you would need to change two
actual numeric values in the selection
statement.

Variable names must always be in lowercase characters, and must begin with a letter.
A variable name cannot be the same as aquery command or a database table or field
name.

page 12

Building and Using Queries

Using String Variables
You can also define variables to contain string val- STEPS
ues. The name you create for a string variable must ! open the Vector Object
Display Controls window
end in a $. The query in this exercise defines a string
and the Query Editor
variable name$, which is assigned the value
window“Glenberg”. The query selects a subset of soil poly- ! choose New from theFile menu
gons belonging to the Glenberg soil series, which! use the Insert
includes two soil types in the Crow Butte area. Inprocedures and / orstead of using the two class symbols to select the
manual entry to create
the following query:
polygons, this query takes advantage of the fact that
the NAME field in the DESCRIPTN
name$ = "Glenberg"
table provides a soil description that
DESCRIPTN.NAME contains name$ andbegins with the name “Glenberg” for( POLYSTATS.Area < 60000 orboth classes. The query uses the
POLYSTATS.Area > 200000 )“contains” operator, which selects
elements for which a specified character string ! click [OK] in the Query
Editor window
matches all or part of a string field. In this case the! click [OK] in the Vector
character string to be matched (“Glenberg”) is stored
Object Display Controls
in the name$ variable. Polygons meeting this selecwindowtion comparison are then screened on the basisof their area (in square meters), which is stored
in the Area field in the standard POLYSTATS
table. (A POLYSTATS table is present only ifstandard attributes have been calculated for thevector object.)
The “contains” operator selects polygons
for which the character string in thename$ variable matches any part of the
DESCRIPTN.NAME string field.

The text string “Glenberg” is included in
the DESCRIPTN.NAME field for both soil
types belonging to the Glenberg series.

page 13

Building and Using Queries

Using the Logical “not” Operator
STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

A number of soils in the Crow Butte area havepotential wheat yields comparable to those of thetwo Glenberg soils (27 and 32 bushels per acre).
The query in this exercise selects all of the soil types
within this range of wheat yield values except the
Glenberg soils.

The first two lines of the query select polygons forwhich the potential wheat yields fall within thedesignated range. The
YIELD.WHEAT >= 27 andthird line of the query
YIELD.WHEAT <= 32 and!( DESCRIPTN.NAME contains "Glenberg" ) begins with the logical“not” operator (!), which! click [OK] in the Queryreverses the result of the variable, operator, orEditor window
expression that follows it. In this case, the expression! click [OK] in the Vector
following the “not” operator would only selectObject Display Controls
window
polygons belonging to the two Glenberg soils. The“not” operator reverses this result and selects all
polygons meeting the previous yield requirements
except the Glenberg soil polygons.
The “not” operator is especially useful when there
is a large set of values you do want to select and asmaller, more easily specified set of values that you
don’t want to select.

The ‘not’ operator reverses the next elementthat follows it (including variables or otheroperators). If you want the ‘not’ operator toapply to an entire expression (as in thisexample), the expression must be enclosed
in parentheses.

page 14

Building and Using Queries

Checking Record Attachments
The query used in this exercise differs from thoseused in all previous exercises. Instead of querying
specific attribute values, it is based on the numberof records in a particular database table that are
attached to a polygon. The query employs the
SetNum( ) set function, which returns the numberof items in a set. In this case the set is provided by
an expression that has the form Table[*], which
stands for all records attached to an element in thenamed table.

STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

This query identifies all soil class polygons that haveno attached records in the YIELD table. The
converse query [ SetNum(YIELD[*]) > 1 ] would
identify all elements with more than one record
attached. You can use queries with this form to
identify polygons that have not yet had attributes
assigned, or that somehow had extra records
attached. In this example, all of the polygons withno attached records in the
YIELD table belong to theclass WATER. They represent
small lakes and ponds, andtherefore have no potential
crop yield.

! click [OK] in the Query
Editor window! click [OK] in the Vector
Object Display Controls
window

Polygons with no attached records in
the YIELD database table all belong toclass WATER. You can also selectunattached elements by right-clicking
on the icon for the corresponding tablein the Group Controls or Layout
Controls window and choosing SelectAll Unattached Elements from the
popup menu.

page 15

SetNum(YIELD[*]) < 1

Use the Function Group
option in the Insert Function
window to select whichgroup of functions
to list in thewindow.

Click [Details] on
the Insert
Function window
to see further
information on
the currently
selected function
and an example
of its use.

Building and Using Queries

Select using Multiple Attached Records
STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

The LAYER table for the soil class polygons in the
CBSOILSQ object contains information on the different
layers in a typical soil profile for each soil. There isa separate record for each layer in the profile, andthus multiple records attached to each soil polygon.
Selecting elements on the basis of attributes among
multiple attached records requires a special querysyntax.
In this exercise, for example, we want to selectsoil types that include weathered bedrock in any
part of the soil profile. This attribute is coded by
the string “WB” in the texture field. If you try using
the conventional selection query LAYER.texture ==“WB”, you will find that no polygons are selected,
even though some soils do have weathered bedrockin the lower part of the profile. This query structure
only checks the first attached record in the table foreach polygon, which in this case is usually the layer
1 record, containing attributes for the topmost soil
layer. Subsequent records for the deeper soil layers
are ignored.

“WB” in LAYER[*].texture! click [OK] in the Query
Editor window! click [OK] in the Vector
Object Display Controls
window

Soil class polygons thathave weathered bedrock aspart of their typical soil
profile.

To query the texture field of all of the attachedrecords, we must use the expression
LAYER[*].texture, which returns a set that lists thecontents of the texture field from eachrecord attached to the current polygon.
We then need to determine if any of themembers of the set correspond to thedesired attribute “WB”. The easiest
way to do this is to use the keyword“in” as a logical operator. The query istrue if the variable preceding theoperator is an exact match to any of theelements in the set produced by theexpression following the operator. This
construction can be used with either
string or numeric fields.

page 16

Building and Using Queries

Find Island Polygons
A vector polygon that is wholly enclosed within alarger polygon is called an island polygon. Because
island polygons often have different attributes thanthe enclosing polygon, processes that alter the
topology or attribute assignments of a vector object
must keep track of island polygon relationships.

STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the following query:

The Internal table for polygons includes several
fields that contain information pertaining to island
polygons. You can query these fields to select island
Internal.Inside > 0
polygons or polygons containing islands. The
Internal.Inside field contains the element number of ! click [OK] in the Query
Editor window
the enclosing polygon, if any. All island polygons
will have a non-zero value in this field. The first ! click [OK] in the Vector
Object Display Controls
query therefore selects all island polygons. The
window
NumIslands field shows the number of islands ! repeat the above steps
using the following
contained by each polygon. The second query inquery:
this exercise selects polygons that have a
NumIslands value greater than 0, corresponding Internal.NumIslands > 0
to all polygons that contain islands.

Island polygons selected by
the first query. Each island
belongs to a different soil
class than its enclosing
polygon.

Two polygons in the
CBSOILS_LITE vector object
include island polygons,
and were therefore
selected by the second
query.

page 17

Building and Using Queries

Styling by Script
STEPS! open the Vector Object
Display Controls window! in the Lines tabbed
panel, turn on the Draw
Lines Before Polygons
toggle button

The Style by Script option allows you to specify
display characteristics for subsets of the selected
elements on the basis of their attributes. To introduce
the style options, this exercise retains the previous
selection query, but uses a style script to set new
display parameters for all selected polygons.
(Normally you would use the All Samestyle option to accomplish this.)

When you are setting styles by script,
the Insert menu on the Query Editor window
provides access to additional variables which areused to set display characteristics. FillInside and
DrawBorder are numeric variables which areassigned a value of 1 to fill selected polygons anddraw a border around them. FillColor$ and
DrawColor$ are string variables which are used to
set the color for the polygon fill and polygon
FillInside = 1
FillColor$ = "100 50 0" border, respectively. The value assigned to them
DrawBorder = 1(enclosed in double quotes) can be either a color
DrawColor$ = "red"name (red, green, blue, black, white, yellow,
orange, brown, cyan, magenta, or gray), or a set of! click [OK] in the Query
Editor window
RGB values (each from 0 to 100%).
! in the Polygons tabbed
panel, set the Style
option to By Script, andclick the adjacentSpecify button! use the Insert Symbol
procedure (Numeric andString options) to create
the following style script:! click [OK] in the Vector
Object Display Controls
window

Use the option button on the Insert Symbol
window to access the different symbol lists:
constants or the different types of variable(including numeric, string, and vector,
among others).

page 18

Building and Using QueriesCompound Style Scripts
In this exercise all soil class polygons are selected
for display, and a style script is used to define twodifferent sets of polygon display parameters on thebasis of the polygon area.

STEPS! open the Vector Object
Display Controls window! in the Polygons tabbed
panel, set the Selectoption to All, leave theStyle option set to By
Script, and click theadjacent Specify button! choose New from theFile menu! use the Insert
procedures and / ormanual entry to create
the query shown below! click [OK] in the Query
Editor window! click [OK] in the Vector
Object Display Controls
window

When you want to specify alternative actions in aquery or style script, you must use “if-then-else”
commands to explicitly define the logic. The statements in this script translate to “if a polygon has anarea greater than 200000 square meters, then fill itwith yellow, else (otherwise) fill it with a bitmap
pattern (‘BitmapPatt4’)”. When more than one related statement follows a “then” or “else” command(as in this example), the group of statements must
be enclosed within begin/end commands. Omitting
the begin/end commands after “else” in this query
would not produce a syntax error.
However, in that case only the first DrawBorder = 1
statement would be applied as the DrawColor$ = "red"if ( POLYSTATS.Area < 200000 ) thenalternative to the “then” action;
begin
the remaining statements would
FillInside = 1
FillColor$ = "yellow"
be interpreted as applying gloend
bally to all selected polygons (likeelse
the first two lines of the query),
begin
overriding the style parameters
FillInside = 1
FillBitmapPatt = 1
defined earlier.
FillPatt$ = "BitmapPatt2"
end

In order to use a bitmap fill pattern in a script,
the pattern assigned to the FillPatt$
variable must reside in the User Set ofdefined patterns for the object. In order tohave the pattern drawn, variables
FillInside and FillBitmapPatt must
both be set to 1. See the booklet Getting
Started: Creating and Using Styles for
information on creating fill patterns and otherstyles.
Polygons with an area of 200,000 square
meters or greater are filled with the stripe
pattern defined in BitmapPatt2. Smaller
polygons are filled with yellow.

page 19

Building and Using Queries

Find the Polygon Enclosing a Point
STEPS! open the Vector Object
Display Controls window! in the Polygons tabbed
panel, set the Style
option to By Attribute! set the Select option to
By Query, and click theadjacent Specify button! choose New from theFile menu in the Query
Editor window! use the Insert
procedures and / ormanual entry to create
the query shown below! click [OK] in the Query
Editor window! click [OK] in the Vector
Object Display Controls
window

The query process incorporates a number of spatial
functions that can be used to select elements. The
FindClosestPoly( ) function (in the Vector functiongroup) returns the element number of the polygonthat encloses a point with the specified x and ycoordinates. The required parameters for thefunction include the vector object to query, the x
and y coordinate values, and the object number ofthe georeference subobject to use in processing thecoordinate values. The georeference object number
is provided by the function GetLastUsedGeorefObject( ) (in the Georeference Function
Group), which is used in the assignment statement
for the variable “georef”. The predefined variable“Vect” (in the Vector list in the Insert Symbol
window) is used to indicate the current vector object.
The first two lines of the query define variables
containing
xvar = -103.33861
values for theyvar = 42.73583
georef = GetLastUsedGeorefObject( Vect )x and yc_poly = FindClosestPoly( Vect, xvar, yvar, georef ) coordinates
Internal.ElemNum == c_polyof the point.
The FindClosestPoly( ) function is used in an assignment statement that stores the element numberof the enclosing polygon in a numeric variable(c_poly in this example). The final statement of thequery compares the element number of
each polygon in the object(Internal.ElemNum) to the number
stored in c_poly to find the matching
polygon for display.

Location of the point specified by coordinates xvar (longitude) and yvar (latitude).
Values used in the query must be in thesame coordinate system as the specified
georeference subobject. Latitude / Longitude coordinates must be expressed in decimal degrees.

page 20

Building and Using Queries

Polygon Adjacency Query: Logic
A selection query can also make use of the
topological information associated with a vector
object. Each line in a vector object has a beginning
node and an ending node, which define a left andright side for the line. Each polygon is made up of
specific line elements, and the Internal table for lines
includes fields that contain the element numbers of
the polygons that lie on either side of the line. The
GetVectorPolyAdjacentPolyList( ) function (in the
Vector list in the Insert Function window) uses thisinformation to determine which polygons are
adjacent to the current polygon. This function canbe used in a query to select polygons that are adjacentto specific polygon classes.
As an example, let’s examine a query for the
CBSOILS _LITE vector object that selects polygons
belonging to soil class “SrD” that are also adjacent
to polygons of class “Sa.” To be considered adjacent,the polygons must share a common line boundary,
not just a common node. The general strategy usedin such a query is as follows:
1) Define the class to select.
2) If a polygon belongs to the selected class, then do
the subsequent steps (test for adjacency), otherwise
reject it.
3) Get the list of polygons that are
adjacent to the current polygon.
4) Check the class assignment for eachadjacent polygon. If any of them matchthe defined adjacent class, select thecurrent polygon for display. If none
match, reject it.
The syntax for this query is shown andexplained on the next page.

page 21

STEPS! open the Vector Object
Display Controls window
and the Query Editor
window! choose New from theFile menu! use the Insert
procedures and / ormanual entry to enter
the query shown on thenext page! click [OK] in the Query
Editor window! click [OK] in the Vector
Object Display Controls
window

Vector object CBSOILS_LITE
with all polygons of classes
SrD and Sa selected (forcomparison with illustrationon the next page).
Class SaClass SrD

Building and Using Queries

Polygon Adjacency Query: Syntax
1 if (CLASS.Class == "SrD") then
2
begin
3
array polylist [10]4numpolys = GetVectorPolyAdjacentPolyList(Vect, polylist)
5
for i = 1 to numpolys begin
6
polynum = polylist[i]7if (Vect.poly[polynum].CLASS.Class$ == "Sa")then8
return 1
9end
10
return 0
11end
12 else return 0
1. Conditional selection of class SrD polygons for subsequent testing.
2. Begins the processing loop to check the class of adjacent polygons.
3. Defines a one-dimensional array called “polylist” to hold a list of the element numbersof polygons that are adjacent to the current polygon. Initializes the array size at 10
elements (it is resized automatically by the function in the next statement).
4. Defines a variable “numpolys” that is assigned a value equal to the number ofpolygons adjacent to the current polygon. This value is returned by the
GetVectorPolyAdjacentPolyList function, which also finds the element numbers of theadjacent polygons and stores them in the “polylist” array. The predefined variable“Vect” is used to indicate the current vector object.
5. Begins a processing loop to examine the class of each polygon in the array. The loop
is run once for each element in the array, beginning with the first position (array index
1) and continuing to the last position (specified by the current value of variable“numpolys”). In each loop the variable “i” is assigned the value of the current arrayindex for use in the next statement.
6. Assigns the number of the current adjacent polygon (specified by its index in thearray) to the variable “polynum”.
7. Looks up the class of the current adjacent polygon and compares it to the specified
adjacent class. The database specification is in the form
“Object.database[record#].table.field”. (The ”$” at the end of the database
specification indicates that the target
field is a string field.) If the classes
match, then...
8. The “return 1” statement explicitly statesthat the query is true for a polygon
satisfying the above condition, so thepolygon will be selected for display.
9. End of array processing loop.
10. If all adjacent polygons fail the classtest above, the “return 0” statement
states that the query is false.
11. End of polygon adjacency loop.
12. States that the query is false for a
Polygons of class SrD that arepolygon not meeting the initial classadjacent to those of class Sa.
selection condition in statement 1.

page 22

Building and Using Queries

Census Boundaries in TIGER Data
STEPS
! remove the layer
used in theprevious exercise! click the Add
Vector icon andselect Add Vector Layerfrom the dropdown
menu! select the ALAMEDA object
from the TIGER ProjectFile! in the Vector Object
Display controls window,
Census block boundary lines can be selected for
set the Style option in
display or extraction using a query that selects lines
the Lines panel to All
for which the block numbers on the left and rightSame and the Selectoption to By Queryside are not the same. Blocks that have been subdivided retain the same block number, but are identi- ! open the Query Editor
window and choose New
fied by different letters in left and right block suffix
from the File menu
fields; the second set of statements in the sample ! use the Insert
procedures and / orquery selects these boundaries. Finally, blocks inmanual entry to create
adjacent BNA’s can have the same number, so thethe query shown below
final statement selects lines separating different ! click [OK] in the Query
Editor window
BNA’s.
! click [OK] in the Vector
Object Display
Basic_Data.Block_Left <> Basic_Data.Block_Right
Controlsor ( Basic_Data.Block_Left == Basic_Data.Block_Right window
and Basic_Data.BlockSuff_Left <>Basic_Data.BlockSuff_Right )or Basic_Data.BNANum_Left <> Basic_Data.BNANum_Right

Vector objects imported from the US Census
Bureau’s TIGER / Line files are made up of line
segments representing natural and manmade physical features as well as governmental and census
boundaries. The boundaries of census tracts (andequivalent Block Numbering Areas, or BNA’s) andtheir component census blocks usually coincide withother map features and are not explicitly identified
by a Census Feature Class Code (CFCC) like thebasic map features.

The same area withcensus blockboundaries selectedby query.

Raw TIGER / Line data
for a portion of Alameda
County, California.

page 23

Building and Using Queries

Computed Fields from Multiple Records
STEPS! minimize the Display
windows from theprevious exercise! choose Edit / Attribute
Databases from the
TNTmips main menu
! navigate to and selectthe CBSOIILS_LITE object in
the CBSOILSQ Project File! turn on the Polygon
radio button in the
Select window! in the Database Editor
window, right-click on
the box for Class table
and select Edit
Definition! in the definition window
for the Class table, click
the Add Field icon
button! in the field list,
highlight the default
name for the new field
and type ClassArea! on the Field info panel,select Computed fromthe Field Type menu andclick Edit Expression! enter the query shown
below in the Query
Editor window

Scripts can also be used to define the values forcomputed fields in database tables. In many cases
these scripts need only create simple arithmetic
combinations of other fields in the same record. The
task in this exercise is more complex: to create acomputed field in the polygon Class table for
CBSOILS_LITE that shows the total area for each soil
type.
Polygon areas are stored in the POLYSTATS tablefor individual polygons, but we are creating thecomputed field in the Class table, which has one
record for each soil type. The script shown here isdesigned to sum the polygon areas for each soil classand return that sum as the computed field value.
The script defines a numeric variable “sum” that isused to sum the areas in the POLYSTATS.Area field.
This variable must be initially reset to a value of 0.0
for each class. The variable “num” is assigned avalue (for the current soil class) equal to the numberof attached records in the POLYSTATS table. Thisvariable is used to set the number of iterations of theloop that sums the areas.

sum = 0.0
num = SetNum( POLYSTATS[*] )for i = 1 to num begin
sum = sum + POLYSTATS[i].Area
end
return sum! click [OK] in the Query
Editor window! enter 12 in the Width
text box and 2 in thePlaces text box! click [OK] in thedefinition window! double-click on the box
for the Class table toopen itClass table with theadded computed
field showing thesummed area foreach soil type.

page 24! choose Table / Close to close
the Class table

Building and Using Queries

String Expression Fields
A string expression field is a special type ofcomputed field in a database table. The simplest
use of a string expression field is to copy the contents
of a string field in another linked table into the current
table. The expression in that case is simply theappropriate TABLE.FIELD reference. You can also
use string expressions to merge the contents ofseveral string fields into one new field. For example
a table called NAME could have separate fields forfirst and last names. You can use the “+” (add)operator to merge these strings. The expression
NAME.FIRST + “ ” + NAME.LAST
would produce entries with the form “John Doe”.
The expression must include any separating
characters (spaces, commas) in quotes, as shown.
You can use a merged string expression field toprovide text for more informative Data Tips or labels.
The expression you use in this exercise employs thesprintf( ) function, which allows you to format
complex string expressions more easily. The firstfunction argument is a control string (in quotes),
which is followed by the string field references. Eachof the “%s” entries in the control string stands forone of the listed string field references. The control
string can also incorporate inserted text, spaces, or
punctuation.

STEPS! choose File / Open
Database on the
Database Editor window! navigate to and selectthe CBSECT object in the
CB_SECT Project File! turn on the Polygon
radio button in the
Select window! in the Database Editor
window, right-click on
the box for the Sections
table and select Edit
Definition! in the definition window
for the Sections table,
click on the Range fieldin the list andclick the Add
Field icon button! in the field list, highlightthe default name for the
new field and type
SecTwpRng! select String Expression
from the Field Typemenu and click Edit
Expression! enter the query shown
below in the Query
Editor window

sprintf( “Sec %s Twp %s Rng %s”, Sections.Section,
Sections.Township, Sections.Range )Formatted
text createdby the string
expression.

page 25! click [OK] in the Query
Editor window! enter 25 in the Width
text box! click [OK] in thedefinition window! double-click on the box
for the Sections table toopen it! choose File / Close fromboth Database Editor
windows when you havecompleted this exercise

Building and Using Queries

Queries to Check Digitizing Artifacts
Click the Select / Deselecticon button and choose
Select by Query to open thestandard Query Editor
window so that you canenter a query for thatelement type.

Selection queries can also be useful when you arecreating or editing a vector object using the TNTmips
Spatial Data Editor. Complex vector objects cancontain digitizing errors such as line overshoots,
unclosed polygons, and sliver polygons. Many ofthese flaws are not visible except at high zoom
levels, which makes manual checking difficult andtime-consuming. You can speed up the search forpotential topology problems by using selection
queries such as the examples below. The Spatial
Data Editor window provides icons that allow you
to create and apply a selection query for aparticular element type.

OVERSHOOTS
Overshoots are short line segments that incorrectly
extend beyond a line intersection. If you have run theStandard Attributes process for the vector object, you
can use a selection query based on line length to selectall very short lines for examination and possible
removal:LINESTATS.Length < [ your length value ]UNCLOSED POLYGONSIn a vector object containing a network of polygons, a gap
between two lines that should intersect may leave a single
polygon where two separate polygons should exist. Lines
that fail to close a polygon can be found by query because
they have the same polygon on both sides:
Internal.LeftPoly == Internal.RightPoly

SLIVER POLYGONS
Double-tracing polygon boundaries can create
extraneous sliver polygons along the boundary of two
contiguous polygons. Sliver polygons usually have amuch smaller area than the main polygons, and areusually highly elongate (with a high Compactness Ratio).
Use a combined query on the Area and CompactRatio
fields in the POLYSTATS table to select sliver polygons:
POLYSTATS.Area < [ your area value ] or
POLYSTATS.CompactRatio > 3.00

page 26

Building and Using Queries

Pan by Query
A query executed from the Element Selection
window in the Spatial Data Editor or in Spatial Data
Display often selects more than one element. Oneof these selected elements is designated the “active”
element; the active and selected elements arehighlighted in different colors. Editing operations
can be applied to either the active or the selected
elements. You can use the Previous Selected andNext Selected icon buttons on the Element Selection
window to step forward and backward through theselected set of elements, making each one active inturn. The view is automatically repositioned (ifnecessary) to display the current active element. This“pan by query” feature allows you to remain zoomed
in to examine (and perhaps edit) each element while
easily stepping through the selected set.

Selectedelement
Use the Previous Selected
and Next Selected icon
buttons to pan forward or
backward through the set of
selected elements.

Activeelement

STEPS! restore the View andGroup Controls windows! open the Vector Object
Display Controls window! set the Select option in
the Lines panel to All
and click [OK]! open the Options menu
in the Spatial Data
Display View window
and make sure that theShow Scale / Position
option is turned on! type “5000” in the Scaletext box at the bottom of
the View window, andpress <Enter>! click the Show
Details icon
button on the Layer iconrow
! click the Selecticon button forlines! click the Select /Deselect icon
button for lines, andchoose Select by Queryfrom the dropdown
menu! enter the following queryin the Query Editor:
LINESTATS.Length < 50
! click [Apply] on the
Select by Query window! click the Next
Selected icon
buttonThe exercises in this Getting Started booklet have introduced the fundamentalsof the structure and syntax of database queries for use in TNTmips, TNTedit, andTNTview. The query language is a subset of the Spatial Manipulation Language(SML) used in TNTmips, and shares the same syntax. In addition to the
documentation on queries cited on page 2, you may wish to consult the SML
documentation in TNTmips Reference Manual for additional programming hints
to expand your query capabilities.
page 27

Advanced Software for Geospatial Analysis

Building and Using Queries

MicroImages, Inc. publishes a complete line of professional software for advanced geospatial
data visualization, analysis, and publishing. Contact us or visit our web site for detailed product information.
TNTmips TNTmips is a professional system for fully integrated GIS, image analysis, CAD,
TIN, desktop cartography, and geospatial database management.
TNTedit TNTedit provides interactive tools to create, georeference, and edit vector,
image, CAD, TIN, and relational database project materials in a wide variety of formats.
TNTview TNTview has the same powerful display features as TNTmips and is perfect for
those who do not need the technical processing and preparation features of TNTmips.
TNTatlas TNTatlas lets you publish and distribute your spatial project materials on CDROM at low cost. TNTatlas CDs can be used on any popular computing platform.
TNTserver TNTserver lets you publish TNTatlases on the Internet or on your intranet.
Navigate through geodata atlases with your web browser and the TNTclient Java applet.
TNTliteTNTlite is a free version of TNTmips for students and professionals with small
projects. You can download TNTlite from MicroImages’ web site, or you can order
TNTlite on CD-ROM.

Index
arithmetic operations..................................9
assignment statement................................12comments..................................................12comparison operators..................................4
equal to, ==...................................5
greater than, >.................................4
not equal to, <>.............................11
contains......................................13compound queries................................10,19
computed fields....................................24,25
database query, defined..................................3
element selection
adjacent polygons..........................21,22
by query................................................3
island polygons...................................17
multiple attached records....................16
overshoots..........................................26
no attached records.............................15
polygon enclosing point........................20

element selection (continued)
sliver polygons....................................26
undershoots........................................26
Insert Field window...................................16
Insert Operator window...............................5
logical operators (and, or, not)................10,14
opening a query...........................................9
pan by query..............................................27
selecting See element selectionsaving a query........................................9
SetNum function.................................15
string field...........................................7
string expression field..........................25
string variables..........................................13style by script....................................3,18,19
syntax, checking.........................................8
Table[*] expression...................................15
TIGER data...............................................23
variables...............................................12,13

M icro Im a g es, In c.
11th Floor - Sharp Tower
206 South 13th Street
Lincoln, Nebraska 68508-2010 USA
Voice: (402) 477-9554
FAX: (402) 477-9559

page 28 email: info@microimages.com

internet: www.microimages.com

Q
U
E
R
I
E
S