Python Forum

have list of companies in excel with address , i need to get Latitude and Longitude details for 300 companies.

I found the below code

import requests

response = requests.get('https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA')

resp_json_payload = response.json()

print(resp_json_payload['results'][0]['geometry']['location'])

Now i need to input a file which will have 300 companies address and loop it to the above code and get the output in Excel file using python.

Regards, San

Output:
In [2]: import requests

In [3]: response = requests.get('https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphithe
   ...: atre+Parkway,+Mountain+View,+CA').json()

In [4]: response
Out[4]: 
{'error_message': 'You must use an API key to authenticate each request to Google Maps Platform APIs. For additional information, please refer to http://g.co/dev/maps-no-account',
 'results': [],
 'status': 'REQUEST_DENIED'}

You need a devloper api key: http://g.co/dev/maps-no-account

This procedure is called geocoding, and you can use OSM for free without any API Developer key.

from geopy.geocoders import Nominatim


def geocode(addresses):
    locator = Nominatim(user_agent="My App Name")
    for address in addresses:
        result = locator.geocode(address)
        yield address, (result.latitude, result.longitude)



example_addresses = ["Kaufland Hohenlimburg", "Berlet Euronics Hagen", "Wieblingwerde", "SIEMENS Nürnberg", "Hoesch Hohenlimburg"]


for address, coords in geocode(example_addresses):
    print(address, coords)

Output:
Kaufland Hohenlimburg (51.36398765, 7.584049323649667)
Berlet Euronics Hagen (51.362861300000006, 7.558072424181042)
Wieblingwerde (51.3097517, 7.6151142)
SIEMENS Nürnberg (49.43815305, 11.075688213230517)
Hoesch Hohenlimburg (51.341030599999996, 7.573515683167329)

Here is the documentation: https://geopy.readthedocs.io/en/stable/#installation

DeaD_EyE - Good Choice!
love OSM!

Thanks , it will work. but i have almost 300+ addresses needs to check the Lat and Long, manually passing those address is really tough , is there anyway we can give the input from file? example i have an excel which contains address and the output needs to update in the same excel or separate file. is that possible?

(Jun-07-2021, 06:00 PM)DeaD_EyE Wrote: [ -> ]

Output:
In [2]: import requests

In [3]: response = requests.get('https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphithe
   ...: atre+Parkway,+Mountain+View,+CA').json()

In [4]: response
Out[4]: 
{'error_message': 'You must use an API key to authenticate each request to Google Maps Platform APIs. For additional information, please refer to http://g.co/dev/maps-no-account',
 'results': [],
 'status': 'REQUEST_DENIED'}

You need a devloper api key: http://g.co/dev/maps-no-account

This procedure is called geocoding, and you can use OSM for free without any API Developer key.

from geopy.geocoders import Nominatim


def geocode(addresses):
    locator = Nominatim(user_agent="My App Name")
    for address in addresses:
        result = locator.geocode(address)
        yield address, (result.latitude, result.longitude)



example_addresses = ["Kaufland Hohenlimburg", "Berlet Euronics Hagen", "Wieblingwerde", "SIEMENS Nürnberg", "Hoesch Hohenlimburg"]


for address, coords in geocode(example_addresses):
    print(address, coords)

Output:
Kaufland Hohenlimburg (51.36398765, 7.584049323649667)
Berlet Euronics Hagen (51.362861300000006, 7.558072424181042)
Wieblingwerde (51.3097517, 7.6151142)
SIEMENS Nürnberg (49.43815305, 11.075688213230517)
Hoesch Hohenlimburg (51.341030599999996, 7.573515683167329)

Here is the documentation: https://geopy.readthedocs.io/en/stable/#installation

read the docs

you need to replace line 12 with your file read routine.

explain exactly where the data originates and what the format is.

attach an example, or show how to get one.

but basically you want to create a loop that looks something like:

1. open file
   
2. read a record
   
3. for address, coords in geocode(record address)
   
4. print or save address and coords.
   

(Jun-08-2021, 04:32 AM)San Wrote: [ -> ]example i have an excel which contains address and the output needs to update in the same excel or separate file. is that possible?

Yes,you need a library that read excel i use always Pandas ,there are other like eg openpyxl
A example i use addresses from DeaD_EyE code into and put into a excel file(one on each row first column),then would read it like this.

from geopy.geocoders import Nominatim
import pandas as pd

def geocode(addresses):
    locator = Nominatim(user_agent="My App Name")
    for address in addresses:
        result = locator.geocode(address)
        yield address, (result.latitude, result.longitude)

file_adresses = pd.read_excel("adresses.xlsx", header=None)
example_addresses = file_adresses[0].values.tolist()

for address, coords in geocode(example_addresses):
    print(address, coords)

Output:
Kaufland Hohenlimburg (51.36398765, 7.584049323649667)
Berlet Euronics Hagen (51.362861300000006, 7.558072424181042)
Wieblingwerde (51.3097517, 7.6151142)
SIEMENS Nürnberg (49.43815305, 11.075688213230517)
Hoesch Hohenlimburg (51.341030599999996, 7.573515683167329

So it works,when shall write back to use df.to_excel()
If should write to excel in code over would collect data first to data structure eg list list, dict,then write with df.to_excel().
Of course you excel file may different,so you need test and maker changes the work for that.