SQL Command Examples

Use the SQL console to work with SQL commands inside Cadcorp SIS Desktop 9.1.

To enable the console, click View > Show/Hide > SQL.

Example 1 - Count items from an overlay

In this example the following will be shown:

SELECT COUNT(column_name) FROM table_name WHERE condition;

Examples:

The record(s) will list the schema column values:

Example 2 - Count occurrences of a value and sort

Select owningstation,count(*) as c from incidents group by owningstation order by c:

Example 3 - Constructing items

Note: Ensure .mode add has been enabled in order to see the items that have been created.

Making a spider diagram between incidents and their owning stations:

The output diagram (output in green):

Spider diagram, all stations, grouped by station (outputs multi-linestrings)

To add found items to current editable overlay:

.mode add

Select st_collect(st_makeline(stations.sis_geometry,incidents.sis_geometry)) from incidents join stations on stations.baseid=incidents.owningstation group by stations.baseid;

The Attribute Table Dataset loads data from the selected database as Empty Items, thus allowing non-spatial data to be used in SQL JOIN statements in the SQL console, the Query Dataset, etc. You will need to add the columns that you want to the SELECT list.

For example:

SELECT Oracle_CENSUSUK11DATA.POPY,OA2011_Greater_London.* 
FROM Oracle_CENSUSUK11DATA JOIN OA2011_Greater_London 
ON Oracle_CENSUSUK11DATA.OACODE = OA2011_Greater_London.OA11CD;

1. Output mode should be set to 'add' beforehand in the SQL console.

2. To join the schema column from the Attribute Table overlay, the Spatial overlay must be set to current (else an internal overlay will be created).

---