Spatial Queries part 2

Query with complex regions

Sometimes the region is not a simple circle, so you must use the text interface to define one. Let's look at a probable approximation of California as a union of a rectangle and a polygon defined as the convex hull of some well-identifiable points

```declare @californiaRegion varchar(max)
set @californiaRegion = 'REGION '
+ 'rect  latlon 39  -125 ' -- nortwest corner
+ '42    -120 '            -- center of Lake Tahoe
+ 'chull latlon 39 -124 '  -- Pt. Arena
+ '39    -120 '    -- Lake  tahoe.
+ '35    -114.6 '  -- start Colorado River
+ '34.3  -114.1 '  -- Lake Havasu
+ '32.74 -114.5 '  -- Yuma
+ '32.53 -117.1 '  -- San Diego
+ '33.2  -119.5 '  -- San Nicholas Is
+ '34    -120.5 '  -- San Miguel Is.
+ '34.57 -120.65 ' -- Pt. Arguelo
+ '36.3  -121.9 '  -- Pt. Sur
+ '36.6  -122.0 '  -- Monterey
+ '38    -123.03 ' -- Pt. Rayes
```

.

```select PlaceName from Place
where HtmID in
(select distinct SI.objID
from fHtmCoverRegion(@californiaRegion)
loop join SpatialIndex SI  on  SI.HtmID between HtmIdStart and HtmIdEnd
and SI.type = 'P'
join place P on SI.objID = P.HtmID
cross join fHtmRegionToTable(@californiaRegion) Poly
group by SI.objID, Poly.convexID
having min(SI.x*Poly.x + SI.y*Poly.y + SI.z*Poly.z - Poly.d) >= 0)
OPTION (FORCE ORDER)
```

The expression inside the min() is a way to test whether or not a point is inside the region. Recall, that a point is in the region if it is inside at least one convex, and it inside a convex if it is inside all the halfspaces. The "coarse filter" is performed by the condition on the join between the covermap and the SpatialIndex. A query with identical results, but without the coarse filtering of the covermap is expressed as

```select PlaceName  from Place
where HtmID in
(select distinct SI.objID
from SpatialIndex as SI, fHtmRegionToTable(@californiaRegion) as Poly
group by SI.objID, Poly.convexID
having min(SI.x*Poly.x + SI.y*Poly.y + SI.z*Poly.z - Poly.d) >= 0)
OPTION (FORCE ORDER)```

Back ... Next ...

Last update May 29, 2007 György Fekete version 3.1.2