Querying Spatial Database Spatial Overlays

SIS Desktop 9 provides users with a number of ways to query data in an overlay. Each method has its own benefits depending on the type of overlay being queried.

Database Query Builder

The Database Query Builder enables fast spatial and attribute searches when querying Oracle, PostGIS, Microsoft SQL Server or FDB spatial Overlays created using dynamic mode. This means that queries are handled by the database server, not the client, and returns a much quicker result than would be achieved using the standard Query Builder (Query Builder (Home > Map).

If Query Builder [Home-Map] is used on database overlays the entire overlay is loaded into Cadcorp SIS Desktop and each item checked to see if it passes or fails the spatial and textual query. In the case of a large database a query by this method could take a long time.

Note: The functionality of the Database Query Builder is available through the API using the CreateFilteredOverlay method.

Database Query Builder benefits include:

  • The most efficient way to query data on spatial database overlays.
  • Spatial and attributes queries are performed on the database server not the client.
  • The performance of queries are only marginally affected by the size of the dataset.
  • New layers can be created from the result set.
  • Existing overlays can be modified to only display the results of the query.
  • Attribute and spatial queries can be combined.
  • If the existing overlay is modified it retains its original status (if editable it would remain editable).

Issues to be aware of:

  • The Database Query Builder is only available for Oracle, PostGIS, Microsoft SQL Server or FDB dynamic overlays.
  • Only one spatial overlay at a time may be queried.
  • If a new overlay is created from the result set it is given a status of hittable, this can be changed to editable.
  • Features added to the overlay that fall outside the original test geometry extents will not be displayed, features added inside the original test geometry extents will be displayed.

Using the Database Query Builder

The Database Query Builder is accessed from the Maps Control Bar by clicking the right-hand mouse button on the database overlay and selecting Dataset/Database Query Builder.

The Query dialog will be displayed:

Enter the details of the spatial query you wish to carry out.

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

On completion of the dialog click Next.

The Query Results dialog will be displayed:

Select from the three Action - Overlay for query results options:

Filter - Use the attribute and/or spatial query in the SQL "WHERE" clause of the overlay's dataset. The query will be dynamic, therefore as an item's properties or geometry change, it may drop out of, or be included in the query.

Copy - Create a new overlay by making a copy of the dataset using the attribute and/or spatial query as a SQL "WHERE" clause. The underlying data will not be copied, just drawn again in another overlay.

Replicate - Copy the query results into a new 'Internal' overlay, for further processing. The connection to the source data will be lost.

Click Finish.

Spatial Filter

Spatial Filter (Create > Miscellaneous) is a quick method for creating a spatial filter based on a selected feature.

Benefits:

  • Allows queries to be run on expressions added to the schema of a database spatial overlay. The time taken for queries depends on the size of the dataset.
  • Users can select which overlays to create the spatial filter on. The overlay does not need to be visible.

Issues to be aware of:

  • For spatial database dynamic overlays if the spatial extents of the possible result set is within the map window extents the query will perform relatively quickly. If the spatial extents go beyond the map window extents, Cadcorp SIS Desktop will need to retrieve the data before it performs the query. This will use additional memory on the client. The Database Query Builder is the preferred method of querying in this instance.
  • Best suited to spatial database Cached and One Off Import overlays.
  • Queries are run on the client machine.
  • If used for dynamic mode overlays any data required for the query will be retrieved from the database before the query starts this will use additional memory on the client.
  • Attribute filters can not be applied by this command.

TIP: See Spatial Filter for a full description of this command.