use Spatial
go
--=======================================================================================
-- EXAMPLES FROM PAPER
--=======================================================================================
-- The Table Valued Function Paper  
-----------------------------------------------------------------------------------------
-- Examples from 1. Find Points near point: find towns near a place. 
select dbo.fHtmMagic(0, 0)
select * -- find a HTM cover 100 NM around Baltimore
from fHtmCoverCircleLatLon( 39.3, -76.61, 100) 
go
declare @Lat float, @Lon float
select @Lat = Lat, @Lon = Lon 
from Place 
where Place.PlaceName = 'Baltimore' 
  and State = 'MD' 
select ObjID, dbo.fDistanceLatLon(@Lat,@Lon, Lat, Lon) as distance
from SpatialIndex join fHtmCoverCircleLatLon(@Lat, @Lon, 100)  
   On HtmID between HtmIDStart and HtmIDEnd           -- coarse test
   and Type = 'P'
   and dbo.fDistanceLatLon(@Lat,@Lon, Lat, Lon) < 100 -- fine test
	order by distance asc
go
select ObjID, distance -- find a HTM cover 100 NM around Baltimore
from fHtmNearbyLatLon('P', 39.3, -76.61, 100) 
go
-----------------------------------------------------------------------------------------
-- Examples from 2. Find places inside a box. 
declare @radius float
set @radius = dbo.fDistanceLatLon(41,-109.55,37,-102.05)/2
select * 
from Station 
where StationNumber in (
    select ObjID 
     from fHtmCoverCircleLatLon(39, -105.55, @radius) join SpatialIndex 
		on HtmID between HtmIDStart and HtmIDEnd
 	   and Lat between 37 and 41
 	   and Lon between -109.05 and -102.048
       and Type = 'S')
OPTION(FORCE ORDER)
-----------------------------------------------------------------------------------------
-- Region examples. 
select count(*) from fHtmCoverRegion('CIRCLE LATLON 39.3 -76.61 100')
--1 
select count(*) from fHtmCoverRegion('CIRCLE CARTESIAN 0.1792 -0.7528 0.6334 100')
--1 
select count(*) from fHtmCoverRegion('RECT LATLON 37 -109.55  41 -102.05')
-- 4
select count(*) from fHtmCoverRegion('CHULL LATLON 37 -109.55 41 -109.55 41 -102.051 37 -102.05')
-- 4
declare @convex varchar(max)
set @convex     =   'CONVEX   -0.17886 -0.63204 -0.75401 0.00000 '
                           + '-0.97797  0.20865 -0.00015 0.00000 ' 
                           + ' 0.16409  0.57987  0.79801 0.00000 '  
                           + ' 0.94235 -0.33463  0.00000 0.00000 '
select count(*) from fHtmCoverRegion(@convex)
-- 4
select * from fHtmCoverRegion ('REGION CONVEX CARTESIAN 0.7 0.7 0.0 -0.5  CIRCLE LATLON 18.2 -22.4 1.75')
-- 83 rows 
-------------------------------------
go
-----------------------------------------------------------------------------------------
-- Colorado example -- Rectangle
select S.* 
from  ( select ObjID 
         from fHtmCoverRegion('RECT LATLON 37 -109.55  41 -102.05') 
              loop join SpatialIndex
		 on HtmID between HtmIDStart and HtmIDEnd
 	        and Lat between 37 and 41
 	        and Lon between -109.05 and -102.048
            and Type = 'S') as G 
  join Station S on G.ObjID = S.StationNumber
OPTION( force order) 
--1030 rows
go
-----------------------------------------------------------------------------------------
-- Colorado example -- Convex Hull
select S.* 
from  ( select ObjID 
         from fHtmCoverRegion(
            'CHULL LATLON 37 -109.55 41 -109.55 41 -102.05 37 -102.05') 
              loop join SpatialIndex
		 on HtmID between HtmIDStart and HtmIDEnd
 --	        and Lat between 37 and 41
 --	        and Lon between -109.05 and -102.048
            and Type = 'S') as G 
  join Station S on G.ObjID = S.StationNumber
OPTION( force order)
-- 1030 rows
go
-----------------------------------------------------------------------------------------
-- Utah example
declare @utahRegion varchar(max)
set @utahRegion = 'region '  
    + 'rect latlon 37 -114.0475  41 -109.0475 ' -- main part
    + 'rect latlon 41 -114.0475  42 -111.01  '  -- Ogden & Salt Lake.

select S.* 
from  ( 
   select ObjID 
   from fHtmCoverRegion(@utahRegion) 
      loop join SpatialIndex
      on HtmID between HtmIDStart and HtmIDEnd
      and (((     Lat between 37        and      41)  -- diamond test
             and (Lon between -114.0475 and -109.04)) -- are we inside 
          or ((   Lat between 41        and      42)  -- one of the two
             and (Lon between -114.0475 and -111.01)) -- boxes?
	     )
      and Type = 'S' ) as G   
  join Station S on G.ObjID = S.StationNumber  
OPTION( force order)
-- 662 rows
go
-----------------------------------------------------------------------------------------
-- California example
declare @californiaRegion varchar(max)
set @californiaRegion = 'region '
                      + 'rect  latlon 39    -125 '    -- nortwest corner
                                  +  '42    -120 '    -- center of Lake Tahoe cut
                      + '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  P.State, SI.ObjID , HS.convexID, min(SI.x *HS.x + SI.y * HS.y + SI.z * HS.z - HS.D)
from fHtmRegionToTable(@californiaRegion) HS cross join SpatialIndex SI join Place P
 on SI.ObjID = P.HtmID
group by all P.State, SI.ObjID , HS.convexID 
 OPTION( force order)
select  *
from Station 
where  StationNumber in 
	(select StationNumber
	from fHtmCoverRegion(@californiaRegion) 
		loop join SpatialIndex
		 on HtmID between HtmIDStart and HtmIDEnd
		/* and <diamond test> */
		and Type = 'S'    
		join Station S on  ObjID = S.StationNumber  )  
OPTION( force order)
-- the full query, done for Places. 
select * 
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)
-- 885 rows
-- 247 seconds with no option
--   0.249 seconds with OPTION (FORCE ORDER)
select *				   -- Get all the California River Stations
from Station
where StationNumber in     -- that are inside the region
 (select objID 
  from fHtmRegionObjects(@californiaRegion,'S'))  
select *				-- Get all the California Cities
from Place
where HtmID in			-- that are inside the region
 (select objID 
 from fHtmRegionObjects(@californiaRegion,'P'))  
go
--------------------------------------------------	
-- Appendix
set nocount on
go
-----------------------------------------------
-- fHtmVersion()
print dbo.fHtmVersion()
-- returns 'Spherical-HTM <version> <build date>'
-----------------------------------------------
-- Generating HTM keys: fHtmLatLon (Lat, Lon) returns HtmID 
update Place set HtmID = dbo.fHtmLatLon(Lat,Lon)
-----------------------------------------------
-- LanLon to XYZ: fHtmLatLonToXyz (Lat,Lon) returns Point (x, y, z)
-- and XYZ to LatLon: fHtmXyzToLatLon(x,y,z) return Point(Lat, Lon)
Select LatLon.Lat,LatLon.Lon     
from fHtmLatLonToXyz(37.4,-122.4) as XYZ cross apply 
     fHtmXyzToLatLon(XYZ.x, XYZ.y, XYZ.z) as LatLon
-- returns 37.4	-122.4
-----------------------------------------------
-- Viewing HTM keys: fHtmToString (HtmID) returns HtmString  
print 'SQL Server development is at: ' + 
 			dbo.fHtmToString(dbo.fHtmLatLon(47.646,-122.123))
-- returns: ' N132130231002222332302' .
-----------------------------------------------
-- HTM trixel Centerpoint:  fHtmToCenterpoint(HtmId) returns Point (x, y, z)
select * from fHtmToCenterPoint(dbo.fHtmLatLon(47.646,-122.123)) 
--- returns -0.358236487370576	-0.57057051526897	0.738996553592839
-----------------------------------------------
-- HTM trixel corner points:  fHtmToCornerpoints(HtmId) returns Point (x, y, z)
-- Returns table of the three Cartesian corner points of the HTM  trixel  
 select * from fHtmToCornerPoints(dbo.fHtmLatLon(47.646,-122.123)) 
/* returns
x                      y                      z
---------------------- ---------------------- ----------------------
-0.358236016409325     -0.570571524945788     0.738996002335747
-0.358235895038649     -0.570569776098563     0.73899741143572
-0.358237550662952     -0.570570244761281     0.738996247005396
*/
-----------------------------------------------
-- HTM trixel corner points:  fHtmToCornerpoints(HtmId) returns Point (x, y, z)
-- Computing distances: fDistanceLatLon(Lat1, Lon1, Lat2, Lon2)  returns distance  
-- in nautical miles (arc minutes) between two points. 
declare @Lat float, @Lon float
select @Lat = Lat, @Lon = Lon 
from  Place 
where PlaceName = 'Baltimore' and State = 'MD' 
select PlaceName,  dbo.fDistanceLatLon(@Lat,@Lon, Lat,  Lon) as distance
from Place
-- returns a table of 22,993 places and distances. 
-----------------------------------------------
-- Finding nearby objects: fHtmNearbyLatLon(Type, Lat, Lon, radius)  returns SpatialIndexTable
-- this finds things within 10 nautical miles of Baltimore
select distance, Place.*
from fHtmNearbyLatLon('P', 39.3, -76.6, 10) I join Place 
on I.ObjID = Place.HtmID
order by distance
-- returns 31 rows.
-----------------------------------------------
-- Finding the nearest object:  fHtmNearestLatLon(Type, Lat, Lon) returns SpatialIndexTable
-- Baltimore is nearest to Baltimore. 
select distance, Place.*
from fHtmNearestLatLon('P', 39.3, -76.6) I join Place 
on I.ObjID = Place.HtmID
go
-----------------------------------------------
-- Circular region HTM cover:  fHtmCoverCircleLatLon(Lat, Lon, radius)  
-- Returns a trixel table covering the designated circle. 
declare @answer nvarchar(max)
declare @Lat float, @Lon float
select @Lat = Lat, @Lon = Lon 
from Place 
where Place.PlaceName = 'Baltimore' 
  and State = 'MD' 
set @answer = ' using fHtmCoverCircleLatLon() it finds:      '
select @answer = @answer  
 	   + cast(P.PlaceName as varchar(max)) + ', ' 
 	   + str( dbo.fDistanceLatLon(@Lat,@Lon, I.Lat, I.Lon) ,4,2) 
 	   + '  arcmintes distant.' 
from SpatialIndex I join fHtmCoverCircleLatLon(@Lat, @Lon, 5)  
   On HtmID between HtmIDStart and HtmIDEnd -- triangle test
   and Type = 'P'					  -- it is a place
   and dbo.fDistanceLatLon(@Lat,@Lon, Lat, Lon) 
                         between 0.1 and 5  -- diamond test
   join Place P on I.ObjID = P.HtmID
    order by dbo.fDistanceLatLon(@Lat,@Lon, I.Lat, I.Lon) asc
print 'The city within 5 arcminutes of Baltimore is: ' 
    + 'Lansdowne-Baltimore Highlands, 4.37 arcminutes away'
print @answer
-----------------------------------------------
-- General region HTM cover: fHtmCoverRegion(region) returns trixelTable 
-- The Colorado query returns 1030 rows.
select S.* 
from  ( select ObjID 
         from fHtmCoverRegion('RECT LATLON 37 -109.55  41 -102.05') 
              loop join SpatialIndex
		 on HtmID between HtmIDStart and HtmIDEnd
 	      and Lat between 37 and 41
 	      and Lon between -109.05 and -102.048
            and Type = 'S') as G 
  join Station S on G.ObjID = S.StationNumber
OPTION( force order) 
-----------------------------------------------
-- General region to table representtion of convexes of halfspaces
-- fHtmRegionToTable(region) returns RegionTable 
-- The Colorado query returns 1030 rows.
select *
from fHtmRegionToTable('RECT LATLON 37 -109.55  41 -102.05') 
/* returns
convexID             halfspaceID          x                      y                      z                      D
-------------------- -------------------- ---------------------- ---------------------- ---------------------- ----------------------
0                    0                    0                      0                      1                      0.601815023152048
0                    1                    -0.977965791127952     0.208765206352684      0                      0
0                    2                    0.942349830759932      -0.334629341909413     0                      0
0                    3                    0                      0                      -1                     -0.656059028990507
*/
go
-----------------------------------------------
-- Region simplification: fHtmToNormalFormString(region) returns regionString  
-- of the form:  REGION {CONVEX {x y z d}* }*.  
print dbo.fHtmRegionToNormalFormString('RECT LATLON 37 -109.55  41 -102.05')
-- returns REGION CONVEX CARTESIAN    0                  0                 1                  0.601815023152048   
--                                   -0.977965791127952  0.208765206352684 0                  0   
--                                    0.942349830759932 -0.334629341909413 0                  0 
--                                    0                  0                -1.000000000000000 -0.656059028990507 
------------------------------------------------
-- Testing fHtmRegionObjects()
select *			-- find Colorado places.
from Place    
where HtmID in
 (select objID 
 from fHtmRegionObjects('RECT LATLON 37 -109.55  41 -102.05','P'))
 
-----------------------------------------------
-- General region diagnostic: fHtmRegionError(region ) returns message 
print dbo.fHtmRegionError ('RECT LATLON 37 -109.55  41 -102.05') -- OK -- which is correct result
print dbo.fHtmRegionError ('RECT LATLON 37 -109.55  41 ') -- NOT OK -- which is correct result
-- correctly returns "Not enough arguments for this specification"
 
  
