Warning: Most of this article deals with Floats, as in Floating Point numbers, not beer floats or parade floats. If you just want to see the status of the Beer App, skip down to the last paragraph.
I have been working this weekend on the user content section of the Beer App. I just finished setting up the page that will let users add new beer locations to the database. I decided to work on this page first for purely selfish reasons. I needed to add more locations for testing purposes and was getting tired of manually geocoding and inserting them into my database.
I whipped up a quick page that had all the necessary input fields; Name, Address, Phone Number, Website, Location type (bar/restaurant, bottle shop, brewpub, etc). The code behind page was pretty simple. First, I take the address and run it through the geocoder built into the Google Maps API. Once I have a latitude and longitude, I reload the page, passing it these values. When the page reloads, if a latitude and longitude were passed in, I check the coordinates against entries in the database to make sure the location is not already on file. I use the location’s coordinates instead of the name or address for the comparison since users may spells names or format addresses differently. If the location is on file, a message returns that the location already exists. If not, the location is added to the database and a message is displayed stating so. Before this feature goes live, when a location exists, the user will be shown the location’s details and given the option to make changes if the information on file is not correct.
Now it was time to test my new routines. I tried adding Beer Nuts, a bottle shop here in Eugene, to the database. The page reloaded, telling me that the location had been added. I opened up phpMyAdmin and browsed the location table. Sure enough, Beer Nuts was sitting at the bottom of the table. I went back to the page and hit the reload button. With any luck, I should receive a message that Beer Nuts already exists.
Shit, I got the “Location has been added” message again. A quick check of the database confirmed that I did in fact have two identical entries for Beer Nuts occupying the last two rows of my table. How the hell did that happen? I put in some debugging code and fired the page again. Damn, a third entry with identical information. I went back to the browser and looked at the URL. Ah, now there’s my problem. The geocoder is returning latitude and longitude to seven decimal places and I am only storing six decimal places in my database. I went back to my database to change the latitude and longitude fields. This is where I ran into my next problem.
On the recommendation of Google’s API tutorial, I set the fields up as Floating Point Numbers. Why is this an issue? You can read the Wiki article for a full explanation, but the simple answer is that a Floating Point number is an approximate representation of a number. This approximation is usually not an issue, but it does throw a wrench in directly comparing two Floating Point numbers.
My first thought was to change the database fields from Floating Point to Decimal. This solution creates a couple problems of it’s own. First, on versions of MySQL prior to 5.0, Floating Point math is still used to compute Decimal values. Second, if the hardware running my site ever changes, I run the risk that the converted geocoder values will no longer match the Decimal values stored in the database. This is due to Floating Point math varying based on the CPU performing the operations. Finally, unless you are using a 386 or older computer, your CPU has a separate section for Floating Point arithmetic. This makes Floating Point operations much faster.
So, it looked like I was stuck with Floating Point numbers. It was time to accept it and make the best of the situation. Knowing that the geocoder output was never going to match the database values, I needed to set up a small range. I added code to round the latitude and longitude values from the geocoder to six decimal places. Next, I created high and low latitude and longitude variable that are +/- .000005 of the rounded latitude and longitude values. This created a range of .00001 for both the latitude and longitude, or 1.11 meters. This should be a precise enough to not return false positives. I changed my select statement to look for locations where the latitude and longitude are between the high and low latitudes and longitudes.
I went back to my browser and tried adding Beer Nuts again. Success! This time I received the page telling me that the location already existed. I tried adding a few more locations that were already in the database. Each time, the query correctly detected the location in the database and did not insert an addition entry.
Now that my page was working correctly, I was finally able to add some new locations for testing. Currently, the following locations in the database:
Sam Bond’s Garage – Eugene, OR
Oak Street Speakeasy – Eugene, OR
Eugene City Brewery – Eugene, OR
High Street Brewery & Cafe – Eugene, OR
East 19th Street Cafe – Eugene, OR
Hop Valley Brewing Company – Springfield, OR
Bailey’s Taproom – Portland, OR
Belmont Station – Portland, OR
Concordia Ale House – Portland, OR
Saraveza – Portland, OR
Green Dragon – Portland, OR
Buckeye Beer Engine – Lakewood, OH
The Drafting Room – Spring House, PA
The Drafting Room – Exton, PA
Yes, if you skipped the top part, this is where you pick it back up.
With the Float issue out of the way, I can now get back to work on finishing the other user content submission pages. I have also made some changes to the layout of the Beer App. After receiving a few e-mails from people having issues with the search, I removed the Basic option. I also temporarily removed the Sub-Style option. Once I finish the JQuery routine, Sub-Style will automatically be populated with valid options when a Style is chosen. The database is also up to 1500+ beers from 140+ breweries. Hopefully I will be ready within the next couple weeks to hand out some beta accounts. Until then, I will keep posting progress updates.
Cheers!
Kevin