0

I'm currently using Redshift and I have a table that has a list of zip codes along with their latitudes and longitudes. I'm trying to write a sql statement where I can specify a given zip code and have it return all of the zip codes within a 5 mile radius.

Any ideas on how I can approach this?

Here's what I had tried: 

SELECT zip, city, latitude, longitude,
  69.0* DEGREES(ACOS(COS(RADIANS(latpoint))
             * COS(RADIANS(latitude))
             * COS(RADIANS(longpoint) - RADIANS(longitude))
             + SIN(RADIANS(latpoint))
             * SIN(RADIANS(latitude)))) AS distance_in_miles

FROM zip_code_db JOIN ( SELECT 42.81 AS latpoint, -70.81 AS longpoint ) AS p ON 1=1 ORDER BY distance_in_miles

I'm trying to see if there is a way to use zip codes instead of specifying latitues and longitudes

user8659376
  • 369
  • 4
  • 8
  • 19
  • can you post your table structure and what have you tried so far? – Pankaj Gadge Jan 31 '18 at 00:25
  • There are already thousands of post available to the problem related to this https://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ – sumit Jan 31 '18 at 00:27
  • Possible duplicate of [Calculate distance between Zip Codes... AND users.](https://stackoverflow.com/questions/3983325/calculate-distance-between-zip-codes-and-users) – clinomaniac Jan 31 '18 at 00:30
  • Yes, I had given this link a try and was wondering if there was a way to use zip codes instead of the latitutdes and longitudes: – user8659376 Jan 31 '18 at 00:46
  • A zip code is an _area_, not a _point_. If you want to keep it simple your best bet is to assign a lat/long to a zip code (in the middle somewhere) and approximate it that way. Otherwise you need to do things like work out the longest distance between the outside borders of two areas – Nick.Mc Jan 31 '18 at 00:53

0 Answers0