Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created August 14, 2013 15:59
Show Gist options
  • Save andrewxhill/6232460 to your computer and use it in GitHub Desktop.
Save andrewxhill/6232460 to your computer and use it in GitHub Desktop.
PLUTO reverse geocode SQL
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