Fastest way to filter lattitude and longitude from a sql table?-CodePudding

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);

CodePudding user response：

If at least one of the ranges of values in X or Y is tight you can try prefiltering rows. For example, is 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);

CodePudding user response：

Do the rounding logic elsewhere. Then, just do a regular search on the already rounded values, this will make your query much faster.