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