[OSGeo-Discuss] Batch geocoding

JP Glutting jpglutting at gmail.com
Fri Feb 4 15:37:04 PST 2011

It is pretty slick, but I just started using it about an hour ago, so I
haven't thought much about what to do with it. I did see a comment where
someone from Google said that reverse geocoding is not possible yet.

I am going to have to get up to speed with PostGIS, but this is a pretty
simple operation. I didn't number the addresses, but each event has a real
address and a "cleaned" address column in a table. The CSV that I uploaded
to Fusion was just a "select distinct..." of the cleaned addresses, so it
will serve as a lookup table for all the addresses in the original table.
I'm not sure how I am going to reorganize the tables afterward, but I want
to interpolate temperatures for each data point based on other data that I
have, and I think PostGIS only does simpler operations right now, so I might
 have to export the points into shapefiles for each day an event occurs and
do splines in another package, then copy the interpolated values back into
the database. I am sure there is a more elegant way to do that, I really
need to think about it some more.

(Oh, hey, I just saw that you can compile R into Postgres. That is pretty
crazy, but potentially totally awesome:


On Sat, Feb 5, 2011 at 12:17 AM, Dan Putler <dan.putler at sauder.ubc.ca>wrote:

> 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?
> Dan
> 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
> _______________________________________________
> Discuss mailing list
> Discuss at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/discuss
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/discuss/attachments/20110205/d5b3d1d7/attachment-0002.html>

More information about the Discuss mailing list