Skip to content

Instantly share code, notes, and snippets.

@zross
Forked from andrewxhill/pluto_reverse_geocode.sql
Created October 7, 2013 12:47
Show Gist options
  • Save zross/6867344 to your computer and use it in GitHub Desktop.
Save zross/6867344 to your computer and use it in GitHub Desktop.
CREATE TYPE pluto_reverse_geocode_result AS (address text, ownername text,distance float);
CREATE OR REPLACE FUNCTION pluto_reverse_geocode(float,float,int) RETURNS SETOF pluto_reverse_geocode_result
AS '
WITH subq as (SELECT address,the_geom,ownername
FROM nyc_mappluto_13v1
ORDER BY the_geom <-> CDB_LatLng($1,$2) LIMIT 20)
SELECT address,ownername,
ST_Distance(the_geom::geography, CDB_LatLng($1,$2)::geography) as distance
FROM subq
WHERE ST_Distance(the_geom::geography, CDB_LatLng($1,$2)::geography) < $3
ORDER BY ST_Distance(the_geom::geography, CDB_LatLng($1,$2)::geography) ASC LIMIT 3
'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment