0

I am selecting five percent of all villages in an area by this query:

select top 5 percent * from Latlong_Amar  a
where a.IsInSarshomari=1 and a.ostan_num=02
order by newid()

Everything is fine, but I want to select random and I want to place a condition. I want to select some villages to check cellphone signal in an area, and the said query return a result, but it's not a desirable result for me. Usually places near main town are denser comparing remote villages, and when I use random, naturally, remote villages have less chance to be in the list. I have latitude and longitude of all villages. Do you somehow happen to think a way to select randomly and fairly considering destination of selected villages?

Yousef
  • 393
  • 8
  • 23
  • IMO, You need [Calculating distance between two points (Latitude, Longitude)](http://stackoverflow.com/questions/13026675/calculating-distance-between-two-points-latitude-longitude) then order it before `newid()` ;). – shA.t Jul 03 '16 at 07:45
  • Could you please tell me how to do it in SQL-Server? – Yousef Jul 03 '16 at 07:46
  • I may have up to 10K points, and they are distributed on an area, and if I want to calculate distance between points, I should do it for 10K or more points, which I guess is 10K factorial, isn't there any simpler way? – Yousef Jul 03 '16 at 07:59
  • I was thinking about a hypothetical grids (vertical and horizontal), and then select from those areas evenly, but I don't know how to perform it. I mean, I should grid it with highest latitude,longitude to lowest ones, and then divide them with a certain distance, and then check them to omit grid areas that have no villages, and then use the remaining ones for selection. – Yousef Jul 03 '16 at 08:03
  • Then I should do it manually, I want to use codes, because I have more than 100 areas to check. – Yousef Jul 03 '16 at 08:07
  • Please provide some sample data and your expected result ;). – shA.t Jul 03 '16 at 08:08
  • I can provide you with sample data (which is a set of lats and longs), but not an expected result, since I want a random result. – Yousef Jul 03 '16 at 08:16

0 Answers0