use Spatial
go
select dbo.fHtmMagic(1, 1)
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 count (stationNumber)
from fHtmCoverRegion(@californiaRegion) 
   loop join SpatialIndex
   on HtmID between HtmIdStart and HtmIdEnd
   /* and <careful test> */
   and type = 'S'   
 join Station S on  objID = S.StationNumber 
OPTION( FORCE ORDER) 
-- 2908 , 2398
----------------------------------------------------------------------
select count (*)			-- Get all the California River Stations
from Station
where stationNumber in	-- that are inside the region
 (select ObjID 
  from fHtmRegionObjects(@californiaRegion,'S')) 
-- 1953
----------------------------------------------------------------------
select count(*) 
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) 
-------------------------------
select count(*)			-- Get all the California Cities
from Place
where HtmID in		-- that are inside the region
 (select ObjID 
 from fHtmRegionObjects(@californiaRegion,'P'))
