Thanks for the link Patrick, was a good read.
We use the sectors technique for other purposes, but our sectors are 6m x 6m. Unfortunately even sectors this small are not accurate enough.
Most of the #Position and #Location data will be in one city.
Here's the simplified sample code.
USE TempDB
GO
IF OBJECT_ID('TempDB..#Positions') IS NOT NULL
DROP TABLE #Positions
CREATE TABLE #Positions -- Actual table has 257 mil rows, 20 columns
(
Position_IDBigInt CONSTRAINT PK_PosID PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),
Location_NameVarchar(50)
)
INSERT#Positions
SELECT581763287, 51.546783, 4.803814, NULL UNION ALL
SELECT581763288, 52.070960, 5.123537, NULL UNION ALL
SELECT581773709, 51.118755, 3.561964, NULL UNION ALL
SELECT581773710, 51.118759, 3.562069, NULL UNION ALL
SELECT581773711, 51.118816, 3.562144, NULL
IF OBJECT_ID('TempDB..#Locations') IS NOT NULL
DROP TABLE #Locations
CREATE TABLE #Locations -- 88 rows
(
Loc_IDInt CONSTRAINT PK_LocID PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),
GeoShapeGeoGraphy,
Loc_NameVarchar(50)
)
INSERT#Locations
SELECT638767, 52.070732, 5.123783, 0xE6100000010405000000FA8777B2D7084A4077EDB30B107C144039C124B041094A4077EDB30B107C144039C124B041094A40E42C30C86F811440FA8777B2D7084A40E42C30C86F811440FA8777B2D7084A4077EDB30B107C144001000000020000000001000000FFFFFFFF0000000003, 'Location 1' UNION ALL
SELECT797174, 51.546888, 4.803603, 0xE6100000010405000000DF03BDD1CBC54940170659B43A341340F58B15D235C64940170659B43A341340F58B15D235C649401C97157D8A391340DF03BDD1CBC549401C97157D8A391340DF03BDD1CBC54940170659B43A34134001000000020000000001000000FFFFFFFF0000000003, 'Location 2'
IF EXISTS (SELECT * FROM sys.objects WHERE Name = 'GetDistance' AND [Type] = 'FN')
DROP FUNCTION GetDistance
GO
CREATE FUNCTION [dbo].[GetDistance]
(
@Latitude1 Decimal(10,6),
@Longitude1Decimal(10,6),
@Latitude2 Decimal(10,6),
@Longitude2 Decimal(10,6)
)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
DECLARE @Temp Float
SET @Temp = SIN(@Latitude1/57.2957795130823) *
SIN(@Latitude2/57.2957795130823) +
COS(@Latitude1/57.2957795130823) *
COS(@Latitude2/57.2957795130823) *
COS(
@Longitude2/57.2957795130823 -
@Longitude1 /
57.2957795130823
)
IF @Temp > 1
SET @Temp = 1
ELSE IF @Temp < -1
SET @Temp = -1
RETURN CAST((3958.75586574 * ACOS(@Temp) * 1609.344) AS INT)
END
GO
UPDATE#Positions
SETLocation_Name = L.Loc_Name
FROM#Positions P WITH (NOLOCK)
OUTER APPLY
(
SELECTTOP(1) L.Loc_Name
FROM#Locations L WITH (NOLOCK)
WHEREGeoshape.STIntersects(geography::Point(P.Latitude, P.Longitude, 4326)) = 1
ORDERBY dbo.GetDistance(P.Latitude, P.Longitude, L.latitude, L.longitude) ASC
) L
SELECT*
FROM#Positions
IF OBJECT_ID('TempDB..#Positions') IS NOT NULL
DROP TABLE #Positions
IF OBJECT_ID('TempDB..#Locations') IS NOT NULL
DROP TABLE #Locations
IF EXISTS (SELECT * FROM sys.objects WHERE Name = 'GetDistance' AND [Type] = 'FN')
DROP FUNCTION GetDistance