Hello there! :)

Just want to say, I'm still very very new to messing around with geospatial data - The reason for doing it, is to make a better analysis for my master thesis - So please be patient if you can :)

I have two tables: DAWA and Node

DAWA contains: 800.000 entries

• ConCatAddress = varchar(255)
• wgs84koordinat_long = float
• wgs84_koordinat_lat = float
• Geog = ([GEOGRAPHY]::STGeomFromText(((('Point('+CONVERT([varchar](32),[wgs84koordinat_long]))+' ')+CONVERT([varchar](32),[wgs84koordinat_lat]))+')',(4326)))

Node contains: 900 entries

• NodeID = int
• wgs84_long = float
• wgs84_lat = float
• Geog = ([GEOGRAPHY]::STGeomFromText(((('Point('+CONVERT([varchar](32),[wgs84_long]))+' ')+CONVERT([varchar](32),[wgs84_lat]))+')',(4326)))

What I need to to, is to find the nearest point from an address in DAWA with an entry from Node and Update DAWA with a column called "NearestPoint" and "distance" with the finding.

Right now, I'm using the SQL in the bottom of this post, but here I have to run i manually 900 times, which most likely will get the same address more than once and will take me forever. As you can see, the SQL below will answer my question: Which addresses is nearest to this point.

What I want it to answer is: Which point, from Node, is nearest to this specific address in DAWA.

SQL:

DECLARE @Lat AS float, @Long AS float, @adresse AS varchar(255);
DECLARE @MaxRange AS int, @MinRange AS int, @RangeDivisor AS float, @RangeUnit AS varchar(10) , @RangeDelimiter AS varchar(2);
SET @MinRange = 0;
SET @MaxRange = 10;
SET @RangeDivisor = 1000;
SET @RangeDelimiter = 4;
SET @RangeUnit = 'KM';

SELECT @Lat = dbo.DAWA.wgs84koordinat_lat, @Long = dbo.DAWA.wgs84koordinat_long, @adresse = dbo.DAWA.ConCatAddress
FROM dbo.DAWA --WHERE postnr = '6700';
WHERE wgs84koordinat_long = '10.1894185761792' AND wgs84koordinat_lat = '56.1940385664007'; --FiberNode lokation (NodeID='8' Adr='8200 Aarhus N Skejbyparken 4 1 1')

WITH Distance AS (
SELECT
  KVHx,
  wgs84koordinat_lat,
  wgs84koordinat_long,
  ConCatAddress,
  Geog.STDistance(geography::Point(@Lat, @Long, 4326))/@RangeDivisor [Dist],
  @RangeUnit [Unit]
FROM dbo.DAWA)

SELECT KVHx,  ConCatAddress AS [Address], wgs84koordinat_lat AS latgrad, wgs84koordinat_long AS longgrad, LEFT(Dist,@RangeDelimiter) AS Dist, Unit
FROM Distance
WHERE Dist <= @MaxRange AND Dist >= @MinRange 
ORDER BY Dist

Can any of you help me? I've tried to make some JOINS, but I can't make it how I want.. ! :(

I've found a picture that maybe explaining it better than I:

• Edited by Patrick K. Petersen Thursday, August 27, 2015 1:23 PM
• Moved by Lydia ZhangMicrosoft contingent staff, Moderator Friday, August 28, 2015 2:21 AM more appropriate

Found this interesting blog post

http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx

but it shows how to find closest neighbors for a particular point, not how to do it for 2 tables.

I've tried your solution and it did work when I did a sample of 200.000 rows.

when I try the full DAWA it gives me this after some time:

Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage:  140738124382208' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

I have an octo core with 16 gb ram, windows server r2 , it should be capable of running this query, what is wrong? 

Ive read that multiple times, it's a good read and I tried to build my solution from that article :)

Since you only have 900 entries in Node, can you run a cursor for that table and apply code form that blog to find the nearest address?