How do you geocode addresses in a CSV file fast with Bing Maps

Question

I'm creating an application that lets a user to upload a CSV file and have an address read from each line and geocoded.

I originally tried to do this with the google map API. But after some research i found that the google map api has a lot of limitation. For example, you can do only 2500 query in a day and also per query you need a time delay.

This is the code I've written so far:

<script src="http://maps.googleapis.com/maps/api/js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<table width="600">
<form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="post" enctype="multipart/form-data">

<tr>
<td width="20%">Select file</td>
<td width="80%"><input type="file" name="file" id="file" /></td>
</tr>

<tr>
<td>Submit</td>
<td><input type="submit" name="submit" /></td>
</tr>

</form>
</table>

<?php    
if ( isset($_POST["submit"]) ) {

   if ( isset($_FILES["file"])) {

            //if there was an error uploading the file
        if ($_FILES["file"]["error"] > 0) {
            echo "Return Code: " . $_FILES["file"]["error"] . "<br />";

        }
        else {
                 //Print file details
             echo "Upload: " . $_FILES["file"]["name"] . "<br />";
             echo "Type: " . $_FILES["file"]["type"] . "<br />";
             echo "Size: " . ($_FILES["file"]["size"] / 1024) . " Kb<br />";
             echo "Temp file: " . $_FILES["file"]["tmp_name"] . "<br />";
             $name = $_FILES['file']['name'];
                $ext = strtolower(end(explode('.', $_FILES['file']['name'])));
                $type = $_FILES['file']['type'];
                $tmpName = $_FILES['file']['tmp_name'];

    // check the file is a csv
    if($ext === 'csv'){
        if(($handle = fopen($tmpName, 'r')) !== FALSE) {
            // necessary if a large csv file
            set_time_limit(0);
            $row = 1;
            while((($data = fgetcsv($handle, 1000, ',')) !== FALSE) && $row!=10) {
                // number of fields in the csv
                $col_count = count($data);
            $address=implode(",",$data);
            $geocode=file_get_contents("http://maps.google.com/maps/api/geocode/json?address=".$address."&sensor=false");
            print_r($geocode);
            $output= json_decode($geocode);

            $lat = $output->results[0]->geometry->location->lat;
            $lng = $output->results[0]->geometry->location->lng;
            sleep(1);
                        print_r($address);
                        echo "------".$lat."----".$lng;
                        echo "<br/>";
                $row++;
            }
            fclose($handle);
        }

}
        }
     } else {
             echo "No file selected <br />";
     }
}   
?>

My question is, how can I do this with the Bing Maps API.

Here is a sample CSV file:

https://drive.google.com/open?id=0Bx3FBqTEy_0MaHp1QVhiNkVTLU0

score 0 · Answer 1 · answered Sep 28 '15 at 16:43

Bing Maps doesn't limit the number of requests you make per day, however there are limits when using the service under the free terms of use. Web based apps are limited to 125,000 transactions per year.

With Bing Maps there are three ways to do geocoding. The first method is to use a REST based service similar to what you are using with Google Maps. You can find documentation on this service here: https://msdn.microsoft.com/en-us/library/ff701711.aspx

I also recommend taking a look at the best practices document if you use this service: https://msdn.microsoft.com/en-us/library/dn894107.aspx

You can also find some tips on how to use this service in PHP here: https://msdn.microsoft.com/en-us/library/ff817004.aspx

The second method you can use to geocode is to use the Search Module that is in Bing Maps. The search module wraps the REST services and exposes them as an easy to use JavaScript library. https://msdn.microsoft.com/en-us/library/hh868062.aspx

The third option, is to use the Bing Spatial Data Services and batch geocode your data. If you have large data sets this is the service to use. You can pass in up to 200,000 rows of data in a single request. There is a CSV file format that is supported by this service that you likely would be able to easily conform to. However, if you are using the service under the free terms of use you are limited to 50 rows of data. https://msdn.microsoft.com/en-us/library/ff701733.aspx

Here is some information on how to use this service in PHP: https://msdn.microsoft.com/en-us/library/ff817006.aspx

There are a few things to be aware of when using any of these services. The first is that you can only use the data with Bing Maps. You can't geocode the data with the Bing Maps services and then display the coordinates on Google Maps or any other mapping platform that doesn't use Bing Maps.

The second is that Bing Maps allows you to store the results from the geocoder. A standard optimization that is used in most applications consists of geocoding data ahead of time. This would reduce the need to re-geocode the data every time the data is needed. Now, in your case it looks like anyone will be able to upload a CSV file. You could geocode all the rows on demand, but that will generate a lot of transactions and likely result in you hitting the free terms of use limit fast. Another option would be to store the results in a database and then when geocoding your data check the database before sending the request to Bing Maps. This would save you re-geocoding any data.

It's also good to be aware of rate limiting. Basic accounts (free accounts) are subject to rate limiting. This occurs when your account makes a large number of requests in a very short period of time or when the servers are under a lot of load from basic accounts. What happens is that some requests will return with empty responses. The header of the response will have a flag that indicates that the request was limited. You can find documentation this here: https://msdn.microsoft.com/en-us/library/ff701703.aspx

Finally, if you simply want to see your data on a map and it doesn't have to be web based. Take a look at Power Map for Excel 2013 and above. You would be able to take your CSV and view it on a map in minutes without having to write any code. Here are some useful resources on Power Map:

https://www.microsoft.com/en-us/download/details.aspx?id=38395

https://support.office.com/en-us/article/Get-started-with-Power-Map-88A28DF6-8258-40AA-B5CC-577873FB0F4A

How do you geocode addresses in a CSV file fast with Bing Maps

1 Answers1