SQL Server 在点周围生成随机空间地理? [英] SQL Server generating random spatial geography around point?

问题描述

我在开发环境中有数千条记录，每条记录都与特定邮政编码的质心相关联.出于测试目的，我需要将每个 SQL Server 地理点随机散布在该质心周围 0-5 英里处.

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 Server geography point 0-5 miles around that centroid.

因此，在下面的示例中，我想更新 LocationGeo，使其与其各自的 ZipGeo 相距 0-5 英里.我是否必须对每个 Lon/Lat 使用随机百分比，还是有更好的选择?

So in the example below I want to update LocationGeo so it is 0-5 miles away from its respective ZipGeo. Do I have to 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

推荐答案

我从这篇文章中找到了答案 https://gis.stackexchange.com/a/25883/37373

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

我将响应修改为 SQL Server 代码.

I adapted the response into 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

这篇关于SQL Server 在点周围生成随机空间地理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！