MySQL: Working with coordinates in the database

How to insert coordinates in the database and how to operate with them in MySQL.

For all examples i have this two simple tables. Both tables contains 3000 rows with data:

CREATE TABLE `test2` (
  `nl_point_id` INT(11) NOT NULL AUTO_INCREMENT,
  `lat` DOUBLE DEFAULT 0,
  `lng` DOUBLE DEFAULT 0,
  PRIMARY KEY (`nl_point_id`)
) ENGINE=INNODB; 
        
CREATE TABLE `test` (
  `nl_point_id` INT(11) NOT NULL AUTO_INCREMENT,
  `gps` POINT DEFAULT NULL,
  PRIMARY KEY (`nl_point_id`)
) ENGINE=INNODB;

CREATE TABLE `test2` (

`nl_point_id` INT(11) NOT NULL AUTO_INCREMENT,

`lat` DOUBLE DEFAULT 0,

`lng` DOUBLE DEFAULT 0,

PRIMARY KEY (`nl_point_id`)

) ENGINE=INNODB;

CREATE TABLE `test` (

`nl_point_id` INT(11) NOT NULL AUTO_INCREMENT,

`gps` POINT DEFAULT NULL,

PRIMARY KEY (`nl_point_id`)

) ENGINE=INNODB;

The first table has a column ready to save the coordinate values separately, the second table will store the coordinates as POINT.

Save coordinates separately

This way we all know, we store the longitude and latitude separately with simple sql query:

INSERT INTO `test2`(lat, lng) 
  VALUES(49.227239, 17.564932)

1 2	INSERT INTO `test2`(lat, lng) VALUES(49.227239, 17.564932)

To obtain data we can use a simple query like the following:

SELECT FORMAT(lat, 6) AS lat, FORMAT(lng, 6) AS lng 
FROM test2 
LIMIT 100;

SELECT FORMAT(lat, 6) AS lat, FORMAT(lng, 6) AS lng

FROM test2

LIMIT 100;

This is not anything new and interesting.

Store the coordinates as POINT

The possibility that I would like to show is save the coordinates in point form. A Point is a geometry that represents a single location in coordinate space.

Work of this type is simple. This is a simple example:

SET @p2 = POINTFROMTEXT('POINT(49.227239 17.564932)');
SELECT X(@p) AS lat, Y(@p) AS lng;

1 2	SET @p2 = POINTFROMTEXT('POINT(49.227239 17.564932)'); SELECT X(@p) AS lat, Y(@p) AS lng;

This code declare local variable in Mysql and select data from this variable. Variable is declared as POINT type, and contain coordinates.
In this case we can simple get X and Y coordinates from variable as you can see.

In the database we can save this variable simply as follows:

INSERT INTO `test`(gps) 
VALUES(POINTFROMTEXT('POINT(49.227239 17.564932)'));

1 2	INSERT INTO `test`(gps) VALUES(POINTFROMTEXT('POINT(49.227239 17.564932)'));

And again select values from table:

SELECT ASWKT(gps) AS `aswkt`, X(gps) AS `lat`, Y(gps) AS `lng`
FROM `test` 
LIMIT 100;

SELECT ASWKT(gps) AS `aswkt`, X(gps) AS `lat`, Y(gps) AS `lng`

FROM `test`

LIMIT 100;

The result will look like:

------------------------------------------------------
| aswkt                      | lat       | lng       |
| POINT(51.227339 17.564932) | 51.227339 | 17.564932 |
| POINT(51.227439 17.564932) | 51.227439 | 17.564932 |
| POINT(51.227539 17.564932) | 51.227539 | 17.564932 |
| POINT(51.227639 17.564932) | 51.227639 | 17.564932 |
| POINT(51.227739 17.564932) | 51.227739 | 17.564932 |
------------------------------------------------------

------------------------------------------------------

| aswkt | lat | lng |

| POINT(51.227339 17.564932) | 51.227339 | 17.564932 |

| POINT(51.227439 17.564932) | 51.227439 | 17.564932 |

| POINT(51.227539 17.564932) | 51.227539 | 17.564932 |

| POINT(51.227639 17.564932) | 51.227639 | 17.564932 |

| POINT(51.227739 17.564932) | 51.227739 | 17.564932 |

------------------------------------------------------

ASWKT – Converts a value in internal geometry format to its WKT representation and returns the string result.
Again, we can easily access to the items saved in the coordinates with X and Y “axes”.

Distance between the points

In this case when we have values stored as POINT, we can use the operations of this type variable. As calculate distance between two points. This is not a geometric distance, but only the distance in the polygon points.

SELECT FORMAT(X(gps), 5) AS `lat`, FORMAT(Y(gps), 5) AS `lng`, GLENGTH(
		LINESTRINGFROMWKB(
			LINESTRING(gps, GEOMFROMTEXT('POINT(42.227239 14.664932)')))
		)
	
AS `distance_points`
FROM `test`
ORDER BY distance ASC 
LIMIT 100;

SELECT FORMAT(X(gps), 5) AS `lat`, FORMAT(Y(gps), 5) AS `lng`, GLENGTH(

LINESTRINGFROMWKB(

LINESTRING(gps, GEOMFROMTEXT('POINT(42.227239 14.664932)')))

)

AS `distance_points`

FROM `test`

ORDER BY distance ASC

LIMIT 100;

As a result we get the distance value in units. If we calculate the distance between two points that differ by one unit, we would get a value of 1.

If we want to calculate the distance, we would have a saved value of the distance from the central point.

Calculation of the distance from a given point

If you want to calculate the distance from a certain point, we can proceed in both cases as well.

Coordinates are separately

Calculate the distance between two points for each row:

SELECT ( (ACOS(
		SIN(X(@p) * PI() / 180) * SIN(lat * PI() / 180) + 
		COS(X(@p) * PI() / 180) * COS(lat * PI() / 180) * 
		COS( ( lng - lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515
	)
		* 1.609344 /* in kilometers */
	AS `distance` 
FROM `test2`
ORDER BY `distance`;

SELECT ( (ACOS(

SIN(X(@p) * PI() / 180) * SIN(lat * PI() / 180) +

COS(X(@p) * PI() / 180) * COS(lat * PI() / 180) *

COS( ( lng - lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515

)

* 1.609344 /* in kilometers */

AS `distance`

FROM `test2`

ORDER BY `distance`;

Coordinates are POINT format

As in the previous example, only approaching the coordinates through the values from POINT variable:

SELECT ( (ACOS(
		SIN(X(@p) * PI() / 180) * SIN(X(gps) * PI() / 180) + 
		COS(X(@p) * PI() / 180) * COS(X(gps) * PI() / 180) * 
		COS( ( Y(@p) - Y(gps)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515
	)
		* 1.609344 /* in kilometers */
	AS `distance` FROM `test`;

SELECT ( (ACOS(

SIN(X(@p) * PI() / 180) * SIN(X(gps) * PI() / 180) +

COS(X(@p) * PI() / 180) * COS(X(gps) * PI() / 180) *

COS( ( Y(@p) - Y(gps)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515

)

* 1.609344 /* in kilometers */

AS `distance` FROM `test`;

Create a function to calculate the distance

Pouze doporučení: V tomto případě je lepší vytvořit jednoduchou funkci v databázi.

DELIMITER $$

DROP FUNCTION IF EXISTS `slevici`.`CalculateDistanceKm`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `CalculateDistanceKm`( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS FLOAT
BEGIN
		DECLARE rads FLOAT DEFAULT 0;
		SET lat1 = lat1 * PI() / 180;
		SET lon1 = lon1 * PI() / 180;
		SET lat2 = lat2 * PI() / 180;
		SET lon2 = lon2 * PI() / 180;
		SET rads = ACOS( 0.5*((1.0+COS(lon1-lon2))*COS(lat1-lat2) - (1.0-COS(lon1-lon2))*COS(lat1+lat2)) ); 
		RETURN 6378.388 * rads;
	END$$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `slevici`.`CalculateDistanceKm`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `CalculateDistanceKm`( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS FLOAT

BEGIN

DECLARE rads FLOAT DEFAULT 0;

SET lat1 = lat1 * PI() / 180;

SET lon1 = lon1 * PI() / 180;

SET lat2 = lat2 * PI() / 180;

SET lon2 = lon2 * PI() / 180;

SET rads = ACOS( 0.5*((1.0+COS(lon1-lon2))*COS(lat1-lat2) - (1.0-COS(lon1-lon2))*COS(lat1+lat2)) );

RETURN 6378.388 * rads;

END$$

DELIMITER ;

Now we can use the same in both cases:

/** define central point **/
SET @p = POINTFROMTEXT('POINT(51.227239 17.564931)');

SELECT CalculateDistanceKm(X(@p), Y(@p), X(gps), Y(gps)) AS distance 
FROM test;

SELECT CalculateDistanceKm(X(@p), Y(@p), lat, lng) AS distance 
FROM test2;

/** define central point **/

SET @p = POINTFROMTEXT('POINT(51.227239 17.564931)');

SELECT CalculateDistanceKm(X(@p), Y(@p), X(gps), Y(gps)) AS distance

FROM test;

SELECT CalculateDistanceKm(X(@p), Y(@p), lat, lng) AS distance

FROM test2;

in conclusion

Neither way is probably better, it always depends on the use of.

Comments are closed for this page

Pavel Hájek

20/02/2016

Thank you for this article

nabil

18/09/2016

Very nice article.I have many gps trajectory data, it have 4 row for id, date time, lat, long. How to calculate all of data to find the distance from start and last trajectory using Haversine or other query? I don’t know to set the lat1, lat2, lon1, lo2 if the data is so big.

nike

30/09/2016

official-drugstore.com scam

02/03/2017

Amazing! Its truly amazing article, I have got much clear idea regarding from this article.

mau

27/07/2017

Hello,Thanks for such a great informative article…But i have a question how can i get results within (lat1,long1) and (lat2,long2) so that i can get list of all the location from db …The db have id,lat,long,name,country,user_count

Regards

ارز دیجیتال

11/06/2018

such a great article . really saved my day

John Smith

05/03/2020

Nice documentation. I don’t understand some samples, like f.e. the one of “Coordinates are separately”: Which LAT and LNG means which coordinate set? For what does “@p” stand for?
“Create a function to calculate the distance”: Is the output in meter, kilometer, …?

Flynn

20/04/2020

Demi

20/04/2020

Otilia

21/04/2020

Trackbacks for this post

MySQL: Working with coordinates in the database...