/*=====================================================================

  File:      TestScript.sql for Spatial Sample
  Summary:   Demonstrates how to use the spatial sample, and performs a little smoke testing.
  Date:	     August 19, 2005

---------------------------------------------------------------------
  This spatial search library was developed by Alexander Szalay, Robert Brunner, 
  Peter Kunszt, and Gyorgy Fekete at the Department of Physics and Astronomy, 
  The Johns Hopkins University, in collaboration with Jim Gray, Microsoft Research.
  Details of the algorithms can be found at http://www.sdss.jhu.edu/htm/.
 
======================================================= */

--------------------------------------------------------------
-- This script exercises the HTM library  
-- using the sample Spatial database and 
-- using the examples from the Table Valued Function.doc in the Project's \doc directory.
-- 
-- It assumes the Spatial database has been created, or attached with
-- somthing like:
-- exec sp_attach_single_file_db @dbname = 'spatial', 
--                               @physname = 'c:\<whatever>\SPHHDM.mdf'
--		fHtmVersion 
--		fHtmLatLon   
--		fHtmToString 
--		fHtmXyzToLatLon
--		fHtmLatLonToXyz 
--      fDistanceLatLon  
--      fHtmToCenterPoint  
--      fHtmToCornerPoints 
--		fHtmCoverCircleLatLon
--      fHtmNearbyLatLon
--      fHtmNearestLatLon
--		fHtmCoverCircleLatLon
--		fHtmCoverRegion
--		fHtmRegionToNormalFormString
--		fHtmRegionToTable
--		fHtmRegionError
------------------------------------------------------------------
go
---------------------------------------------------------------------
-- use the sample spatial database
use Spatial
go
exec sp_configure 'clr enabled', 1
go
reconfigure
go
set nocount on
go
---------------------------------------------------------------------
-- assure that the HTM routines are installed. 
-- note that doc\spHtmCsharp.sql re-installs these routines if fresher ones are wanted. 
print '----------'
print 'test fHtmVersion()'
declare @version varchar(max)
select @version = dbo.fHtmVersion()
if @version is null set @version = ' HTM Library not installed.'
PRINT '[spHtmTestScript.sql]: Version: ' + @version + ' of the HTM library installed.'
-- produces something like:
-- [spHtmTestScript.sql]: Version: C# HTM.DLL V.1.0.0; 2005 August 1 of the HTM library installed.
go
----------------------------------------------
-- test fHtmToString() and fHtmLatLon()
print '----------'
print 'test fHtmToString() and fHtmLatLon()'
print  'the following string should be ''N320000000000000000000'' if fHtmToString() and  fHtmLatLon() are working.' 
print  '                               '''+ dbo.fHtmToString(dbo.fHtmLatLon(0,0)) + ''''
if ('N320000000000000000000' = dbo.fHtmToString(dbo.fHtmLatLon(0,0))) print '+++ Test passed +++'
else                                                                   print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- test fHtmToCornerPoints() 
print '----------'
print 'test fHtmToCornerPoints()'
declare @answer nvarchar(max)
print  'this should be 1,0,0  0,0,-1, 0,1,0  if fHtmToCornerPoints() is working ' 
set @answer = ''
select @answer =  @answer + cast(x as varchar(10)) + ',' + cast(y as varchar(10)) + ',' + cast(z as varchar(10)) + '  '
from fHtmToCornerPoints(8)  
print '               ' +@answer
-- 1 0  0
-- 0 0 -1
-- 0 1  0
if ('1,0,0  0,0,-1  0,1,0' = @answer) print '+++ Test passed +++'
else                                   print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- fHtmToCenterPoint()
print '----------'
print 'test fHtmToCenterPoint()'
declare @answer nvarchar(max)
select @answer =   'Lat:'   + str(LatLon.Lat,    10,4) 
				+  ', Lon:' + str(LatLon.Lon ,10,4)   
from fHtmToCenterPoint(dbo.fHtmLatLon(37.4,-122.4)) cross Apply 
     fHtmXyzToLatLon(x,y,z) as LatLon
print 'The answer should be Lat:   37.4000, Lon: -122.4000 if fHtmToCenterPoint() is working'  
print 'The answer is:       ' + @answer
if ('Lat:   37.4000, Lon: -122.4000' = @answer) print '+++ Test passed +++'
else                                            print '--- TEST FAILED -------------------------------'
go 
----------------------------------------------
-- fHtmLatLonToXyz() and fHtmXyzToLatLon()
print '----------'
print 'test fHtmLatLonToXyz() and fHtmXyzToLatLon()'
declare @answer nvarchar(max)
select @answer =   'Lat:'   + str(LatLon.Lat,    8,2) 
				+  ', Lon:' + str(LatLon.Lon    ,8,2)   
from fHtmLatLonToXyz(37.4,-122.4) as XYZ cross apply 
     fHtmXyzToLatLon(XYZ.x, XYZ.y, XYZ.z) as LatLon 
print 'The answer should be Lat:   37.40, Lon: -122.40 if fHtmLatLonToXyz() and fHtmXyzToLatLon() are working'  
print 'The answer is:       ' + @answer
if ('Lat:   37.40, Lon: -122.40' = @answer) print '+++ Test passed +++'
else                                        print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- fDistanceLatLon()
print '----------'
print 'test fDistanceLatLon() '
declare @answer nvarchar(max)
select @answer = str(dbo.fDistanceLatLon(0,38,0,48),8,2) 
print 'The answer should be that distance between 0,38 and 0,48 should be 600.00 arcminutes'  
print 'The answer according to fDistanceLatLon(0,38,0,48) is:           ' + @answer + ' arcminutes'
if ('  600.00' = @answer) print '+++ Test passed +++'
else                      print '--- TEST FAILED -------------------------------'
go

----------------------------------------------
-- fHtmToCornerPoints()
print '----------'
print 'test fHtmToCornerPoints()'
declare @answer nvarchar(max)
print 'The answer should be                                      (47.6460,-122.1229) (47.6461,-122.1229) (47.6460,-122.1230)'
set @answer = ''
select @answer = @answer 
				+  '('   + ltrim(str(LatLon.Lat,    8,4))
				+  ','     + ltrim(str(LatLon.Lon ,10,4)) + ') '   
from fHtmToCornerPoints(dbo.fHtmLatLon(47.646,-122.123)) cross apply fHtmXyzToLatLon(x,y,z) as LatLon
print 'fHtmToCornerPoints(dbo.fHtmLatLon(47.646,-122.123)) says: ' +@answer
if ('(47.6460,-122.1229) (47.6461,-122.1229) (47.6460,-122.1230)' = @answer) print '+++ Test passed +++'
else                                                                         print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- dbo.fHtmCoverCircleLatLon
print '----------'
print 'test fHtmCoverCircleLatLon()'
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 = ''
select @answer = @answer  
 	   + cast(P.PlaceName as varchar(max)) + ', ' 
 	   + str( dbo.fDistanceLatLon(@Lat,@Lon, I.Lat, I.Lon) ,4,2)  
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 ' using fHtmCoverCircleLatLon() it finds:      ' + @answer + '  arcmintes distant.' 
if ('Lansdowne-Baltimore Highlands, 4.37' = @answer) print '+++ Test passed +++'
else                                                 print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- dbo.fHtmCoverRegion 
print '----------'
print 'test fHtmCoverRegion()'
declare @answer1 varchar(max)
declare @answer2 varchar(max)
set @answer1 = ''
set @answer2 = ''
select @answer1 = @answer1 + '(' + cast(HtmIDStart as varchar(30)) 
						   + ',' + cast(HtmIDEnd  as varchar(30) ) 
						   + ') '
from fHtmCoverRegion('CIRCLE LATLON 45 45 1')
select @answer2 = @answer2 + '(' + cast(HtmIDStart as varchar(30)) 
						   + ',' + cast(HtmIDEnd  as varchar(30) ) 
						   + ') '
from fHtmCoverCircleLatLon(45,45,1)
print 'fHtmCover(''CIRCLE LATLON 45 45 1'') says:                 ' + @answer1
print ' and the equivalent fHtmCoverCircleLatLon(45,45,1) says: ' + @answer2
if (@answer1 = @answer2) print '+++ Test passed +++'
else                     print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- dbo.fHtmRegionToNormalFormString
print '----------'
print 'test fHtmRegionToNormalFormString( )'
declare @answer varchar(max), @CorrectAnswer varchar(max)
set @CorrectAnswer = 'REGION CONVEX CARTESIAN    0.500000000000000    0.500000000000000    0.707106781186547    0.999999957692025 '
select  @answer = dbo.fHtmRegionToNormalFormString('CIRCLE LATLON 45 45 1')
print 'dbo.fHtmRegionToNormalFormString(''CIRCLE LATLON 45 45 1'')should be: ' + @CorrectAnswer + ':' 
print 'returned result is                ''                    ''          : ' + @answer        + ':'
if ( @answer  =  @CorrectAnswer) print '+++ Test passed +++'
else                     print '--- TEST FAILED -------------------------------'
go
print 'test fHtmRegionToNormalFormString( ) on null region'
declare @answer varchar(max), @CorrectAnswer varchar(max)
set @CorrectAnswer = 'REGION '
select  @answer = dbo.fHtmRegionToNormalFormString('CIRCLE LATLON 45 45 0')
print 'dbo.fHtmRegionToNormalFormString(''CIRCLE LATLON 45 45 0'')should be: ' + @CorrectAnswer + ':' 
print 'returned result is                ''                    ''          : ' + @answer        + ':'
if ( @answer  =  @CorrectAnswer) print '+++ Test passed +++'
else                     print '--- TEST FAILED -------------------------------'
go
print 'test fHtmRegionToNormalFormString( ) simplification'
declare @answer varchar(max), @CorrectAnswer varchar(max)
set @CorrectAnswer = 'REGION CONVEX CARTESIAN    1.000000000000000    0.000000000000000    0.000000000000000    0.700000000000000 '
select  @answer = dbo.fHtmRegionToNormalFormString('REGION CONVEX 1 0 0 .5 1 0 0 .7 1 0 0 -.5')
print 'dbo.fHtmRegionToNormalFormString(''REGION CONVEX 1 0 0 .5 1 0 0 .7 1 0 0 -.5'')should be: ' + @CorrectAnswer + ':' 
print 'returned result is                ''                                        ''          : ' + @answer        + ':'
if ( @answer  =  @CorrectAnswer) print '+++ Test passed +++'
else                     print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- dbo.fHtmRegionToTable 
print '----------'
print 'test fHtmRegionToTable()'
declare @answer nvarchar(max), @correctAnswer nvarchar(max)
set @correctAnswer = '0#0 0.5,0.5,0.707107,1||| 1#0 1,0,0,0||| 1#1 0,1,0,0||| 1#2 0,0,1,0||| '
print  'this should be :' + @correctAnswer + ': if fHtmRegionToTable() is working ' 
set @answer = ''
select @answer =  @answer +  cast(convexID as varchar(2)) + '#' + cast(halfspaceID as varchar(2)) + ' ' 
				+ cast(x as varchar(4)) + ',' + cast(y as varchar(4)) + ',' + cast(z as varchar(15)) + ','
                + cast(D as varchar(15)) + '||| '
from  fHtmRegionToTable('REGION CIRCLE LATLON 45 45 1 CONVEX 1 0 0 0 0 1 0 0 0 0 1 0')
print '               :' +@answer + ':'
if (@correctAnswer = @answer) print '+++ Test passed +++'
else                          print '--- TEST FAILED -------------------------------'
go
 ----------------------------------------------
-- dbo.fHtmRegionError
print '----------'
print 'test fHtmRegionError()'
declare @answer nvarchar(max), @correctAnswer nvarchar(max)
set @correctAnswer = 'end of line not expected. maybe need more arguments?'
select @answer = dbo.fHtmRegionError('REGION CONVEX CARTESIAN    0.500000000000000')
if (substring(@answer,1,len(@correctAnswer)) = @correctAnswer) print '+++ Test passed +++'
else                                                           print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- dbo.fHtmGetNearbyLatLon  
print '----------'
print 'test fHtmNearbyLatLon()'
declare @answer nvarchar(max)
declare @Lat float, @Lon float
select @Lat = Lat, @Lon = Lon 
from Place 
where Place.PlaceName = 'Baltimore' 
  and State = 'MD' 
select @answer = cast (count(*) as int) 
from fHtmNearbyLatLon('P', @Lat, @Lon, 6) as N join Place as P 
		on N.ObjID = P.HtmID
print 'fHtmNearbyLatLon() should find 10 places witin 6 nautical miles of Baltimore.'
print '                      it found ' + @answer +  ' places.'               
if ('10' = @answer) print '+++ Test passed +++'
else                print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- dbo.fHtmGetNearestLatLon
print '----------'
print 'test fHtmNearestLatLon()'
declare @answer nvarchar(max)
declare @Lat float, @Lon float
select @Lat = Lat, @Lon = Lon 
from Place 
where Place.PlaceName = 'Baltimore' 
  and State = 'MD' 
select @answer = PlaceName 
from fHtmNearestLatLon('P', @Lat, @Lon ) as N join Place as P 
		on N.ObjID = P.HtmID
print 'fHtmNearestLatLon() says ' + @answer + ' is nearest to Baltimore.'
if ('Baltimore' = @answer) print '+++ Test passed +++'
else                print '--- TEST FAILED -------------------------------'
go
-----------------------------------------------------------------------------------------
--=======================================================================================
-- EXAMPLES FROM PAPER
--=======================================================================================
-- The Table Valued Function Paper  
-----------------------------------------------------------------------------------------
-- Examples from 1. Find Points near point: find towns near a place. 
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           -- triangle test
   and Type = 'P'
   and dbo.fDistanceLatLon(@Lat,@Lon, Lat, Lon) < 100 -- diamond 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')
--22
select count(*) from fHtmCoverRegion('CIRCLE CARTESIAN 0.1792 -0.7528 0.6334 100')
--22
select count(*) from fHtmCoverRegion('RECT LATLON 37 -109.55  41 -102.05')
-- 18
select count(*) from fHtmCoverRegion('CHULL LATLON 37 -109.55 41 -109.55 41 -102.051 37 -102.05')
--42
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)
--42
select * from fHtmCoverRegion ('REGION CONVEX CARTESIAN 0.7 0.7 0.0 -0.5  CIRCLE LATLON 18.2 -22.4 1.75')
-- 86 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)
-- 672 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 'C# HTM.DLL V.1.0.0; 2005 June 28'
-----------------------------------------------
-- 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'
-----------------------------------------------
-- 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"
 
  
