AND Operator Examples

The following examples use the AND operator in queries and show how the elements can be delegated to the data source provider.

Example 1:

SELECT Obj FROM table WHERE pop>10000 AND city='Austin' AND hhinc<75000 

If the data source provider supports AND, > , =, and <, the entire query is delegated to the data source provider.

If the data source provider supports AND and some of the other operators, those operators are delegated and the rest is processed by Spectrum Spatial.

If the data source provider does not support > or < then the following is delegated to the data source provider:

SELECT pop,hhinc FROM table WHERE city='Austin

Spectrum Spatial uses the results from the delegated query to evaluate the original WHERE clause.

SELECT obj FROM <results of the delegation> WHERE pop>10000 AND city='Austin AND hholdinc<75000

Example 2:

If the data source provider does not support AND, only one operation can be delegated according to the rules. To determine which operation to delegate, Spectrum Spatial scores the operators and sends the one with the highest score to the data source provider. The scoring is actually done on a theoretical rewrite of the query that could be run if the data source supported every operation.

In addition to not supporting AND, the data source in this example does not support Contains. It does, however support Envelope_Intersects.

SELECT city FROM table WHERE Obj Contains <geom> AND pop > 5000 

is converted to:

SELECT city FROM table WHERE Obj Envelope_Intersects <geom> AND pop > 5000
Spectrum Spatial scores Envelope_Intersects higher than the logical operator >. This is also the case where a spatial operation is delegated over a non-spatial operator. So the following is delegated to the data source:
SELECT city FROM table WHERE Obj Envelope_Intersects <geom>

Spectrum Spatial evaluates the original query using the result of the above query:

SELECT city FROM <results of the delegation> WHERE Obj contains <geom> AND pop > 5000