Skip to content

Instantly share code, notes, and snippets.

@jatorre jatorre/url.txt
Created Feb 1, 2015

Embed
What would you like to do?
SELECT *, 'http://maps.googleapis.com/maps/api/streetview?size=300x190&location='||urlencode (address)||'&sensor=false&fov=110' as image_with_address
FROM example_addresses
CREATE OR REPLACE FUNCTION urlencode(in_str text, OUT _result text)
STRICT IMMUTABLE AS $urlencode$
DECLARE
_i int4;
_temp varchar;
_ascii int4;
BEGIN
_result = '';
FOR _i IN 1 .. length(in_str) LOOP
_temp := substr(in_str, _i, 1);
IF _temp ~ '[0-9a-zA-Z:/@._?#-]+' THEN
_result := _result || _temp;
ELSE
_ascii := ascii(_temp);
IF _ascii > x'07ff'::int4 THEN
RAISE EXCEPTION 'Won''t deal with 3 (or more) byte sequences.';
END IF;
IF _ascii <= x'07f'::int4 THEN
_temp := '%'||to_hex(_ascii);
ELSE
_temp := '%'||to_hex((_ascii & x'03f'::int4)+x'80'::int4);
_ascii := _ascii >> 6;
_temp := '%'||to_hex((_ascii & x'01f'::int4)+x'c0'::int4)
||_temp;
END IF;
_result := _result || upper(_temp);
END IF;
END LOOP;
RETURN ;
END;
$urlencode$ LANGUAGE plpgsql;
@jatorre

This comment has been minimized.

Copy link
Owner Author

commented Feb 1, 2015

First you will need to install the urlencode function by running it on the SQL window. Then you can use a SQL like this to get the Street View by address.

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.