Sql Server Generating Random Spatial Geography Around Point?

I have several thousand records in a development environment, each associated with a centroid of a particular zip code. For testing purposes, I need to randomly scatter each SQL Se

Solution 1:

I found an answer from this post https://gis.stackexchange.com/a/25883/37373

I adapted the response into SQL Server code.

DECLARE@geoas GEOGRAPHY,@newgeoas GEOGRAPHY
SET@geo= (SELECT ZipGeo FROM Location.ZipCodes WHERE ZipCode='90210')

DECLARE@rfloat,@tfloat, @wfloat, @xfloat, @yfloat, @ufloat, @vfloat;

SET@u=RAND();
SET@v=RAND();

--8046m = ~ 5 milesSET@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+@xASVARCHAR(MAX))+' '+CAST(@geo.Lat+@yASVARCHAR(MAX))+')',4326)
--Convert the distance back to miles to validateSELECT@geo.STDistance(@newgeo)/1609.34