part 2

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 fromfHtmCoverRegion(@californiaRegion)loop join SpatialIndex SI

onSI.HtmID between HtmIdStart and HtmIdEndand SI.type = 'P' join place P on SI.objID = P.HtmID cross join fHtmRegionToTable(@californiaRegion) Poly group by SI.objID, Poly.convexID havingmin(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)

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