|WHERE Clause Examples for Delegation / AND Operator Examples|
According to the rules for delegating AND operations to the data source, if a data source does not support AND, only one operation can be delegated. To determine which operation to delegate Spectrum Spatial evaluates all operations in the WHERE clause and ranks them high to low. The operation with the highest score will be delegated the data source. Spectrum Spatial will process the remaining operations client side.
The following is the order of precedence for delegation:
|Distance||MI_Distance(..) < 5|
|Geometry||where obj Intersects MI_Buffer(MI_Point(10.5,51,'epsg:4326'), 200, 'mi', 'Spherical', 12)|
|Envelope_Intersects||where obj EnvelopesIntersect MI_Box(-5,42,33,57,'epsg:4326')|
|Key||MI_Key = 5|
|Comparison attributes||pop > 10000, city = "Austin"|
|InList||city In ("FL", "OH", "NY")|
|Is Null||city Is Null|
|Like||city Like "%City"|
|Between||pop Between 5000 and 50000|
|Logical operators OR, NOT||where NOT MI_Envelope(obj) within MI_Box(-180, -90, 180, 90, 'EPSG:4326')|
Spectrum Spatial will always choose to delegate a spatial operation over a non-spatial operation.
Before the scoring process, Spectrum Spatial turns the WHERE clause into the most efficient query that could be delegated to data source if the data source supported everything. Spectrum Spatial then scores the operations in that version of the clause. The clause is reconstructed to match what the data source supports and sends the highest scoring operation to the data source. See Example 2.
In the case of an equal score, which can happen when two comparison operators are in the WHERE clause, the left-most operation in the clause will take precedence. It is up to the query writer to know the data well enough to decide the order of operations in a WHERE clause. For example, if one column is indexed, this would be a more efficient operation to be delegated to the data source over a non-indexed column. Spectrum Spatial does not examine the data to make this decision.