Query Dataset

Queries items from one or more datasets within the SWD and creates a new overlay from the results of a SQL query.

Note: Query Dataset is only compatible with data that has been added in to Cadcorp SIS Desktop 9.1 as a form of database. It cannot query .bds/.shp files etc. even if they contain point data. Data added through PostGIS, SQL, Oracle, Cadcorp View Points (both file-based and .csv) and OS Vector Data are all compatible.

  • Query Dataset uses SQLite, and supports most normal SQLite SELECTsyntax (except DDLs) and window functions
  • Query Dataset can be used to query other Query Datasets to further refine a search.
  • Query datasets are dynamic, ie they will respond to changes in the database when the Regenerate command is run.

Click Add Overlay > Analysis > Query Dataset.

A View Points dataset named Crimes is used here as an example:

The Crimes overlay has the following Schema columns:

  • Date1stCrime
  • EarliestDate
  • LatestDate
  • Occurrence

The SQL command must be a SELECT statement; you cannot use UPDATE, INSERT, DELETE etc.

It is not necessary to use the semicolon character at the end of the SQL statement. You do not need to add the $, #, & character before the column name as you would with the query builder.

Enter SELECT * FROM Crimes WHERE Date1stCrime > 39850 in the Query Dataset SQL dialog pane and click Validate.

The following Add Overlay confirmation will be displayed:

If an error is made in the SQL syntax an error message will be displayed. Here is an example: the incorrect statement SELECT * FOM Crimes WHERE Date1stCrimme > 39850 has been entered.

Here you can see FOM has been typed instead of FROM. The error message highlights the approximate location of the first error it finds in the statement, any subsequent errors are not highlighted.

The check is only made to ensure the SQL syntax is valid it will not check that the column/table names etc. are entered correctly. In this example the incorrect name of Date1stCrimme would not be reported.

In this case an empty Query Dataset will be returned.

Option Description

Overlay Configuration Name

Enter a name for the resulting overlay. If this field is left blank the new overlay will be called Query.

Theme

Select a theme from the drop-down if required.

More Properties

Click More Properties to set the Coordinate system, Feature Table, etc.

Click the Properties button to display the Query Dataset properties.

Click Finish.

The new overlay in this example LatestCrimes is displayed: