Geek asks and answers

Sort results by proximity (with coordinates and radius)

For a database of 4 circles, where each circle has a radius and a geolocation center:

id | radius | latitude | longitude
---+--------+----------+----------
 1 |      3 |    40.71 |    100.23
 2 |     10 |    50.13 |    100.23
 3 |     12 |    39.92 |    100.23
 4 |      4 |    80.99 |    100.23

Note: longitude is the same for each circle, so that everything is simple.

Assuming we are on circle 2, I would like to find each circle nearby, in accordance with the coordinates of latitude/ longitudeand radiuseach circle.

For example, according to the latitude / longitude coordinates, we have the following order:

  • Circle 1 (because of the proximity of: 9.42 <- 50.13 - 40.71)
  • round 3 (due to the proximity of: 10.21 <- 50.13 - 39.92)
  • Circle 4 (due to the proximity of: 30.86 <- 80.99 - 50.13)

But according to the latitude / longitude coordinates and the radius of each circle, we should have:

  • round 3 (due to the proximity of: 1.79 <- 12 - 10.21)
  • Circle 1 (because of the proximity of: 6.42 <- 9.42 - 3)
  • Circle 4 (due to the proximity of: 26.86 <- 30.86 - 4)

Is there an easy way to do this in SQL?

+5
source
4

cube earthdistance, postgresql contrib, , . , , , .

2 :

select circle.id,
       earth_distance(ll_to_earth(circle.latitude, circle.longitude),
                      ll_to_earth(x.latitude, x.longitude))
 from circle,
      circle x
 where x.id = 2 and circle.id <> x.id
 order by 2;

x.radius circle.radius , , . earth_distance .

, , -, , , , . :

  • gist, , , , .
  • , , .

:

create table circle_distance as
select a.id as a_id, b.id as b_id,
 earth_distance(ll_to_earth(a.latitude, a.longitude),
                ll_to_earth(b.latitude, b.longitude))
 from circle a, circle b
 where a.id <> b.id;
alter table circle_distance add unique(a_id, b_id);
create index on circle_distance(a_id, earth_distance);

/ circle_distance, circle. , :

select b_id from earth_distance where a_id = $circle_id order by earth_distance limit $n

(a_id,earth_distance) .

+1

PostGIS Geography (: ST_Distance),

0

:

1

:

id | calc1  | calc2    
---+--------+----------
 1 |  9.42  |    1.97
 3 |  10.21 |    6.42
 4 |  30.86 |   62.86

Calc1 - calc2 -

, , , , ,

Intrudoction

0

More articles:


All Articles