Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a database which has 300,000 address, which are to be shown on map. I know if I geo-code all the address it will be too expensive for me. So I was wondering if it is possible to geo-code the address on the fly/ realtime, when a user would select a address (a property address) it would search through the database and then geo-code the address and then map it with other attributes.

It would be really nice if you could share a code, concept, or anything. By the way my backend is on mysql supported by Joomla.

Thanks.

share|improve this question
    
I generally use the ArcGIS geocoding capability to geocode large number of addresses. Also, I'm really interested about the processes explained here, specially, like to test the python script to geocode the addresses using Google, then compare them with what I've got from ArcGIS. Unfortunately, it seems to me that I'm not able to find all of the related files and scripts which are in different places. It would be so appreciated if someone please send me all of the scripts in one zip file or any step by step instruction. I've tried to use it and get some feedback to you, but I was not successfu –  Housh Nov 11 '13 at 4:28

5 Answers 5

up vote 6 down vote accepted

Mehul, I work in the address verification industry with a company called SmartyStreets. There are lots of geocoding services out there, but only few will support batch processing with the volume you require. (Google and others don't permit bulk use of their API or storing/caching results.)

If you go to your MySQL database and perform an export of your table which contains the addresses, save it as a CSV file for example. You can then upload it to a batch service like LiveAddress for Lists. Like I said, there are several services out there, but you'll want something, I presume, that verifies the existence of addresses too (hence the reason for geocoding) -- if the address is wrong or incomplete, so are the geocoding results. Only a few do this.

LiveAddress is a service which is CASS-Certified by the USPS. There are a few out there so do your research, but you want something "on-the-fly"/quick and inexpensive so again I recommend LiveAddress. It'll not only verify the address but then do as you require which is supply lat/lon information and also the precision of the geocoding results. It's all web-based and will process tens of millions of records in no time (see this question as a reference).

If you have further need to geocode addresses as users are interacting, LiveAddress also has an API version which can plug into just about anything and it also supports batch processing on-the-fly, but is paid as a subscription, not a one-time payment.

I'll be happy to personally answer any of your other questions about addresses. Hope this is helpful.

share|improve this answer
    
Not familiar with SmartyStreets, looks promising, thanks for the heads up. –  Derek Swingley Mar 22 '12 at 17:54
    
The LiveAddress API will do 300,000 in approximately 5-10 minutes. The LiveAddress for Lists service (upload a list for processing) takes 15-20 minutes. Both pretty speedy. The List service will not require you to write any code. –  Jeffrey Mar 23 '12 at 19:10
1  
SmartyStreets only geocodes for the US? –  Mapperz Mar 23 '12 at 21:32
    
Yes, currently SmartyStreets only works on US Postal Service address, which means that it also services US territories such as Puerto Rico and other remote locations but it is not yet an international service. –  Jeffrey Mar 23 '12 at 21:41
    
I have data which is for Singapore will it work ? If not any directions you could give me ???? –  user1089553 Mar 26 '12 at 13:14

If you like Python, you could use the GeoPy API, combined with the GDAL Python bindings or Fiona, and create a very basic script like this for converting the addresses to a point shapefile.

This will geolocate a file named 'addresses_to_geocode', creating an output shapefile named 'my_output.shp':

import os
from geopy import geocoders
from osgeo import ogr, osr

def geocode(address):
    g = geocoders.Google()
    place, (lat, lng) = g.geocode(address)
    print '%s: %.5f, %.5f' % (place, lat, lng)
    return place, lat, lng

def parse_file(filepath, output_shape):
    # create the shapefile
    drv = ogr.GetDriverByName("ESRI Shapefile")
    if os.path.exists(output_shape):
        drv.DeleteDataSource(output_shape)
    ds = drv.CreateDataSource(output_shape)
    # spatial reference
    sr = osr.SpatialReference()
    sr.ImportFromProj4('+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')
    lyr = ds.CreateLayer(output_shape, sr, ogr.wkbPoint)
    # fields
    featDefn = lyr.GetLayerDefn()
    fld_id = ogr.FieldDefn('id', ogr.OFTInteger)
    fld_address = ogr.FieldDefn('ADDRESS', ogr.OFTString)
    fld_address.SetWidth(255)
    lyr.CreateField(fld_id)
    lyr.CreateField(fld_address)
    print 'Shapefile %s created...' % ds.name
    # read text addresses file
    i = 0
    f = open(filepath, 'r')
    for address in f:
        try:
            print 'Geocoding %s' % address
            place, lat, lng = geocode(address)
            point = ogr.Geometry(ogr.wkbPoint)
            point.SetPoint(0, lng, lat)
            feat = ogr.Feature(lyr.GetLayerDefn())
            feat.SetGeometry(point)
            feat.SetField('id', i)
            feat.SetField('ADDRESS', address)
            lyr.CreateFeature(feat)
            feat.Destroy()
            i = i + 1
        except:
            print 'Error, skipping address...'

parse_file('addresses_to_geocode', 'my_output')

The file is supposed to have just a line for a single address, like for example:

Via Benedetto Croce 112, Rome, Italy
Via Aristide Leonori 46, Rome, Italy
Viale Marconi 197, Rome, Italy

Here I am using the Google API, but with GeoPy is very basic to switch to differents API, like Yahoo!, GeoNames, or MapPoint.

share|improve this answer

Another option tol solve your problem would be to import your dataset to fusion tables and set the address field as location. Then it will geocode the points automatically. Once thats complete, you can export the data as KML.

Or..alternatively, you can write a php script to make use of the yahoo geocoder which has a limit of 50 000 records, so sooner or later you will have all of your points geocoded in your database.

I hope this helped!

share|improve this answer
    
thanks tamas but i would not like to get the kml and then fetch the information from there and then to m db. I do like to idea of yahoo geocoding but i am not too sure of the accuracy it has, as i have never used yahoo for mapping. Please let me know if you have any script written or some thing. This is a great help –  user1089553 Mar 22 '12 at 9:35
    
Keep in mind that using Yahoo (or Google, for that matter) geocoder with automated queries or without showing a map will violate TOS... –  Matt Mar 22 '12 at 12:11
    
As far as I know, it is not if you present the output on a map. Correct me if I am wrong! –  Tamas Mar 22 '12 at 14:48
    
@Tamas Kind of. See this, however: developers.google.com/maps/terms#section_10_1_3 –  Matt Mar 22 '12 at 17:41

Maybe not the best answer to your question but you may try BatchGeo. Free version would make you suffer a lot, but still was good enough for my work. Though, we have bought the pro version.

Trick to get coordinates from KML file is to import it to ArcGIS later on.

share|improve this answer
    
Thanks, can you tell me how do I do a BatchGeo, also would you know the names of free version (I was trying for Google Map api v3). Also does this mean that I have to store the Lat/Long values in my database in order to map it. This is what i intended to do in the first place. –  user1089553 Mar 22 '12 at 9:30
    
Anil thanks for your comments –  user1089553 Mar 22 '12 at 9:35
    
With BatchGeo, you will have to import KML to your database then extract the coordinates, I know no other way as Google forbids to provide coordinates. About yahoo, my experience with Turkey is not really bright. Most developing countries are left out of Yahoo's scope. Anil. –  Anıl Çelik Mar 22 '12 at 11:07
    
@ Anil Thanks it was of good help :)_ –  user1089553 Mar 22 '12 at 11:18

I've been successfully using geopy that uses Google geocoding web service. It works perfectly for up to 2k points per 24 hours.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.