[OSGeo-Discuss] Batch geocoding

Dan Putler dan.putler at sauder.ubc.ca
Fri Feb 4 15:17:59 PST 2011

Glad to be of help, I'm just curious about what the licensing / terms of 
use on the KML files that come out of fusion tables. Plus I want to know 
if it will reverse geocode. It is very slick, but I can imagine 
violating the terms of use almost instantly.

In terms of your next problem (which seems like one for the PostGIS user 
list), did you create a unique address ID? if you did, can't you put it 
in the data you created the fusion table from and then just do the 
appropriate table join in Postgres/PostGIS?


On 02/04/2011 03:08 PM, JP Glutting wrote:
> That is a nice trick, I will remember that. I really just need the 
> coordinates, since each coordinate needs to go back into a database in 
> a register of a location-time that needs more data added later. These 
> are the "generic" addresses, with the apartment numbers stripped, so 
> each address will provide coordinates for multiples DB entries on 
> different dates, and I won't really be using them on the same layer. 
> But I am sure I can figure out a way to get the data back out with 
> PostGIS (the original data is in Postgres anyway).
> Cheers,
> JP
> On Fri, Feb 4, 2011 at 11:59 PM, Dan Putler <dan.putler at sauder.ubc.ca 
> <mailto:dan.putler at sauder.ubc.ca>> wrote:
>     JP,
>     If you export the KML, and then feed it into QGIS you can then
>     save the resulting layer as a shapefile, which is probably what
>     you are really hoping to have since you can then easily feed it to
>     R, PostGIS, or a number of other tools to pull the coordinates.
>     Dan
>     On 02/04/2011 02:20 PM, JP Glutting wrote:
>         Hi Bernie,
>         You can't actually get the lat-lng back out of a Fusion table.
>         It will geocode all the addresses, but you can't get the
>         numbers back, you can just make a map or layer or something.
>         http://groups.google.com/group/fusion-tables-users-group/browse_thread/thread/91c43c73107164eb
>         Unless I export it to KML and pull it back out of there?
>         That seems like a lot of work. But it sure geocodes like
>         gangbusters.
>         Cheers,
>         JP
>         Public Health - GIS - Bioinformatics
>         http://www.bmeisis.com <http://www.bmeisis.com/>
>         http://www.jpglutting.com
>         On Fri, Feb 4, 2011 at 7:42 PM, Connors, Bernie (SNB)
>         <Bernie.Connors at snb.ca <mailto:Bernie.Connors at snb.ca>
>         <mailto:Bernie.Connors at snb.ca <mailto:Bernie.Connors at snb.ca>>>
>         wrote:
>            Try Google Fusion Tables.  All you have to do is put all of the
>            address components in one field and it should be recognized and
>            geocoded.
>            --
>            *Bernie Connors, P.Eng*
>            Service New Brunswick
>            (506) 444-2077
>            45°56'25.21"N, 66°38'53.65"W
>         www.snb.ca/geonb/ <http://www.snb.ca/geonb/>
>         <http://www.snb.ca/geonb/>
>            *From:*discuss-bounces at lists.osgeo.org
>         <mailto:discuss-bounces at lists.osgeo.org>
>         <mailto:discuss-bounces at lists.osgeo.org
>         <mailto:discuss-bounces at lists.osgeo.org>>
>            [mailto:discuss-bounces at lists.osgeo.org
>         <mailto:discuss-bounces at lists.osgeo.org>
>         <mailto:discuss-bounces at lists.osgeo.org
>         <mailto:discuss-bounces at lists.osgeo.org>>] *On Behalf Of *JP
>         Glutting
>            *Sent:* Friday, 2011-02-04 09:34
>            *To:* discuss at lists.osgeo.org
>         <mailto:discuss at lists.osgeo.org>
>         <mailto:discuss at lists.osgeo.org <mailto:discuss at lists.osgeo.org>>
>            *Subject:* [OSGeo-Discuss] Batch geocoding
>            Hello,
>            I have a large set of addresses (around 150k) that I need to
>            geocode for a study (my Masters thesis on heat-related
>         mortality).
>            I am looking into different solutions, but I can't find
>         anything
>            that seems like it would work properly.
>            I could script a solution using Google's map API, but there
>         is a
>            limit of 2,500 addreses per day (I can get around them with a
>            little patience).
>            Right now the best solution I am looking at geopy for geocoding
>            addresses (http://code.google.com/p/geopy/). It seems like
>         a good
>            system, I think I can use it to pull addresses out of my
>         database
>            and write back coordinates. There is one thing that I am
>         not sure,
>            about, though, is whether I am actually allowed to use the
>         Google
>            API without my use being liked to a specific web page. The
>         terms
>            of service and form for getting a Google API key require a URL
>            linked to a Google account. In fact, it looks like the API can
>            only be used through a web site:
>                "5.2 _Account Key_. After supplying Google with your
>         account
>                information and the URL of your Maps API
>         Implementation, and
>                accepting the Terms, you will be issued an alphanumeric key
>                assigned to you by Google that is uniquely associated with
>                your Google Account and the URL of your Maps API
>                Implementation. Your Maps API Implementation must
>         import the
>                Google Maps APIs using this key as described in the
>         Maps APIs
>                Documentation
>         <http://code.google.com/apis/maps/documentation/>, and Google
>                will block requests with an invalid key or invalid URL. You
>                may only obtain and use a key in accordance with these
>         Terms
>                and the Maps APIs Documentation
>         <http://code.google.com/apis/maps/documentation/>."
>            So it looks like I can't even get it to work without a URL.
>            I can always write a script that loops through results
>         extracted
>            from the database, creates URLs and parses the XML results
>         one at
>            a time, but that seems like a fairly inelegant solution.
>            Does anyone have any good ideas about how to geocode a few
>            thousand addresses?
>            Many thanks,
>            JP
>            _______________________________________________
>            Discuss mailing list
>         Discuss at lists.osgeo.org <mailto:Discuss at lists.osgeo.org>
>         <mailto:Discuss at lists.osgeo.org <mailto:Discuss at lists.osgeo.org>>
>         http://lists.osgeo.org/mailman/listinfo/discuss
>     _______________________________________________
>     Discuss mailing list
>     Discuss at lists.osgeo.org <mailto:Discuss at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/discuss

More information about the Discuss mailing list