
USE Sample_USA
GO

drop table numbers

--Create a Numbers Table with 100,000 entries
SELECT TOP 100 IDENTITY(int,1,1) AS n INTO numbers FROM master..spt_values a, master..spt_values b
CREATE UNIQUE CLUSTERED INDEX idx_1 ON numbers(n) --Index numbers
GO

--Find The Nearest GeoNames Data Around A Point Location
DECLARE @input GEOGRAPHY = 'POINT (-147 61)';
DECLARE @start FLOAT = 10000;
WITH NearestNeighbor AS(
  SELECT TOP 1 WITH ties *, b.GEOG.STDistance(@input) AS dist
   FROM numbers n JOIN GeoNames b WITH(INDEX(geog_hhhh_16_sidx)) -- optimizer hint
     ON b.GEOG.STDistance(@input) < @start*POWER(2,n.n)
       ORDER BY n
)
SELECT TOP 1 geonameid, name, feature_code, admin1_code, dist
  FROM NearestNeighbor
ORDER BY n, dist

select * into geonames_blank
from geonames
where 1=2
go

alter table geonames_blank
add PRIMARY KEY CLUSTERED 
(
	[geonameid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE SPATIAL INDEX [geog_hhhh_16_sidx] ON [dbo].[Geonames_blank] 
(
	[GEOG]
)USING  GEOGRAPHY_GRID 
WITH (
GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

-- arithmetic overflow
DECLARE @input GEOGRAPHY = 'POINT (-147 61)';
DECLARE @start FLOAT = 10000;
WITH NearestNeighbor AS(
  SELECT TOP 1 WITH ties *, b.GEOG.STDistance(@input) AS dist
   FROM numbers n JOIN GeoNames_blank b WITH(INDEX(geog_hhhh_16_sidx)) -- optimizer hint
     ON b.GEOG.STDistance(@input) < @start*POWER(2,n.n)
       ORDER BY n
)
SELECT TOP 1 geonameid, name, feature_code, admin1_code, dist
  FROM NearestNeighbor
ORDER BY n, dist

-- one row
insert into geonames_blank select top 1 * from geonames

-- subsecond
DECLARE @input GEOGRAPHY = 'POINT (-147 61)';
DECLARE @start FLOAT = 10000;
WITH NearestNeighbor AS(
  SELECT TOP 1 WITH ties *, b.GEOG.STDistance(@input) AS dist
   FROM numbers n JOIN GeoNames_blank b WITH(INDEX(geog_hhhh_16_sidx)) -- optimizer hint
     ON b.GEOG.STDistance(@input) < @start*POWER(2,n.n)
       ORDER BY n
)
SELECT TOP 1 geonameid, name, feature_code, admin1_code, dist
  FROM NearestNeighbor
ORDER BY n, dist

-- wrong srid, 1 out of 1.5 million rows, over 2 minutes
DECLARE @input GEOGRAPHY = 'POINT (-147 61)';
SET @input.STSrid = 4269


BEGIN
DECLARE @start FLOAT = 10000;
WITH NearestNeighbor AS(
  SELECT TOP 1 WITH ties *, b.GEOG.STDistance(@input) AS dist
   FROM numbers n JOIN geonames b WITH(INDEX(geog_hhhh_256_sidx)) -- optimizer hint
     ON @input.STSrid = SRID AND b.GEOG.STDistance(@input) < @start*POWER(cast(2 as float),n.n)
       ORDER BY n
)
SELECT TOP 1 geonameid, name, dist
  FROM NearestNeighbor
ORDER BY n, dist
END

select COUNT(*) from numbers

-- 1000 rows in numbers table won't get a floating point overflow
-- it covers STDistances up to 1.14841449006148E+305 meters
-- the query with no rows takes 17 seconds
select 10000*POWER(cast(2 as float),1000.1000)

-- 100 rows in numbers table won't get a floating point overflow
-- it covers STDistances up to 1.35863427309281E+34 meters
-- the query with no rows takes 1-2 seconds
select 10000*POWER(cast(2 as float),100.100)

-- 20 rows in numbers table won't get a floating point overflow
-- it covers STDistances up to 12044975262.8937 meters
-- the query with no rows takes 1-2 seconds
select 10000*POWER(cast(2 as float),20.20)

declare @np geography = 'POINT(0 0)'
declare @sp geography = 'POINT(180 0)'
select @np.STDistance(@sp) -- 20003931.4582406 meters

update top(1) geonamesNW set geog.STSrid = 4326

alter table geonames add SRID as geog.STSrid persisted

