4 ms·

How does one maintain clean data in their database if they do this. Countless people will type things in their own way. Miami,FL Miami, FL miami,fl miami fl et

How does one maintain clean data in their database if they do this. Countless people will type things in their own way. Miami,FL Miami, FL miami,fl miami fl etc. would a city/state autocomplete be the best here, but what about other sections of the required data?

staktrace 15y ago
You can preprocess and normalize the data before you put it in the database. City/state is probably one of the easiest fields to do this with.
- stinky613 15y ago
  I like the idea this article proposes, but I believe the author and our commenters glance over some backend difficulties. Yes, there are exactly 50 states each with precisely one two-letter abbreviation. How would you handle, say, "Boston, masachusetts" [sic]?
  - spc476 15y ago
    I mentioned a method above (http://news.ycombinator.com/item?id=2825015 http://news.ycombinator.com/item?id=2825015) and since entering it, I've thought a bit more about the issue. Most instances will have only two words. If it's three or more and the last word isn't two characters, then if the second to last is one of "north", "south", "west", "new", "washington" or "puerto", then the last two words form the state and the rest is the city. And you can ignore any punctuation.
- stinky613 15y ago
  I like the idea this article proposes, but I believe the author and our commenters glance over some backend difficulties. Yes, there are exactly 50 states each with precisely one two-letter abbreviation. How would you handle, say, "Boston, masachusetts" [sic]?
rmc 15y ago
You should clean all data you get from the user. You are already doing it, unless Bobby Tables is messing with your database. You should just add in another layer.
mtoddh 15y ago
Services like Google's geocoding API (http://code.google.com/apis/maps/documentation/geocoding/ http://code.google.com/apis/maps/documentation/geocoding/) can help with this; it's pretty good about figuring out the address you mean and returning it in a normalized format: >>> from geopy import geocoders >>> g = geocoders.Google() >>> place, (lat, lng) = g.geocode("10900 Euclid Ave in Cleveland") >>> print place 10900 Euclid Ave, Case Western Reserve University, Cleveland, OH 44106, USA
- ma2rten 15y ago
  The only problem, is that the TOS do not allow you to use it if you do not show the information on a map and there is a usage limit (it's per IP, so you could do it client side).
auston 15y ago
To summarize everyone: - Use 3rd party services to scrub data before storing data (phone numbers & addresses are particularly easy to scrub) - Try to normalize your data yourself (turn everything into lower case when storing in the db, format on the way out to the html page. i.e. Try to convert the date to an object [in python] OR make sure phone number follows the right format with regex) -- To add my own bit: - Add visual cues to the form (like ajax validation, etc)

staktrace 15y ago

You can preprocess and normalize the data before you put it in the database. City/state is probably one of the easiest fields to do this with.

stinky613 15y ago
I like the idea this article proposes, but I believe the author and our commenters glance over some backend difficulties. Yes, there are exactly 50 states each with precisely one two-letter abbreviation. How would you handle, say, "Boston, masachusetts" [sic]?
- spc476 15y ago
  I mentioned a method above (http://news.ycombinator.com/item?id=2825015 http://news.ycombinator.com/item?id=2825015) and since entering it, I've thought a bit more about the issue. Most instances will have only two words. If it's three or more and the last word isn't two characters, then if the second to last is one of "north", "south", "west", "new", "washington" or "puerto", then the last two words form the state and the rest is the city. And you can ignore any punctuation.
stinky613 15y ago
I like the idea this article proposes, but I believe the author and our commenters glance over some backend difficulties. Yes, there are exactly 50 states each with precisely one two-letter abbreviation. How would you handle, say, "Boston, masachusetts" [sic]?

rmc 15y ago

You should clean all data you get from the user. You are already doing it, unless Bobby Tables is messing with your database. You should just add in another layer.

mtoddh 15y ago

Services like Google's geocoding API (http://code.google.com/apis/maps/documentation/geocoding/ http://code.google.com/apis/maps/documentation/geocoding/) can help with this; it's pretty good about figuring out the address you mean and returning it in a normalized format: >>> from geopy import geocoders >>> g = geocoders.Google() >>> place, (lat, lng) = g.geocode("10900 Euclid Ave in Cleveland") >>> print place 10900 Euclid Ave, Case Western Reserve University, Cleveland, OH 44106, USA

ma2rten 15y ago
The only problem, is that the TOS do not allow you to use it if you do not show the information on a map and there is a usage limit (it's per IP, so you could do it client side).

auston 15y ago

To summarize everyone: - Use 3rd party services to scrub data before storing data (phone numbers & addresses are particularly easy to scrub) - Try to normalize your data yourself (turn everything into lower case when storing in the db, format on the way out to the html page. i.e. Try to convert the date to an object [in python] OR make sure phone number follows the right format with regex) -- To add my own bit: - Add visual cues to the form (like ajax validation, etc)