CODEWITHSUNDEEP
sqlt-sqlsql-server-2012geogeography
Jithin Shaji
Jithin Shaji

Reputation: 6073

Find postcodes within 16km Range

i have a table ([dbo].[LUPostCode]) with postcode, Lattitude and longitude. (27K records). I have another table ([dbo].[LUPostCodeMaster]) , a master table with same structure, have (2.5 Million records).

I need to find out all the postcodes from [dbo].[LUPostCodeMaster] which is 16Km (or) less distant from Postcodes in [dbo].[LUPostCode]

I am trying

SELECT  C.skMasterPostCode PostCode,
        M.skMasterPostCode AdjPostCode
INTO    WorkTable
FROM    [dbo].[LUPostCode] C,
        (
        SELECT [skMasterPostCode],[Latitude],[Longitude] 
        FROM    [dbo].[LUPostCodeMaster]
        ) M --Expected rows --> 70,771,012,410
WHERE   CAST('POINT('+C.Latitude+ ' '+C.Longitude+')' AS GEOGRAPHY).STDistance(CAST('POINT('+M.Latitude+ ' '+M.Longitude+')' AS GEOGRAPHY)) <= 16000

It is taking so much time..I know it is a bad idea to CROSS JOIN 27K with 2.5 Million.

Can anyone suggest a better Idea.

Upvotes: 1

Views: 249

Answers (1)

Stefan Steiger
Stefan Steiger

Reputation: 82186

-- DROP TABLE #mytemptable 

SELECT 
 C.UID  AS uidC
,M.UID AS uidM
,CAST('POINT(' + C.Latitude + ' ' + C.Longitude + ')' AS GEOGRAPHY)
.STDistance(CAST('POINT(' + M.Latitude+ ' ' + M.Longitude+')' AS GEOGRAPHY))
 AS distance 
INTO #mytemptable 
FROM LUPostCode AS C
CROSS JOIN LUPostCodeMaster AS M 


DELETE FROM #mytemptable WHERE distance > 16000    


SELECT * FROM #mytemptable 

LEFT JOIN LUPostCode AS C
    ON C.uid = uidC
LEFT JOIN LUPostCodeMaster AS M 
    ON M.uid = uidM

Works within 15 seconds on my 4 million entry sample table.

Well, according to
http://aboutsqlserver.com/2013/07/22/clr-vs-t-sql-performance-considerations/
you can still move it to a CLR function.
That might cut the time in half.

CLR Speed

And well, you're still doing a computation on 67.5 billion combinations, so it simply takes some time.

Upvotes: 2

Related Questions