excelgisdistancelatitude-longitudehaversine

I have a list of lat/long points of interest, and lat/longs for public trans how to find addresses with have all three public trans?

I've been trying to figure out the best way to solve this, I kept coming across the Haversine formula found here: https://www.movable-type.co.uk/scripts/latlong.html

And am trying to only do this in Excel.

The problem I am facing is I have a list of lat/longs of interest (addresses). I also have lat/longs for three transportation types of interest: bus station, commuter rail, and subway. These are in no particular order and I trying to determine if any of the addresses of interest have all three transportation types within a 5 mile radius, if so I'd like to keep those. This is basically a filter.

My Excel file looks like this:

|Address lat |Address long |bus lat |bus long |rail lat |rail long |subway lat |subway long|

I would like to be able to also filter those addresses that only have one transportation type within 5 mi such as: bus, rail, subway.

Solution

  • You can use a formula to calculate the distance between any two points using the geographical coordinates:

    =6371 * 2 * ASIN(SQRT(
    SIN(RADIANS((D2-B2)/2))^2 +
    COS(RADIANS(B2)) * COS(RADIANS(D2)) *
    SIN(RADIANS((E2-C2)/2))^2 ))

    The above formula calculates the distance in kilometers, if you prefer to use The Freedom Units, substitute 6371 with 3959 (approximate Earth radius).