Points within a radius using pythagoras only and longitude / latitude

• dave-428767

  SSC Enthusiast

  Points: 167

  More actions

  December 29, 2011 at 3:20 pm

  #104106

  Hi,

  I know it is a common question about working out how many points (lon, lat) fall within a radius of another point but most answers and worked examples are using great circle.

  I dont need that kind of accuracy so i want to use pythagoras to do it. However, i just cant find any worked examples - i just see people say use "Pythagoras". I understand pythagoras of course but im struggling with converting the radius in to miles / km and returning the correct results.

  Please could somebody be kind enough to do a worked example as i dont believe what i have is correct..

  The table contains various points with lon and lat values. I want to provide my current long and lat, a radius and the unit of measure (M or K) as params and get a list of all the points within that radius.

  this is what i have but it doesnt work:

  @Distance = 1

  @Unit = 'K'

  --i.e. 1KM radius

  IF @Unit = 'M'

  set @Offset = (@Distance/0.00062)/100000

  ELSE -- must be km

  set @Offset = @Distance/100000

  SELECT *

  FROM dbo.points

  WHERE(Latitude between (@Lat-@Offset) and (@Lat+@Offset))

  AND (Longitude between (@Long-@Offset) and (@Long+@Offset))

  Really hope someone can help as i keep re-reading the same posts all the time and going round in circles 🙂

  Thanks

• Bamboozled

  Grasshopper

  Points: 13

  More actions

  December 31, 2011 at 7:37 am

  #1427215

  Try this:

  --Create a temp table with some random points for testing purposes

  create table #Points(

  idint identity(1,1) not null,

  x decimal(10,5),

  y decimal(10,5))

  declare @count int = 0

  while @count < 20

  begin

  insert into #Points(x,y)

  values (rand()*100.0 - 50.0,

  RAND()*100.0 - 50.0)

  set @count = @count + 1

  end

  declare @locX decimal(10,5) = 0.0

  declare @locY decimal(10,5) = 0.0

  declare @radius decimal(10,5) = 15.0

  --SELECT Points closer than @radius

  select * from #Points p

  WHERE power(p.x - @locX, 2) + POWER(p.y - @locY, 2) < POWER(@radius,2)

  --SELECT Points and show their distance from origin (@locX, @locY)

  select p.*, POWER(power(p.x - @locX, 2) + POWER(p.y - @locY, 2), 0.5)

  from #Points p

• dave-428767

  SSC Enthusiast

  Points: 167

  More actions

  January 2, 2012 at 9:24 pm

  #1427542

  Hi there,

  Many thanks for your reply..

  The code looks like what im after but in your example is the radius of 15 in kilometers or miles?

  Thanks