-
-
Save jl-/2be89cec2be3bb05a60e to your computer and use it in GitHub Desktop.
PostgreSQL fast distance query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH in_radius AS ( | |
SELECT id | |
FROM table | |
WHERE earth_box( ll_to_earth( 51.534901, -0.204573), 5000.00) @> ll_to_earth(table.lat, table.lng) | |
), results AS ( | |
SELECT * | |
FROM spaces | |
WHERE id IN (SELECT id FROM in_radius) | |
) | |
SELECT *, | |
earth_distance(ll_to_earth( 51.534901, -0.204573 ), ll_to_earth(results.lat, results.lng)) as distance | |
FROM results | |
INNER JOIN users on results.user_id = users.id | |
WHERE results.show = 1 | |
ORDER BY distance ASC; | |
// index the tables lat lng float8's for better speed | |
CREATE INDEX ${name_of_index} on table USING gist(ll_to_earth(lat, lng)); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment