Skip to content

Instantly share code, notes, and snippets.

@jatorre jatorre/gist:7210438
Created Oct 29, 2013

Embed
What would you like to do?
Geocoder/Reverse geocoder inside SQL
CREATE TYPE geocode_response AS (match_level text, latitude float, longitude float, address text, the_geom geometry);
CREATE OR REPLACE FUNCTION cdb_geocode(text) RETURNS SETOF geocode_response
AS $$ DECLARE
token text :='xxxx';
app_id text :='xxxx';
BEGIN
RETURN QUERY WITH geocode_response AS (
SELECT status, content_type,
content::json->'Response'->'View'->0->'Result'->0 as geocode_result
FROM http_get('http://geo.nlp.nokia.com/search/6.2/search.json?searchtext='
|| url_encode($1)
|| '&token='|| url_encode(token) ||'&app_id='||url_encode(app_id))
)
SELECT
geocode_result->>'MatchLevel' as match_level,
(geocode_result->'Location'->'DisplayPosition'->>'Latitude')::float as latitude,
(geocode_result->'Location'->'DisplayPosition'->>'Longitude')::float as longitude,
geocode_result->'Location'->'Address'->>'Label' as address,
ST_SetSrid(ST_MakePoint(
(geocode_result->'Location'->'DisplayPosition'->>'Longitude')::float,
(geocode_result->'Location'->'DisplayPosition'->>'Latitude')::float
),4326) as the_geom
FROM geocode_response;
END;
$$
LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE TYPE reversegeocode_response AS (address text,match_level text);
CREATE OR REPLACE FUNCTION cdb_reversegeocode(geometry) RETURNS SETOF reversegeocode_response
AS $$ DECLARE
token text :='A7tBPacePg9Mj_zghvKt9Q';
app_id text :='KuYppsdXZznpffJsKT24';
BEGIN
RETURN QUERY WITH geocode_response AS (
SELECT status, content_type,
content::json->'Response'->'View'->0->'Result'->0 as geocode_result
FROM http_get('http://geo.nlp.nokia.com/search/6.2/reversegeocode.json?mode=retrieveAddresses&prox='||ST_Y($1) ||','||ST_X($1) ||'&token='|| token ||'&app_id='|| app_id ||'')
)
SELECT
geocode_result->'Location'->'Address'->>'Label' as address,
geocode_result->>'MatchLevel' as match_level
FROM geocode_response;
END;
$$
LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT;
---TESTING
SELECT st_x(the_geom),st_y(the_geom), (SELECT address FROM cdb_reversegeocode(the_geom)) FROM cdb_geocode('154 N Riverside Plz, Chicago, IL 60606, United States');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.