How can I speed-up my query on geo-location processes
7
votes
2
answers
8472
views
I have a table that contains 10,301,390 GPS records, cities, countries and IP address blocks. I have user's current location with latitude and longitude. I created this query:
SELECT
*, point(45.1013021, 46.3021011) point(latitude, longitude) :: point AS distance
FROM
locs
WHERE
(
point(45.1013021, 46.3021011) point(latitude, longitude)
) < 10 -- radius
ORDER BY
distance LIMIT 1;
This query successfully gave me what I want, but it is slow. It took 2 to 3 seconds to get one record by given latitude and longitude.
I tried a B-Tree index on the
latitude
and longitude
columns, also tried GIST( point(latitude, longitude));
but still querying is slow.
How can I speed up this query?
# Update:
It seems slowness is caused by the ORDER BY
but I want to get the shortest distance, so the question remains.
Asked by xangr
(457 rep)
Dec 16, 2016, 10:40 AM
Last activity: Apr 1, 2022, 04:58 PM
Last activity: Apr 1, 2022, 04:58 PM