use Spatial
go
exec sp_configure 'clr enabled', 1
go
reconfigure
go
set nocount on
go
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: Convex-HTM.DLL V.1.0.0; 2006 February 14 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.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 = 'Not enough arguments for this specification'
select @answer = dbo.fHtmRegionError('REGION CONVEX CARTESIAN    0.500000000000000')
if (substring(@answer,1,len(@correctAnswer)) = @correctAnswer) print '+++ Test passed +++'
else                                                           print '--- TEST FAILED -------------------------------'
go
----------------------------------------------
-- 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

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--------------------------------------------------	
-- 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 

-----------------------------------------------
-- 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
*/
-----------------------------------------------

-----------------------------------------------
-- Circular region HTM cover:  fHtmCoverCircleLatLon(Lat, Lon, radius)  
-- Returns a trixel table covering the designated circle. 
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()

-----------------------------------------------
-- 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"
 
  
