National Address Book

[webfoo]

This post may sound a little creepy, but its not, I promise =)

When I ask my GPS for directions, the first thing I need to put in is the ZIP code. Based on the ZIP, it auto-populates the City and State fields. Then, I start typing in a street number. It does something similar to Google Suggest, and comes up with a list of addresses that match the one I started to type in.

What I'm looking for is this same thing, in a web page. Not for navigation purposes. Rather, so that my users require as few keystrokes as possible. In other words, they type in a ZIP code and the first few characters of the street address, and then all of the work is done for them.

I was thinking that this could be done utilizing the power of XML, with something like this. Am I off my rocker or is this feasible?

<xml>

<town>

<city>Rochester</city>
<state>NY</state>
<zip>14602</zip>

<streets>

<street>
<name>Plymouth Ave</name>
<addresses>
<address>100</address>
<address>101</address>
<address>102</address>
<address>103</address>
....
</addresses>
</street>

<street>
<name>Emerson St</name>
<addresses>
<address>100</address>
<address>101</address>
<address>102</address>
<address>103</address>
....
</addresses>
</street>

....
</streets>

</town>

</xml>

[httpwebwitch]

I certainly would not store the address data as XML. A fast indexed database is essential, something SQL-powered, responding to queries generated via AJAX requests to the server.

You could deliver the search results in XML format to the client.
An alternative for easy consumption by a JavaScript autosuggest widget would be JSON.

[webfoo]

So you would store the database like this?

TABLE address:

id | number | street ----------------| city -------| state | zip
01 | 60121 -| River Rd --------------| Anytown ----| RI ---| 12120
02 | 521 ---| Industrial Commerce Pl | Someville --| CO ---| 89071
03 | 9924 --| Elmhurst St ---------- | Exampleton -| OR ---| 62094

Once the database is established, it should be fairly simple to AJAX it in to place. But, the main question is, where will the data come from? Does such a database exist already? Who will maintain it?

Again, the idea is to reduce the number of keystrokes required to enter the data into a web form. The users of this system need to enter a large amount of data very quickly.

[g1smd]

I wonder what the size of the database would be? With residential and business addresses included, I assume the US alone would stretch to at least 150 million entries.

[httpwebwitch]

I would have one column in the database for each field that the user might start entering. So, if someone is going to start by typing in a ZIP, have an indexed column with just ZIPs in it.

Then when someone is typing in the street address, you'll do queries like "SELECT * FROM addresses WHERE ZIP='50443' AND address LIKE '123 mai%' LIMIT 10", returning the matching rows results quickly to the web interface.

If you get the data separated with columns for number, street, city... then your queries will have some "OR" clauses in them.

And mind the inevitability of differences between "st", "st.", "street"... and spelling variations like "St. Mary Blvd N" and "Saint Mary Boulevard North". The canonical address you return in the autocompletion response will depend entirely on how the data is formatted.

good indexing and optimizing your queries is crucial for a fast response. You do not want any queries that require a scan of the entire table.

If you're using a pre-built autocompletion gadget, look to see how it triggers those AJAX requests. You do NOT want an AJAX request on every keyup event. The gadget should at least use some kind of throttling, but the best ones use a method called "debouncing" -- read about it here:
[unscriptable.com...]

If the input field is a "mixed" text entry field, ie people would type "123 main street 50324 anytown KY" then you've got quite a challenge. Autocomplete may not be appropriate - or feasible - for that kind of input.

btw, I have no idea where you'd get the data. ZIP databases are easy to find, but I've never sought one with granular street addresses. Maybe the US government has something in the public domain.

[g1smd]

There's a Google open source project to do with address formatting.

Some of that code might prove useful: [code.google.com...]

[webfoo]

In fact, Google Maps does precisely what I am looking for! When you go to maps.google.com and start typing in the address field, it looks for known addresses that match what you are typing. Is there a way to query the Gmaps API to emulate this on my website?