jl-
12/31/2015 - 6:07 PM

PostgreSQL fast distance query

PostgreSQL fast distance query

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