CODEWITHSUNDEEP
sqlpostgresql
user6611764
user6611764

Reputation:

How to search in a radius using Postgres extension?

Finding distances on the surface of the earth means using Great Circle distances, worked out with the Haversine formula, also called the Spherical Cosine Law formula.

The problem is this: Given a table of locations with latitudes and longitudes, which of those locations are nearest to a given location?

I have the following query:

 SELECT z.id,
        z.latitude, z.longitude,
        p.radius,
        p.distance_unit
                    * DEGREES(ACOS(COS(RADIANS(p.latpoint))
                    * COS(RADIANS(z.latitude))
                    * COS(RADIANS(p.longpoint - z.longitude))
                    + SIN(RADIANS(p.latpoint))
                    * SIN(RADIANS(z.latitude)))) AS distance
 FROM doorbots as z
 JOIN (   /* these are the query parameters */
    SELECT 34.0480698 AS latpoint, -118.3589196 AS longpoint,
           2 AS radius,  111.045 AS distance_unit
      ) AS p ON 1=1
 WHERE z.latitude between ... and 
       z.longitude between ...

How to use earthdistance extension to change my complicated formula in the query?

Is it equivalent change?

SELECT z.id,
       z.latitude, z.longitude,
       p.radius,
       round(earth_distance(ll_to_earth(p.latpoint, p.longpoint), ll_to_earth(z.latitude, z.longitude))::NUMERIC,0) AS distance
 FROM doorbots as z
 JOIN (   /* these are the query parameters */
    SELECT 34.0480698 AS latpoint, -118.3589196 AS longpoint,
           2 AS radius,  111.045 AS distance_unit
      ) AS p ON 1=1
 WHERE z.latitude between ... and 
       z.longitude between ...

Upvotes: 1

Views: 3565

Answers (1)

pozs
pozs

Reputation: 36274

You can get the most out of earthdistance with the following queries:

Locations close enough (i.e. within 1000000.0 meters -- 621.371192 miles) to (34.0480698, -118.3589196):

select *
from   doorbots z
where  earth_distance(ll_to_earth(z.latitude, z.longitude), ll_to_earth(34.0480698, -118.3589196)) < 1000000.0; -- in meters

select *
from   doorbots z
where  point(z.longitude, z.latitude) <@> point(-118.3589196, 34.0480698) < 621.371192; -- in miles

Top 5 locations closest to (34.0480698, -118.3589196):

select   *
from     doorbots z
order by earth_distance(ll_to_earth(z.latitude, z.longitude), ll_to_earth(34.0480698, -118.3589196))
limit    5;

select   *
from     doorbots z
order by point(z.longitude, z.latitude) <@> point(-118.3589196, 34.0480698)
limit    5;

To use indexes, apply the following one to your table:

create index idx_doorbots_latlong
  on doorbots using gist (earth_box(ll_to_earth(latitude, longitude), 0));

Use index for: locations close enough (i.e. within 1000000.0 meters -- 621.371192 miles) to (34.0480698, -118.3589196):

with p as (
  select 34.0480698   as latitude,
         -118.3589196 as longitude,
         1000000.0    as max_distance_in_meters
)
select z.*
from   p, doorbots z
where  earth_box(ll_to_earth(z.latitude, z.longitude), 0) <@ earth_box(ll_to_earth(p.latitude, p.longitude), p.max_distance_in_meters)
and    earth_distance(ll_to_earth(z.latitude, z.longitude), ll_to_earth(p.latitude, p.longitude)) < p.max_distance_in_meters;

Use index for: top 5 locations closest to (34.0480698, -118.3589196):

select   z.*
from     doorbots z
order by earth_box(ll_to_earth(z.latitude, z.longitude), 0) <-> earth_box(ll_to_earth(34.0480698, -118.3589196), 0)
limit    5;

http://rextester.com/WQAY4056

Upvotes: 6

Related Questions