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.
Best Answer
You may consider using a GIST index based on using the function
ll_to_earth
. This index will allow for fast "nearby" searches.Once you have this index, your query should be done in a different way.
Your (lat, lng) pairs need to be converted to the
earth
type, and compared with the indexed values (which are of the same type). Your query will need to have two conditions, one for "approximate" result, and one for the "precise" one. The first one will be able to use the previous index:For using this code, you need two extensions (included in most PostgreSQL distributions):
This is the documentation for them:
earth_box
andearth_distance
. This module assumes that the earth is spherical, which is an approximation good enough for the majority of applications.A test with a table consisting of 2.2 million rows taken from the Free World Cities Database gives me the following answer to the previous query (which is not exactly the same as yours):
To have an "order of magnitude" idea about timings: pgAdmin III is telling me that the time to get this answer is 22 ms. (PostgreSQL 9.6.1 with "out-of-the-box" parameters, on a Mac with Mac OS 10.12, Core i7, SSD)