Sample Header Ad - 728x90

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