This month I’ve been programming quite a bit in PHP and MySQL with respect to GIS. Snooping around the net, I actually had a hard time finding some of the Geographic calculations to find the distance between two locations so I wanted to share them here.
The simple way of calculating a distance between two points is using the Pythagorean formula to calculate the hypotenuse of a triangle (A² + B² = C²). This is known as the Euclidean distance.
That’s an interesting start but it doesn’t apply with Geography since the distance between lines of latitude and longitude are not equal distances apart. As you get closer to the equator, lines of latitude get further apart. If you use some kind of simple triangulation equation, it may measure distance accurately in one location and terribly wrong in the other, because of the curvature of the Earth.
Great Circle Distance
The routes that are traveled long distances around the Earth are known as the Great Circle Distance. That is… the shortest distance between two points on a sphere is different than the points on a flat map. Combine that with the fact that the latitude and longitude lines aren’t equidistant… and you’ve got a difficult calculation.
Here’s a fantastic video explanation of how Great Circles work.
The Haversine Formula
The distance using the curvature of the Earth is incorporated in the Haversine formula, which uses trigonometry to allow for the curvature of the earth. When you’re finding the distance between 2 places on earth (as the crow flies), a straight line is really an arc.
This is applicable in air flight – have you ever looked at the actual map of flights and noticed they are arched? That’s because it’s shorter to fly in an arch between two points than directly to the location.
PHP: Calculate Distance Between 2 Points of Latitude and Longitude
Here’s the PHP formula for calculating the distance between two points (along with Mile vs. Kilometer conversion) rounded to two decimal places.
The variables are:
- $Latitude1 – a variable for your first location’s latitude.
- $Longitude1 – a variable for your first location’s longitude
- $Latitude2 – a variable for your second location’s latitude.
- $Longitude2 – a variable for your second location’s longitude.
- $unit – the default being miles. This can be updated or passed as kilometers.
Python: Calculate Distance Between 2 Points of Latitude and Longitude
Anyways, here’s the Python formula for calculating the distance between two points (along with Mile vs. Kilometer conversion) rounded to two decimal places. Credit to my son, Bill Karr who is a Data Scientist for OpenINSIGHTS, for the code.
from numpy import sin, cos, arccos, pi, round def rad2deg(radians): degrees = radians * 180 / pi return degrees def deg2rad(degrees): radians = degrees * pi / 180 return radians def getDistanceBetweenPointsNew(latitude1, longitude1, latitude2, longitude2, unit = 'miles'): theta = longitude1 - longitude2 distance = 60 * 1.1515 * rad2deg( arccos( (sin(deg2rad(latitude1)) * sin(deg2rad(latitude2))) + (cos(deg2rad(latitude1)) * cos(deg2rad(latitude2)) * cos(deg2rad(theta))) ) ) if unit == 'miles': return round(distance, 2) if unit == 'kilometers': return round(distance * 1.609344, 2)The variables are:
- latitude1 – a variable for your first location’s latitude.
- longitude1 – a variable for your first location’s longitude
- latitude2 – a variable for your second location’s latitude.
- longitude2 – a variable for your second location’s longitude.
- unit – the default being miles. This can be updated or passed as kilometers.
MySQL: Retrieving All Records Within A Range By Calculating Distance In Miles Using Latitude and Longitude
It’s also possible to use SQL to do a calculation to find all records within a specific distance. In this example, I’m going to query MyTable in MySQL to find all the records that are less than or equal to variable $distance (in Miles) to my location at $latitude and $longitude:
The query for retrieving all of the records within a specific distance by calculating distance in miles between two points of latitude and longitude are:
You’ll need to customize this:
- $longitude – this is a PHP variable where I’m passing the longitude of the point.
- $latitude – this is a PHP variable where I’m passing the longitude of the point.
- $distance – this is the distance that you would like to find all the records less or equal to.
- table – this is the table… you’ll want to replace that with your table name.
- latitude – this is the field of your latitude.
- longitude – this is the field of your longitude.
MySQL: Retrieving All Records Within A Range By Calculating Distance In Kilometers Using Latitude and Longitude
And here’s the SQL query using kilometers in MySQL:
$query = "SELECT *, (((acos(sin((".$latitude."*pi()/180)) * sin((`latitude`*pi()/180)) + cos((".$latitude."*pi()/180)) * cos((`latitude`*pi()/180)) * cos(((".$longitude."- `longitude`) * pi()/180)))) * 180/pi()) * 60 * 1.1515 * 1.609344) as distance FROM `table` WHERE distance <= ".$distance."You’ll need to customize this:
- $longitude – this is a PHP variable where I’m passing the longitude of the point.
- $latitude – this is a PHP variable where I’m passing the longitude of the point.
- $distance – this is the distance that you would like to find all the records less or equal to.
- table – this is the table… you’ll want to replace that with your table name.
- latitude – this is the field of your latitude.
- longitude – this is the field of your longitude.
I utilized this code in an enterprise mapping platform that we utilized for a retail store with over 1,000 locations across North America and it worked beautifully.
Microsoft SQL Server Geographic Distance: STDistance
If you’re utilizing Microsoft SQL Server, they offer their own function, STDistance for calculating the distance between two points using the Geography data type.
DECLARE @g geography; DECLARE @h geography; SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326); SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326); SELECT @g.STDistance(@h);Hat tip to Manash Sahoo, VP and Architect of Highbridge.