Tag Archives: Geocoding

Excel Geocoder

17 Apr

ESRI makes maps for Office. I thought this could be interesting and went to work on trying to insert a map in to a spreadsheet. I did not have much luck. Instead, I decided to throw together a quick macro that would geocode addresses in a spreadsheet and give back coordinates. I do not think VB Script can parse JSON, so the result is ugly, but you get the idea.

Start with a spreadsheet of addresses

sheet

 

Then create a Macro – I copied the code for reading the URL from Ryan Farley.

Sub Macro1()
Range(“A2”).Select
i = 2

Do Until IsEmpty(ActiveCell)

URL = “http://coagisweb.cabq.gov/arcgis/rest/services/locators/CABQ_NetCurr/GeocodeServer/findAddressCandidates?f=json&outSR=4326&street=” & ActiveCell.Value
Dim objHttp
Set objHttp = CreateObject(“Msxml2.ServerXMLHTTP”)
objHttp.Open “GET”, URL, False
objHttp.Send
Cells(i, 2).Value = objHttp.ResponseText
i = i + 1
ActiveCell.Offset(1, 0).Select
Set objHttp = Nothing
Loop

End Sub

The code starts at cell A2 and reads addresses until it reaches an empty cell. It takes the value and sends it to the ESRI REST endpoint for the City of Albuquerque Geocoding Service. It sets the cell next to it with the results. They should really be parsed, but I am too lazy and was just curious if it could be done. It can. The result is below.

results

I am still thinking of how to embed a web page in the sheet.

Reverse Geocoding a Line

3 Mar

In Geocoding with ABQ Open Data, I showed you how to geocode an address and how to reverse geocode a point when the user clicks on the map. Geocoding is an operation performed on points or addresses, so what do we do with lines? In this post I will show you how I am geocoding a line.

The Need

I have an application that allows a user to select a location for where they would like to perform some construction work and need a permit. The application started by allowing the user to specify a point or address. As I met with the users of the application, it turned out that having line features was crucial. Work may be performed along a stretch of road and the approvers of the permit wanted to be able to find at least the starting address when looking at the data in a tabular format – mostly for things like how many permits are on Central Ave.

The Solution

Alert box showing the start and end addresses of a line

Alert box showing the start and end addresses of a line

The solution uses the same reverse geocoding process as my previous post, but parses a line feature in to points. I am using the leaflet.draw plugin to allow the user to draw the line on a map. The draw plugin has a draw:created event that searches to see the type of object drawn. My code runs in the if (type == ‘polyline’) block.  The code below converts the layer drawn to GeoJSON and then the GeoJSON coordinates to a string split on the comma. Using the string at index 0 and 1, I have the starting point of the line. Using the index of length-1 and length-2 I can grab the last point. I use length because the user can draw a line segment with an unlimited number of points so I will not know where the line ends and do not want to assume a two point line. when I have the points, I call reverseGeocode().

var tojson=layer.toGeoJSON();
var x=String(tojson.geometry.coordinates).split(“,”);

//start point
var lt=parseFloat(x[1]);
var ln=parseFloat(x[0]);
reverseGeocodeLineFrom(ln,lt);

//endpoint
var ltend=parseFloat(x[x.length-1]);
var lnend=parseFloat(x[x.length-2]);
reverseGeocodeLineTo(lnend,ltend);

The reverseGeocode function just makes an AJAX call to the ESRI REST API service for my geocoder passing the point and parsing the response.

function reverseGeocodeLineFrom(a,b){

var urlgeocode=”http://servername/ArcGIS/rest/services/wgs84locator/GeocodeServer/reverseGeocode”;

var geocodeparams = “location=”+a+”,”+b+”&distance=1000&f=json”;
var http;
http=new XMLHttpRequest();
http.open(“POST”, urlgeocode, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {

addressAsJSON=JSON.parse(http.responseText);
alert(addressAsJSON.address.Street);
}
}
http.send(geocodeparams);
}

Now I have a street address for the start and end of my line.

Geocoding with Albuquerque Open Data

16 Jan

Albuquerque provides a large assortment of open data on their website. One service that I would find useful is a geocoding service. Digging around, I was able to find one that was not listed on the open data page. In my previous post, I showed you how to fake a geocoding service using the address points data set, but it had a serious flaw – it required an exact match. In this post, I will show you the correct way to geocode using the proper service.

Geocoding an Address

The first step is to create a widget that will allow a user to enter the address we want to find. The CSS and HTML below will create the widget for us.

#AddressSearchBox{
background-color: rgba(255, 255, 255, 0.5);
border: 1px solid #000;
-moz-border-radius: 15px;
border-radius: 15px;
height:40px;
width:350px;
padding-top:10px;
padding-left:5px;
position: absolute;
left: 40%;
top: 3px;
z-index: 100000;
}

<div style=”text-align: center”><div style=”text-align: left; margin: 0 auto; width: 50%;”>
<div id=”AddressSearchBox”> <center><b>Address:</b><input type=’text’ id=’addr’ name=’to’><button onclick=’GeocodeAddress()’>Search</button><center></div>
</div></div>

Now that we can accept user input, we need to write the GeocodeAddress function. The function is an AJAX call to the geocoding service. It will return a series of candidates with scores, however, for this example I will just grab the first result.  The code below sends an address, asks for the results in wkid:4326 and as JSON. We grab the first result, add a marker to the map and zoom.

function GeocodeAddress(){
var params = “Street=”+document.getElementById(“addr”).value+”&f=json&outSR=4326″;
var url = “http://coagisweb.cabq.gov/arcgis/rest/services/locators/CABQ_Composite/GeocodeServer/findAddressCandidates&#8221;
http=new XMLHttpRequest();
http.open(“POST”, url, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {
var thexy= JSON.parse(http.responseText);
x=thexy.candidates[0].location.x;
y=thexy.candidates[0].location.y;
console.log(x+”,”+y);
var yousearchedfor = L.marker([thexy.candidates[0].location.y,thexy.candidates[0].location.x]).addTo(map).bindPopup(‘<h3>’+thexy.candidates[0].address+'</h3>’).openPopup();
map.setView([thexy.candidates[0].location.y,thexy.candidates[0].location.x],18);
}}
http.send(params);}

That is all there is to it. The image below shows the results for 123 Sentral. Notice the street name is actually central. Because we are using a real geocoding service in this example we do not need exact matches.

Searching for Sentral

Now you can enter an address and find the result on a map. But with ESRI gecoding services you can also reverse geocode.

Reverse Geocoding

Reverse geocoding takes a point and returns the address. We will allow the user to click on the map and will return a marker with the address of the click. Our map will return coordinates in wkid:4326 (lat,long) but the service we are using is using wkid:3857. Now we have a problem – the points we will pass do not match the expected input to the service and we will receive no results back. I addressed projections in a previous post and  how we could pass an inSR and an outSR to the service. If you look again at the code above, we asked the geocoding service to outSR=4326, so can’t we just do that again in this example? No. The reverse geocoding service will not take inSR as a parameter. Here is the solution:

when an ESRI service does not allow an inSR parameter – for example adding a feature or reverse geocoding – you can assign the inSR value in the geometry you are sending.

What does this mean? It means that when the user clicks we need to send a point object to the service that looks like:

 location = {x:-106 , y:35, “spatialReference” : {“wkid” : 4326}}

Now we can code the reverse geocoding portion of our application. using Leaflet.js we can write:

map.on(“click”,function(e){ CODE HERE});

Inside the function, we need to make an AJAX call to the service passing the geometry as a parameter – with our spatial reference – and then get the results, draw a marker, and zoom. The code is shown below.

map.on(“click”,function(e){
var marker=L.marker(e.latlng);
coords=marker.toGeoJSON();
var urlgeocode=”http://coagisweb.cabq.gov/arcgis/rest/services/locators/CABQ_Composite/GeocodeServer/reverseGeocode&#8221;;
a1=”location={x:”;
a2=String(coords.geometry.coordinates[0]);
a3=”,y:”;
a4=String(coords.geometry.coordinates[1]);
a5=’,”spatialReference” : {“wkid” : 4326}}&distance=1000&f=json&outSR=4326′;
var geocodeparams =a1.concat(a2,a3,a4,a5);
var http=new XMLHttpRequest();
http.open(“POST”, urlgeocode, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {
results=JSON.parse(http.responseText);
L.marker([results.location.y,results.location.x]).addTo(map)
.bindPopup(‘<h3>’+results.address.Street+'</h3>’)
.openPopup();
map.setView([results.location.y,results.location.x],18);
}}
http.send(geocodeparams);
});

Now when a user clicks on the map, they will get the image below.

Reverse Geocoding

Reverse Geocoding

Fake Geocoding with CABQ Open Data

15 Jan

Geocoding addresses is hard. For starters, there tend to be very few free services. The services that are free also tend to limit the number of queries you can make in a day or at a time. So what is a community developer to do? In this article, I will show you how to use Albuquerque Open Data to create a fake geocoding service.

Open Data

Browsing the Albuquerque Open Data site, I noticed they have published address points. Clicking it, we can choose a REST service for this data. If you look at the available fields in the data you will see one called GeoAddress. I used the ESRI form to grab some sample data to see what was in the field. From the looks of it, it was a concatenation of streetnumber, streetname, streetdesignation and streetquadrant. We will use this to build our geocoder.

The Map and Widget

Before I start with the details, here is an image of the final application with the address for Chipotle entered.

The Final Geocoding Application.

The Final Geocoding Application.

 

To create the above application, we need to style the widget that allows the user to type an address and press enter or click the submit button. Below is the CSS required and then the HTML for the widget.

#AddressSearchBox{
background-color: rgba(255, 255, 255, 0.5);
border: 1px solid #000;
-moz-border-radius: 15px;
border-radius: 15px;
height:40px;
width:350px;
padding-top:10px;
padding-left:5px;
position: absolute;
left: 40%;
top: 3px;
z-index: 100000;
}

<div style=”text-align: center”>
<div style=”text-align: left; margin: 0 auto; width: 50%;”>
<div id=”AddressSearchBox”> <center><b>Address:</b><input type=’text’ id=’addr’ name=’to’ onkeypress=’handleKeyPress(event)‘><button onclick=’GeocodeAddress()‘>Search</button><center></div>
</div>
</div>

The HTML contains functions for when the user presses enter and if they click the button. Those functions will be created in our Leaflet.js map.

The handleKeyPress function will just check if the user pressed the enter key and then call the GeocodeAddress function. We put this in because many users hit enter instead of clicking the UI buttons.

function handleKeyPress(e){
var key=e.keyCode || e.which;
if (key==13){
GeocodeAddress();
}}

The application functionality all happens in the GeocodeAddress function. Because this is not a real geocoding service, we are using a MapServer service to query a field of addresses and then grabbing the result. Our query will look for the feature which has a GeoAddress equal to what the user submitted. The user entered address must be in all CAPS. For this to work, the match must be exact. As I mentioned, this is not a real geocoding service, but a way to fake one. The code below queries the service, gets the result, adds a marker to the map and zooms in.

function GeocodeAddress(){

var params = “where=GeoAddress='”+document.getElementById(“addr”).value+”‘&f=json&outSR=4326&outFields=*”;
var url = “http://coagisweb.cabq.gov/arcgis/rest/services/public/cadastral/MapServer/6/query&#8221;
http=new XMLHttpRequest();
http.open(“POST”, url, true);
http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”);
http.onreadystatechange = function() {//Call a function when the state changes.
if(http.readyState == 4 && http.status == 200) {
alert(http.responseText);
var result= JSON.parse(http.responseText);
L.marker([result.features[0].geometry.y,result.features[0].geometry.x]).addTo(map);
map.setView([result.features[0].geometry.y,result.features[0].geometry.x],18);

}}
http.send(params);
}

It is by no means perfect, but it is a quick and dirty way to find an address if a real geocoding service has not been provided. In my next post, I will show you how to geocode address when a service has been provided.

Geocode to Rooms in a Building

24 Oct

Continuing with the theme of GIS inside the building, in this post I will show how to geocode students to classrooms inside of a building.

First, import a CAD file to use as a base.  Once the file is in and located properly, draw the classrooms in GIS as a polygon shapefile.

The classrooms need a field that can be used as an address. Create a new field and insert the room number of each room. If you want to code more than one building at a time, then room number would not work — there cannot be duplicate addresses. In that case you would need to create unique addresses for each room — the school acronym followed by room number may be a good start.

For this example I have created a student list. Each student is named ‘Student’ plus a number and is assigned to a classroom. The list is shown below.

Create an address locator in GIS using a single field. Pick your reference data and the key field and click OK. Geocode the address list using the newly created locator. Add points to the map. Your file will look like the one below.

There is only one point in each room. If you use the INFO tool and click on a point you will see a list of all the students at this location.  To create a better visualization of our data we will count the number of points in each room.  In the point shapefile create a field called COUNT and set the value equal to 1.  Join the room shapefile with the point file based on location. The output will be a single colored room file that looks like this:

Looking at the properties of the file you will see the COUNT field summed for the number of students in each room.

The last steps are to change the SYMBOLOGY of the shapefile based on quantity, using COUNT as the field. Add a label and you will now have a color coded shapefile showing the number of students in each room.