How can i map addresses to (latitude, longitude) coordinates?

Unknown

I have a dataset I would like to visualize on a map. I have addresses for each of my datapoints, but I need a way to convert those addresses into latitude and longitude coordinates for displaying on the map. Does anyone have tips or an approach to lookup (latitude,longitude) coordinates from addresses? There are free web services I could use, but I thought I'd ask for help, rather than trying to reinvent the wheel. Is there a particular web service that works best with BOARD? Is there a data reader or process you could share or explain to help me out?

 

Thanks for your help,

Previous Member

Hi Bob,

Try this on Google maps.

It takes a bit of time to get the coordinates for each location but at least it works. Coordinates are displayed in decimal degrees.

 

Once you have the decimal degrees go to this link and input the decimal degrees and it will convert to minutes and seconds for you.

https://data.aad.gov.au/aadc/calc/decimal_dms.cfm 

 

The information below shows you how to get the coordinates on Google maps and also to enter coordinates to check you were right..

 

Find or enter latitude & longitude - Computer - Google Maps

Enter coordinates to find a place

1. On your computer, open Google Maps.
2. In the search box at the top, type your coordinates. Here are examples of formats that work:
   • Degrees, minutes, and seconds (DMS): 41°24'12.2"N 2°10'26.5"E
   • Degrees and decimal minutes (DMM): 41 24.2028, 2 10.4418
   • Decimal degrees (DD): 41.40338, 2.17403
3. You'll see a pin show up at your coordinates.

Get the coordinates of a place

1. On your computer, open Google Maps. If you're using Maps in Lite mode, you’ll see a lightning bolt at the bottom and you won't be able to get the coordinates of a place.
2. Right-click the place or area on the map.
3. Select What's here?
4. At the bottom, you’ll see a card with the coordinates.

Tips for formatting your coordinates

Here are some tips for formatting your coordinates so they work on Google Maps:

• Use the degree symbol instead of "d".
• Use periods as decimals, not commas. Incorrect: 41,40338, 2,17403. Correct: 41.40338, 2.17403. 
• List your latitude coordinates before longitude coordinates.
• Check that the first number in your latitude coordinate is between -90 and 90.
• Check that the first number in your longitude coordinate is between -180 and 180.

Hope this resolves your problem.

Cheers Billy

Unknown

Thanks Billy Ryan. That sounds like it will work well.

Federico Cazzalini

Hello Bob Gill,

have you already tried also the websites/services mentioned in the dedicated page of the Board Manual?

 

http://www.doogal.co.uk/BatchGeocoding.php

https://geoservices.tamu.edu/Services/Geocode/OtherGeocoders/

http://www.findlatitudeandlongitude.com/batch-geocode/#.VBqPhVaWJFw

 

I hope this also helps you.

 

Regards,

federico

Unknown

I've been able to find lat/long coordinates manually using these kind of free services. I'm still wondering if someone has an approach I could use in a datareader or process to automate the process. As new addresses appear in my dataset, I'd like the system to automatically lookup the appropriate lat/long coordinates. If you were able to automate this piece, what approach did you use?

 

Thanks for your insight and assistance.

Previous Member

Hi,

You could use this python script to get the longitude and latitude for each address in your database. You need to apply for a free google map developer ID.

 

best regards,

 

RJ

 

 

 

import pypyodbc as db

from geopy.geocoders import Nominatim

geolocator = Nominatim()

#gmaps = GoogleMaps("<Your Google Map developer ID>")

connection = db.connect('Driver={SQL Server};'

                              'server=H2674598\SQLEXPRESS;'

                              'Database=MSTR;'

                              'uid=board;'

                              'pwd=Qazmko012')

cursor = connection.cursor()

SQLCommand = ("Select CUST_CITY_ID, CUST_CITY_NAME, CUST_STATE_NAME From dbo.LU_CUST_CITY_LONG_LAT")

cursor.execute(SQLCommand)

for row in cursor.fetchall():

    address = str(row[1]) + ", " + str(row[2])

    location = geolocator.geocode(address)

    print(str(row[1]) + ", " + str(row[2]) + ", " + str(location.longitude) + ", " + str(location.latitude))

    SQLUpdate = ("update LU_CUST_CITY_LONG_LAT set long = " + str(location.longitude) + " ,lat = " + str(location.latitude) + "where CUST_CITY_ID = " + str(row[0]))

    cursor.execute(SQLUpdate)

    cursor.commit()

print("Finished")

Previous Member

Hi there,

 

as a BOARD customer we're facing the same issue. Our ERP system provies addresses of our customers but no lat/long information. Currently we dread the effort for an individual implementation like mentioned in this discussion. Maybe it would be an interesting idea to add this as a standard feature to BOARD.

 

Best regards,

Marco

Previous Member

I have not attempted to solve this problem in BOARD, but we did tackle this about a year ago.  I used a free web service - https://geocod.io/ - and use an automation program (TaskCentre, but there are others on the market) to put the latitude and longitude into our ERP database.  That way we have the data at the source;  I found the geocod.io service very easy to use, and learning and implementing it far faster than one-by-one coding through Google Maps. 

 

(Geocod.io does impose a fee for "large" numbers of transactions - for us, that was only relevant when we were collecting against existing data, because our database expands with new records far below their fee threshold.) 

Unknown

I very much appreciate all the ideas everyone has shared. I'm most comfortable with Windows PowerShell as a scripting language, so I decided to use that to build the automation piece. I trust Google Maps as a good resource for mapping information and signed up for their free API. With the combination of the Google Maps Geocoding API and PowerShell, I wrote a script to return a (latitude, longitude) coordinate from an address, shown below. Feel free to reuse this, if it can help you out. You'll need to register your own APIKEY. This one only works from my server.

 

#############################################

# Get (Lat,Long) From Address
# Bob Gill, bob.gill@clearinsight.ca, 1/25/2018
# This script uses a Google Maps Geocoding API to lookup the latitude and longitude for a particular address.
# The free account is limited to 2000 queries per day. If usage is higher, a paid account can be purchased.

# Here is the link to get your own free Google Maps Geocoding API account
# https://developers.google.com/maps/documentation/geocoding/intro

$Address = "1600+Amphitheatre+Parkway,+Mountain+View,+CA"
$APIKEY = "AIzaSyC01XNF-th47XJ5hM-QcD2FIPqd1coFNak"

$APIRequestString = "https://maps.googleapis.com/maps/api/geocode/json?address=" + $Address + "&key=" + $APIKEY

 

# Check the API Request String is correctly formatted
# $APIRequestString.ToString()

# Call the web service and store the result
$R = Invoke-WebRequest $APIRequestString

 

# Parse out the (lat, long) coordinates
$Lat = $R | ConvertFrom-Json | select -expand results | select -expand geometry | select -expand location | select lat | out-string
$Long = $R | ConvertFrom-Json | select -expand results | select -expand geometry | select -expand location | select long | out-string

 

# Display them to the screen for validation
$Lat.ToString()
$Long.ToString()

#############################################

 

 

Thank you to Billy Ryan, Federico Cazzalini, Robert-Jan van Kuppeveld, Marco Philipeit and Luke Brown for your contributions.

Stefan Rappen

Hi together,

 

two of our customers provide Geo Information in ETRS89 UTM format. The conversion into longitude and latitude is not very easy.. Does somebody have a tsql-script or a set of mathematic formulas to do that?

 

Thanks and Regards

 

Stefan

Previous Member

Hi All,

 

I tried a different approach which may work.  I downloaded from a public website a list of all post codes and the geo-coordinates.  I think loaded this to SQL Server table (but access or similar would also be fine.  In BOARD I have a the post code as an entity.  I then created a data reader to my SQL database and loaded the Coords to my Long and Lat cube.  You need to be careful of the formating of the post code but you allow to load and ignore errors as these will be postcodes that don't exist in your source.  I had several thousand to load so this worked well.  I did get some that did not match and then used a google search for the missing ones.

 

I was doing this for a proof of concept but may work for others.  I also did the same for a US Zip code list.

Previous Member

There is to many website on internet but latlongfinder.net is great website through you can easily find latitude longitude of any area or vice versa you can also get address through latitude and longitude.

Paul Wyatt

I've read all the posts on this thread and wanted to ask if anyone had yet found a way to take a selection of properties, with Lat/Long coords and plot them into Google maps.   Currently, I can create a dynamic URL for a selected property and 'breakout' of board into a new WEB tab to utilise Google Maps.  What I want to do is plot a selection of properties from BOARD into Google Maps.

Previous Member

Hello All,

 

Here is a simpler way i use to get Co-ordinates Automatically just by Address-Postal Code from your Data's Address Column. Plus its a Google Add-on

 

Paul Wyatt - Avison Young UK Bob Gill christopher field Stefan Rappen Federico Cazzalini

 

Please Note : you might have to use Google sheets for this one.

 

Give this a Try :

How to quickly add latitude and longitude coordinates to a spreadsheet using Google Sheets - YouTube