SQL Server generates random spatial geography around a point?
I have several thousand records in a development environment, each associated with the centroid of a specific zip code. For testing purposes, I need to randomly scatter each SQL Server geographic point 0-5 miles around that center.
So, in the example below, I want to update LocationGeo to be 0-5 miles away from its corresponding ZipGeo. Should I use a random% applied to each Lon / Lat, or is there a better option?
LocationID int
LocationGeo geography
ZipCode char(5)
ZipCode char(5)
ZipGeo geography
+3
source to share
1 answer
I found the answer from this post https://gis.stackexchange.com/a/25883/37373
I adapted the answer to SQL Server code.
DECLARE @geo as GEOGRAPHY,@newgeo as GEOGRAPHY
SET @geo = (SELECT ZipGeo FROM Location.ZipCodes WHERE ZipCode='90210')
DECLARE @r float,@t float, @w float, @x float, @y float, @u float, @v float;
SET @u=RAND();
SET @v=RAND();
--8046m = ~ 5 miles
SET @r= 8046/(111300*1.0);
SET @w = @r * sqrt(@u);
SET @t = 2 * PI() * @v;
SET @x = @w * cos(@t);
SET @y = @w * sin(@t);
SET @x = @x / cos(@geo.Lat);
SET @newgeo = geography::STPointFromText('POINT('+CAST(@geo.Long+@x AS VARCHAR(MAX))+' '+CAST(@geo.Lat+@y AS VARCHAR(MAX))+')',4326)
--Convert the distance back to miles to validate
SELECT @geo.STDistance(@newgeo)/1609.34
+2
source to share