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.

  • 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, 2013 at 4:28

12 Answers 12


Mehul, I used to 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 process it using the Bulk Address Validation Tool for Lists or Command Line Tool. 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 services 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, US Street Address 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.

  • 1
    Not familiar with SmartyStreets, looks promising, thanks for the heads up. Mar 22, 2012 at 17:54
  • 1
    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, 2012 at 19:10
  • 2
    SmartyStreets only geocodes for the US?
    – Mapperz
    Mar 23, 2012 at 21:32
  • I have data which is for Singapore will it work ? If not any directions you could give me ???? Mar 26, 2012 at 13:14
  • 1
    SmartyStreets does offer international address verification now. @user1089553
    – camiblanch
    Sep 16, 2015 at 17: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' in my_output folder:

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

def geocode(address):
    g = geocoders.GoogleV3()
    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):
    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)
    print 'Shapefile %s created...' % ds.name
    # read text addresses file
    i = 0
    f = open(filepath, 'r')
    for address in f:
            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.SetField('id', i)
            feat.SetField('ADDRESS', address)
            i = i + 1
            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.

  • This is great! Thanks man! Anyway, at present (01/2016), 'geocoders.Google()' should be changed to 'geocoders.GoogleV3()' as in geopy.readthedocs.org/en/1.11.0
    – umbe1987
    Jan 12, 2016 at 17:02

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!

  • 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 Mar 22, 2012 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, 2012 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! Mar 22, 2012 at 14:48
  • @Tamas Kind of. See this, however: developers.google.com/maps/terms#section_10_1_3
    – Matt
    Mar 22, 2012 at 17:41

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


by far the best and easiest geocoder I have used https://pypi.python.org/pypi/geocoder/1.8.0 bing maps, google maps, OSM etc..


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.

  • 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. Mar 22, 2012 at 9:30
  • 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.
    – Celik A
    Mar 22, 2012 at 11:07

Matej, Thats because Google API allow to pull up to 2.5k per day.
About the Geo solution, batch is not yet found to be supported that's because from my review of the geo python code it's seems to open connection every time he request a new cordinate, 300k will probably stuck forever (probably with error 400).
Play with Poligons should do the trick but it's depends what is your 'Play ground' area, if it's 1 country or n countries.
For 1 country the polygons should work pretty nice.
For n countries the solution won't work since the collection will take longer anytime you add another country. The best aproch to do it is lazy load.
=> start with the polygon idea, every thing in another country , create a big database table to hold the data, eventually you will hold the data you need I suppose.


If you want to do it with PHP - MySQL here is a solution that worked for me:

<script type="text/javascript" charset="utf-8">

    var customIcons = {
      restaurant: {
        icon: 'http://labs.google.com/ridefinder/images/mm_20_blue.png',
        shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
      bar: {
        icon: 'http://labs.google.com/ridefinder/images/mm_20_red.png',
        shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png'
        icon: 'http://labs.google.com/ridefinder/images/mm_20_yellow.png',
        shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png' 
        icon: 'http://labs.google.com/ridefinder/images/mm_20_green.png',
        shadow: 'http://labs.google.com/ridefinder/images/mm_20_shadow.png' 

      function initialize() 
        var mapOptions = {
          center: new google.maps.LatLng(37.976178, 23.735881),
          zoom: 7,
          mapTypeId: google.maps.MapTypeId.roadmap
        var map = new google.maps.Map(document.getElementById("map-canvas"),
        <?php header("content-type: text/html;charset=utf-8");
        $getpoints = "SELECT lat, lng, name, address, type FROM markers";
        $getpoints .= $filter;

        if(!$result = $con->query($getpoints)){
        die('There was an error running the query 
        [' . $con->error . ']');

            while ($row = $result->fetch_assoc()) 
                $thematic = "'$row[type]'";
                $name = "'$row[name]'";
                $map_address = "$row[address]";

                $url = "http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=".urlencode($map_address);
                $lat_long = get_object_vars(json_decode(file_get_contents($url)));

                // pick out what we need (lat,lng)
                $lat_long = $lat_long['results'][0]->geometry->location->lat . "," . $lat_long['results'][0]->geometry->location->lng;

                echo "var myLatlng1 = new google.maps.LatLng($lat_long); 
                var icon = customIcons[$thematic] || {};
                var marker1 = new google.maps.Marker({ 
                position: myLatlng1, 
                map: map,
                icon: icon.icon,
                title: '$map_address'


      google.maps.event.addDomListener(window, 'load', initialize);

Try this geocoding api. It's free for small usage but if you want more they make you pay. It is cheap however and you could process that much easily, I process millions a month through them.


Use Mappointing tools ( Map pointing | Batch Geocoding Tool (http://www.mappointing.com/) ) In this tool you can process the data using Google map free API key. And also this tool is providing Distance calculation & Place search tool.

  • Why wouldn't this hit the same API limit OP mentioned? Nov 23, 2017 at 22:09

You can save your data as a text file (one record per line) then batch geocode it using this service: http://geocode.xyz/batch (works for most european countries)

or, you can write your own code to access the REST/JSON API: http://geocode.xyz/api (it is free for unlimited lookups)


You can give Geokeo APIs a try. They provide spell correction and provide data from many open source data sources thus improved accuracy. You can easily give it a try on their demo page- Demo without any registration or API keys. A free tier of 2500 daily requests are also available. code implementation related to Python, PHP , jQuery etc are available at their documentation page for easy implementation - Documentation


  • If you are affiliated with Geokeo please indicate your affiliation.
    – Aaron
    Sep 27, 2021 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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