Recently I was working on a project which had a need for geocoding. Normally we use PostGIS along with some free data for geocoding. However, to cross check data and to verify results I decided to write a little Python function to see what Google would actually give me.
To come up with the Python function the first thing to do is to install geopy, a module freely available on the web:
|
1 2 3 4 5 6 |
root@chantal:/tmp# git clone https://github.com/geopy/geopy Cloning into 'geopy'... remote: Counting objects: 2848, done. remote: Total 2848 (delta 0), reused 0 (delta 0) Receiving objects: 100% (2848/2848), 913.77 KiB | 504 KiB/s, done. Resolving deltas: 100% (1841/1841), done. |
The stuff can be compiled and deployed easily:
|
1 2 |
root@chantal:/tmp# cd geopy/ root@chantal:/tmp/geopy# python setup.py install |
Then PL/Python has to be enabled:
|
1 2 |
test=# CREATE LANGUAGE plpythonu; CREATE LANGUAGE |
Then the function can already be written. The first thing I have done here is to come up with a data type returned by the function. In the example the position, the longitude and the latitude are relevant:
|
1 2 3 4 5 6 |
CREATE TYPE v_position AS ( location text, lat numeric, lng numeric ); |
Finally there is the function itself, which connects to Google and fetches some data. It is more or less trivial:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION geo (raw_string text) returns setof v_position AS $$ from geopy import geocoders geocoder = geocoders.GoogleV3() try: for res in geocoder.geocode(raw_string, exactly_one=False): yield {'location': res[0], 'lat': res[1][0], 'lng': res[1][1]} except: pass $$ LANGUAGE plpythonu VOLATILE; |
Now we can test the function with some sane input:
|
1 2 3 4 5 6 7 |
test=# x Expanded display is on. test=# SELECT * FROM geo('groehrmuehlgasse 26, wiener neustadt'); -[ RECORD 1 ]---------------------------------------------- location | Gröhrmühlgasse 26, 2700 Wiener Neustadt, Austria lat | 47.8178812 lng | 16.2384326 |
The result gives us the position of our Cybertec headquarter here in Austria. So far so good ...
Unexpected results …
To see if my function works properly I tried to come up with a string, which seemed pretty unlikely to me:
|
1 2 3 4 5 |
test=# SELECT * FROM geo('dirt hole'); -[ RECORD 1 ]-------------------------------------------------- location | Dirt Hole Creek Road, Tambaroora NSW 2795, Australia lat | -32.9826131 lng | 149.4264436 |
Well, obviously some places are not blessed with proper names. There seem to be dirt holes in Australia after all 😉
Let us focus our attention on some more “religious” question:
|
1 2 3 4 5 |
test=# SELECT * FROM geo('infidel'); -[ RECORD 1 ]----------------------------------------------- location | Infidel Place, Torbay, Auckland 0630, New Zealand lat | -36.7024209 lng | 174.746813 |
It seems that even this leads to a result ...
But, this is pointless.
|
1 2 3 4 5 |
test=# SELECT * FROM geo('pointless'); -[ RECORD 1 ]-------------------------------------------- location | Truax SE5, Squamish-Lillooet A, BC V0N, Canada lat | 50.7844444 lng | -122.6816666 |
… or maybe just insane ...
|
1 2 3 4 5 6 7 8 9 10 |
test=# SELECT * FROM geo('insane'); -[ RECORD 1 ]-------------------------------------------------------- location | Insane, 115 21 Stockholm, Sweden lat | 59.3236986 lng | 18.095511 -[ RECORD 2 ]-------------------------------------------------------- location | Insane, The Tongass National Forest, Juneau, AK 99801, USA lat | 58.2691792 lng | -134.5122587 |
Maybe we should not trust everything returned by a webservice
We have some more great GIS posts for your right here on our GIS blog spot... enjoy!
+43 (0) 2622 93022-0
office@cybertec.at