Created
March 4, 2020 11:36
-
-
Save svilensabev/4f06eedf58665214279063c76e326980 to your computer and use it in GitHub Desktop.
PostgreSQL function to find points in distance
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
BEGIN; | |
CREATE OR REPLACE FUNCTION api.geog_points_in_distance(latitude NUMERIC, longitude NUMERIC, distance NUMERIC) | |
RETURNS JSON | |
SECURITY DEFINER | |
LANGUAGE PLPGSQL STABLE | |
SET search_path TO PUBLIC, api | |
AS $$ | |
DECLARE | |
_lat NUMERIC DEFAULT latitude::NUMERIC; | |
_lng NUMERIC DEFAULT longitude::NUMERIC; | |
_distance NUMERIC DEFAULT COALESCE(distance::NUMERIC, 500); | |
result JSONB; | |
BEGIN | |
WITH geog_points AS ( | |
SELECT | |
gp.* | |
, ST_Distance(gp.geog, ST_SetSRID(ST_MakePoint(_lng, _lat),4326)) AS distance_4326 | |
FROM api.geog_point AS gp | |
WHERE ST_DWithin(gp.geog, ST_Point(_lng, _lat)::geography, _distance) | |
ORDER BY gp.geog::geometry <-> ST_SetSRID(ST_MakePoint(_lng, _lat),4326) | |
) | |
SELECT | |
json_agg(geog_points) | |
FROM geog_points | |
INTO result; | |
RETURN result; | |
END $$; | |
GRANT EXECUTE ON FUNCTION api.geog_points_in_distance(latitude NUMERIC, longitude NUMERIC, distance NUMERIC) TO app_anonymous; | |
COMMIT; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment