Question 114981 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Fastest way to filter latitude and longitude from a sql table?

I have a huge table data where sample data is like below. I want to filter few latitude and longitude records from the huge table and I am using In clause to filter list of lat,lon values but when I try to run the query it takes more a min to execute what is the better query to execute it faster? the list of lat,lon is around 120-150

id   longitude   latitude
--------------------------
190  -0.410123   51.88409
191  -0.413256   51.84567

query:-

SELECT DISTINCT id, longitude, latitude
FROM geo_table 
WHERE ROUND(longitude::numeric, 3) IN (-0.418, -0.417, -0.417, -0.416 and so on )
  AND ROUND(latitude::numeric, 3) IN (51.884, 51.884, 51.883, 51.883 and so on);
Tags:

2

Answers


  1. 0

    If at least one of the ranges of values in X or Y is tight you can try prefiltering rows. For example, if X (longitude) values are all close together you could try:

    SELECT distinct id,longitude,latitude
from (
  select *
  FROM geo_table 
  where longitude between -0.418 and -0.416 -- prefilter with index scan
    and latitude between 51.883 and 51.884  -- prefilter with index filter
) x
-- now the re-check logic for exact filtering
where ROUND(longitude::numeric,3) in (-0.418, -0.417, -0.417, -0.416, ...)
  and ROUND(latitude::numeric,3) in (51.884, 51.884, 51.883, 51.883, ...)

    You would need an index with the form:

    create index ix1 on geo_table (longitude, latitude);
    Login or Signup to reply.
  2. 0

    First, the way you are looking for a list of latitudes and a list of longitudes is likely wrong if you are looking for points locations:

    point: lat;long
----------------
Point A: 1;10  
Point B: 2;10  
Point C: 1;20  
Point D: 2;20

    –> if you search for latitude in (1;2) and longitude in (10;20), the query will return the 4 points, while if you search for (latitude,longitude) in ((1;10),(2;20)), the query will return only points A and D.

    Then, since you are looking for rounded values, you must index the rounded values:

    create index latlong_rdn on geo_table (round(longitude,3),round(latitude,3));

    and the query should use the exact same expression:

    select * 
from geo_table 
where (round(longitude,3),round(latitude,3)) in 
  (
    (-0.413,51.846),
    (-0.410,51.890)
  );

    But here again rounding is not necessarily the best approach when dealing with locations. You may want to have a look at the PostGIS extension, to save the points as geography, add a spatial index, and to search for points within a distance (st_dwithin()) of the input locations.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search