Query Builder

Query Builder constructs a data query to find items in the table view. You can look for:

  • items with specific values for one or more attributes (e.g. house numbers in a certain range, or only those items which are Road Centrelines).
  • items whose shape or location has a spatial relationship with the currently selected items (e.g. inside or overlapping)
  • a combination of the above

When it has found matching items, the Query Builder can either:

  • create a new overlay showing the results of the query.
  • set the overlay filter or spatial filter so that items matching the query are included

TIP: You can also have the query set a filter or spatial filter; this means you can use the items found by the query as the basis for further operations. See Spatial testing: Spatial Filter.

Note: For the fastest query responses on database overlays use the Run the query using: Database filtering option in the Query Overlay dialog. This uses the database for performing the query. See Querying Spatial Database Spatial Overlays

  1. Before you begin, select an item or items if you want to look for other spatially-related items.
  2. Select Table > Sort and Filter > Query Builder.
  3. You can also open the Query Builder via Home > Map or from the Overlay’s local menu.
  4. Select the overlay you wish to query in the Query Overlay dialog.

  5. You can choose to run the query using SIS filtering or Database filtering. If you select Database filtering, remember to add in wildcard characters in the Like query, else there will be no output returned.

    6. Note: If you use an overlay’s local menu or you have only one overlay open this dialog is not displayed. Also if Query (Table > Sort & Filter) is used the overlay is already selected therefore this dialog will not be displayed.

  6. Click Next to open the Query options.
  7. Enter the details of the attribute query and/or spatial query you wish to carry out.

    8. Note: The Spatial Query against the currently selected item tickbox and options will only be available if items are selected on the overlay.

Attribute Query

  1. The Function keypad

    Examples of the use of the keys:

     Less than

    Example Query: CALCULATED_AREA_VALUE& < 1000

    Returns all areas less than 1000 in current units.

     Equal to

    Example Query: CALCULATED_AREA_VALUE& = 1000

    Returns all areas equal to 1000 in current units.

     Greater than

    Example Query: CALCULATED_AREA_VALUE& > 1000

    Returns all areas greater than 1000 in current units.

     Less than or equal to

    Example Query: CALCULATED_AREA_VALUE& <= 1000

    Returns all areas less than or equal to 1000 in current units.

     Not equal to

    Example Query: CALCULATED_AREA_VALUE& <> 1000

    Returns all areas not equal to 1000 in current units.

     Greater than or equal to

    Example Query: CALCULATED_AREA_VALUE& >= 1000

    Returns all areas greater than or equal to 1000 in current units.

    The And, Or, Like and In keys

    Using Counties.shp as an example:

     And

    Example Query: NAME$ = "Highland" And COUNTY_ID# = 5

    Returns Highland, providing the name (Highland) and the county id match:

     

     Or

    Example Query: NAME$ = "Cornwall" Or NAME$ = "Isle of Wight" Or NAME$ = "Kent"

    Returns Cornwall, Isle of Wight and Kent:

     

     Like

    Example Query: NAME$ Like "Isle"

    Note: If the Database filtering option has been chosen in the Query Overlay dialog it is necessary to add wildcard characters, otherwise the query will not return any output. Therefore, in this example the Like query should be ‘NAME Like "%Isle%"’ in order to return the same output, also note the $ character is omitted due to using database data.

    Returns names containing Isle, i.e. Western Isles, Isle of Man, Isle of Anglesey, Isle of Wight.

     In

    The In function reduces the need to use multiple OR functions.

    Example Query: NAME$ In("Northhamptonshire","West Lothian","Isle of Man","Derby" )

    Returns Northhamptonshire, West Lothian, Isle of Man and Derby:


    Sample button

    Click the Sample button to display the first number of item(s), as selected in the drop-down in a range of 1 to 1000, for the selected column.

     

    Typical values - This pane shows the values that SIS Desktop 9 will scan the overlay for in the selected column.

    Query - Enter the query you want to use to find items. You can build simple queries using the block of function keys above.

    Spatial Query against the currently selected item
    The currently selected item(s) must ... - Only select items that pass a geometric test with the current item. The tests use the OGC "Relate" function. This measures the dimensions of the nine intersections formed between the Interior, Boundary and Exterior of the two items being compared. The different tests have different rules for which dimension matrices are valid.                

See the Geometry Tests topic for a further explanation of the OGC specified method for computing spatial relationships between geometries.

all... - Tick if all of the selected items have to pass the test, or untick if any one will be enough.

The following is an example of the dimensionally extended nine-intersection matrix (DE-9IM) for the case where (a) and (b) are two Polygons that overlap.


contain - The interior of the found item must be strictly inside the current item.

"T****F*"

cross - If the items are LineStrings, then they must intersect without being tangential. Otherwise, their interiors must intersect, with the current item going outside the found item.

LineStrings: "0********"
Other: "T*T******"

touch - The items interiors must be disjoint, AND their boundaries must intersect. 

"FT*******" or "F**T*****" or "F***F****"

be disjoint from - The items must be completely separate, with daylight between them. 

"FF*FF****"

be equal to - The two items' geometry must be the same. 

"TFFF*FFF*"

be within - The interior of the current item must be strictly inside the found item. 

"T*F**F***"

intersect - The items must not disjoint, so they must have a point in common. This is the fastest, and most common test. 

not "FF*FF****"

overlap - If the two items are LineStrings, then they must be tangential, and neither should contain the other. Otherwise, their interiors must intersect, with neither containing the other.

LineStrings: "1*T***T**"
Other: "T*T***T**"

be crossed by - If the items are LineStrings, then they must intersect without being tangential. Otherwise, their interiors must intersect, with the found item going outside the current item. 

LineStrings: "0********"
Other: "T*****T**"

...the... - ...of the found items

origin - This is the origin of the Items to be queried. This is normally (but not always) the approximate centre of a Polygon or the starting point of a LineString.

extents - This means the imaginary bounding box around the item’s geometry using the minimum and maximum x/y values of the item.

geometry - This is the outline of the Item. This is the slowest but most accurate mode.

The above example shows a query build that filters the overlay to display data that intersects the currently selected item.