SELECT Clause Examples

The examples below examine how Spectrum Spatial delegates queries to the data source provider while supporting the rules of delegation. Both aggregating and non-aggregating queries are provided. In each example, the same spatial 'table' is used. It has the following attributes:
  • city [char25]
  • state [char20]
  • pop [decimal(12,0)]
  • hhinc [decimal(8,0)]
  • area [decimal(6.1)]
  • GEOLOC (Obj)

Example 1:

SELECT * FROM table

This query returns all the attributes from all rows from the table. The * is a wildcard that represents all the attributes. Spectrum Spatial expands the * into a list of all the attributes in the table in the order they are in the table and delegates the entire query to the data source provider. This is because of Rule 1.

Example 2:

SELECT Count(*) FROM table

This aggregation query returns all the attributes from all rows from the table. This satisfies Rule 5 where the aggregation function is in the SELECT clause.

Example 3:

SELECT state, city FROM table 

This query returns the ‘state’ and ‘city’ attributes for all rows in the table. This query is delegated in its entirety to the data source provider because of Rules 2 and Rule 4.

Example 4:

SELECT UPPER(city) FROM table

This example provides the first look at a query that is only partially pushed down to the data source provider. Rule 6 states that Spectrum Spatial cannot delegate operators or functions to a data source provider. This means Spectrum Spatial must execute the UPPER function. To do that it needs the ‘city’ attribute from ‘table’ and constructs the following query to be delegated to the data provider:

SELECT city FROM table

The following query is executed by Spectrum Spatial against the results of the query from the data provider:

SELECT UPPER(city) FROM <results of the delegation> 

Example 5:

SELECT city, pop/area AS proportion FROM table

In this example the user wants 'pop' divided by the 'area' and 'city' returned. Rule 6 does not permit the '/' to be delegated. Spectrum Spatial also leveragesRule 1 to get the attributes needed to satisfy the query and constructs the following to be delegated to the data provider:

SELECT city, pop, area FROM table
The following query is executed by Spectrum Spatial against the results of the query from the data provider. The results are returned using an alias. If alias was not provided in the original query the results would have a computer-generated unfriendly name.
SELECT city, pop/area AS proportion FROM <results of the delegation>
Example 6:
SELECT city, avg(hhinc) FROM table WHERE state='ny' GROUP BY city ORDER BY city

This query returns the ‘city' attribute for all rows in the table and the average household income (hhinc). This query is delegated in its entirety to the data provider because of Rule 2, Rule 3 and Rule 5.

Example 7:
SELECT city, avg(hhinc) FROM table WHERE pop => 50000 GROUP BY city ORDER BY city

In this example, the data source provider supports the avg() function but does not support the => operator in the WHERE clause. The query that the data source provider acts on is:

SELECT city, hhinc FROM table 

And Spectrum Spatial would act on this:

select city, avg(hhinc) FROM <results from the delegation> WHERE pop => 50000 GROUP BY city ORDER BY city
Example 8:
SELECT city, avg(hhinc) FROM table WHERE pop => 50000 GROUP BY city ORDER BY city

In this example, the data source provider supports the avg() function, the => operator in the WHERE clause but does not support GROUP BY. The query that the data source provider acts on is:

SELECT city, hhinc FROM table WHERE pop => 50000 

And Spectrum Spatial would act on this:

select city, avg(hhinc) FROM <results from the delegation> WHERE pop => 50000 GROUP BY city ORDER BY city