I found myself with about a 1000 locations in the cmn_location table of which only about a 100 had lat long. I tried the get_lat_long business rule but it didn't work (I think it's based on a dead Google service.) After poking around a bit I found a way to update them using a background script which you can also use with a bit of modification to fix the get_lat_long business rule. The main issue is that you MUST have a MID Server and set up a REST client on it. If you try and run any of the public APIs for geolocation you will find that the usage limits have been totally exceeded since your running on a Service Now instance that's shared with lots of other people. The MID server however is running on your local network and that makes all the difference. So how does this work?
1. Set up a Rest service using the Rest Message functionality of Service Now with an endpoint ofhttps://maps.googleapis.com/maps/api/geocode/json. It should look like this:
2. Run this background script, adjusting the J variable to do as many records as you want at a time (I did them in batches of 100 to not tie up the server for too long as each lookup takes 3-15 seconds). Anytime it fails it will mark the lat_long_error field and skip the record the following pass:
var loc = new GlideRecord('cmn_location');
// Issue the query to the database to get all records
loc.query();
var j=0;
while (loc.next()) {
if(loc.lat_long_error != "true" && (loc.latitude=="" || loc.longitude=="")) {
if(updateLatLong(loc)) {
gs.log("Updated: "+loc.name);
} else {
gs.log("Failed to update: "+loc.name);
}
//only process 100 records
if (j<100)
j++;
else
break;
}
}
function updateLatLong(loc) {
if(loc.city != "") {
var address = new String(loc.city);
} else {
var address = new String(loc.name);
}
if(loc.country != "") {
address+= ("," + loc.country);
}
gs.log("requesting address:"+address);
//get the Rest Message service
var r = new RESTMessage('Google GeoLocate', 'get');
r.setStringParameter('address',address);
var response = r.execute();
// put in a a wait or it will return undefined
var k = 1;
while ( response == null ) {
response = r.getResponse( 1000 );
k++;
if ( k > 30 ) {
gs.log( 'service time-out' );
break;
}
}
gs.log( "response took ... " + k + " seconds");
//parse response
var parser = new JSONParser();
var parsed = parser.parse(response.getBody());
if(parsed != null && typeof(parsed) != undefined) {
loc.latitude = parsed.results[0].geometry.location.lat;
loc.longitude = parsed.results[0].geometry.location.lng;
var g_address = parsed.results[0].address_components;
for(var m=0;m<g_address.length;m++) {
for(var n=0;n<g_address[m].types.length;n++) {
if (g_address[m].types[n] == "country") {
loc.country = g_address[m].short_name;
}
}
}
} else {
gs.log("Problem with returned JSON");
}
//update record on change
if (loc.latitude.changes() || loc.longitude.changes()) {
loc.update();
return true;
} else {
loc.lat_long_error = "true";
loc.update();
return false;
}
}
