Calculate distance between zip codes sql

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Calculate distance between Zip Codes AND users. Ask Question. Asked 11 years, 2 months ago. Active 4 years, 3 months ago. Viewed 47k times. Add a comment. Active Oldest Votes. Here's what an Quadtree looks like: To search over it, you drill down through each larger cell using the index of smaller cells that are within it.

Paul, I had no idea I would get so many and varied responses to this question. I found yours to be particularly informative.

Thank you for taking the time out of your day to provide such a thorough explanation. Glad you liked it. Definitely did take longer than I originally intended when I started answering the question, but I guess that's how SO works!

I downloaded a zipcode datafile from the web which contained the longitudes and latitudes of all the official US zipcodes in csv format: "","Adjuntas","Adjuntas","Puerto Rico","PR","","Atlantic", ZipArea, toZip. ZipArea, dist ; sw. For example if you have a user whose zipcode is and they want to find people who are within a 10 mile radius of them then you can now simply do the following: select p.

Hope this helps EDIT: extended radius to miles which increased the number of zipcode distances to Jon Black Jon Black This is a good solution, but lives with an assumption of a given distance. Of course the solution I liked above grows with distance at query time, and this one doesn't. However, how many rows do you wind up if you use say, miles as your outer limit? I'm also not sure what the practical limit of rows in SQL server is, but I suspect I'd be pushing my luck on keeping excellent performance with that many rows.

The runtime is 0. I'm since learned much about the limits of performance on big SQL tables. They're not nearly as slow as I thought they might be.

Thanks, guys, for going to all that extra effort. Your answers and comments were super helpful. Show 2 more comments. Facundo Colombier Facundo Colombier 3, 1 1 gold badge 32 32 silver badges 38 38 bronze badges. Jander Jander 4, 1 1 gold badge 21 21 silver badges 19 19 bronze badges.

This seems like a REALLY fast way of getting some indexing done, but with a much smaller and therefore more useable indexed data set. This might turn out faster than the solution I posted below. I say might because I have not thought it through. I suspect variation of this can be used to get ZipCodes that are known to be within range, and allow me to do a boxed select by Lat and Long, and then user the Haversinse Formula to calculate a much smaller number of distances.

Zipcodes aren't approximately equal in size though. I think there are better solutions for doing this spatial breakdown. Good visualization here: benfry.

Paul: Hm! Actually, in my head I was treating ZIP codes as points, but this will work with shaped zipcodes too. The key here is that we store the records in our database with this information together. Following more historical methods for doing distance calculations, we would be forced to create a complex query that uses a number of trigonometric functions to get our distance amount. If we wanted to query and find records where two geography points are less than or equal to 25 miles apart we can use the following simple where clause.

This assumes that "StreetGeo" is the column in the database that we are checking and MyLocation is the geography representation of the location we are searching from! The only piece of magic here is that we have to divide the result by Overall I hope that this has been helpful, it is sure a lot easier than doing the more involved distance queries. The fastest way to do your calculations in real-time is to precalculate and store the expensive trig values in columns in your table, e.

Do your cos radians Thus reducing all calculations to constant values before getting to SQL and calculated columns will make your query look like this:. You could probably increase performance slightly by adding a bounding-box reduction of zips in a subquery before doing the trig, but that may be more complicated than you would like. Store All Calculated Distances Another thing you could do is precalculate all distances of all zips, and store then in a separate table.

This would by far be the fastest solution, though it involves storing on the order of 1 Billion records. You will get some error using a square but you will filter most of the wrong zipcodes. Then you could your original query with a much smaller data set. This may or may not be the fastest, but you can do it by first precomputing the normal vectors NV for each coordinate pair and expressing the vector in terms of it's X, Y and Z components:.

Then the distance between any two coordinates can be computed by determining the difference of the two normal vectors NV1 and NV2 and using Pythagoras equation in three dimensions to get the straight line distance between the two points, that is the chord length C:. To further optimize this you can calculate a some bounds on the coordinates. How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 6 years, 3 months ago.

Active 6 years, 3 months ago.