Killersite Blog

To follow up on my earlier post about programming the ability for my webapp to do calculations on zipcodes. My usecase is that I want to be able to show users businesses in their own and nearby zipcodes.

My solution so far is as follows:

• From the users IP Address get his lat/lon location from one of the many IP Location lookup services.
• Do a query for the closest zipcode based on the users lat & lon location.
• Do a radius search on our Lat, Lon, zipcode DB based on the users closest zipcode for the nearby zipcodes
• Do a DB search for all businesses that are located in those zipcodes

Here is the Hibernate query for the radius search. Pass in the Zip and radius distance…


<sql-query name="ZIPS_BY_DISTANCE_SQL">
<return class="com.salecity.persistent.Zip" alias="ZIP"/>
<![CDATA[
SELECT {ZIP.*}, (((acos(sin((:lat*:pi/180)) * sin((LATITUDE*:pi/180)) + cos((:lat*:pi/180)) * cos((LATITUDE*:pi/180)) * cos(((:lon - LONGITUDE)*:pi/180))))*180/:pi)*60*1.423) as distance
FROM {ZIP}
HAVING distance < = :distance
ORDER BY distance ASC;
]]>
</sql-query>


The one tricky thing with Java (and MySql) is that the built-in value for PI is not as large as I would hope, so it seems that the search is not as precise as it could be.