Product Search by Zip Code | Haversin Algorithm | Representation

Question

I have an application that searches for items based on a zip code.

When searching for a postal code, I return all products from this City / Neighborhood (performed by parsing postal / postal codes).

Now I need to sort these products based on the distance from the source zip / postal code.

I have a Lat / Long stored in the database, and plan to use the Haversin formula to calculate the approximate distance from the original query.

My question is where should this be calculated. Do I have to do this in a stored procedure before returning my dataset?

Or should I return my dataset using my Lat / Long and compute it on the server side before returning to the user.

You may need to calculate up to 1000 results.

Answer 1

Typically, database servers are tied to IOs, not tied to CPUs. YMMV, but if your case is typical, it would be advisable to perform a Haversine calculation on the database server.

I would recommend using a custom lookup table for your arcsine calculations, since you can probably provide approximate distances on a logarithmic scale, for example:

• 100m
• 300
• 1km
• 3km
• 10km
• 30km

• > 30 km

  and then use linear interpolation as a refinement.

For typical distances found in one metropolitan area, you can use only 2 or 3 members of the Taylor extension for sin and sose and not more accurate calculations:

• sin (x) = ~ x - x ^ 3/6 + x ^ 5/120
• cos (x) = ~ 1 - x ^ 2/2 + x ^ 4/24

Recall also that for a converging Taylor series, the error after the nth term is strictly less than the value of the (n + 1) ’term. This allows you to effectively stop the calculation as soon as the desired accuracy is achieved, which in general for the Haversin formula is only 0.5% due to the fact that the Earth is not a homogeneous sphere.

Answer 2

Are you using SQL Server 2008 or higher? If so, I recommend using the built-in geography data type, rather than directly calculating Haversine. You can have a zip code table with a zip code (e.g. 90210) and either the center point of the zip code or the entire area covered by the zip code in another column (or both if it makes sense for your application). Then you can use the STDistance() function to calculate the distance. In addition, with spatial indexing, you can get a list estimated by distance without much effort.