CODEWITHSUNDEEP
sqlpostgresqlcoordinateswhere-clause
data en
data en

Reputation: 595

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

Upvotes: 0

Views: 1084

Answers (2)

JGH
JGH

Reputation: 17846

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.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48780

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

Upvotes: 2

Related Questions