Title: Distance between two POINT values in MySQL in a loop
Tags: mysql;geometry
Question: I am storing geometry data in a MySQL Database

```--------------------------------------------------
| ID | SPATIAL_INDEX | COORDS         | DISTANCE  |
| 1  | ...           | POINT(0.5,0.5) |     0     |
| 2  | ...           | POINT(0.7,0.7) |     ?     |
---------------------------------------------------
```

The coords come from a 2D-Cartesian tracking system.
How can I populate the column DISTANCE with the distance between the coords of a row and the last row?

```Row 1: Distance = 0;
Row 2: Distance = Distance between coords of row 1 and row 2
Row 3: Distance = Distance between coords of row 2 and row 3
[...]
```

Comment: Assumuing you have all the rows inserted into the table in the first place, and now need to populate the distances then you can for an UPDATE statement that is a self join

Here is the accepted answer: SQL has a special spatial function to calcutate the distance between two points. you can use that plus a join on the table itself to achieve what you want.

```UPDATE tablename AS a 
LEFT JOIN tablename AS b 
ON b.id = a.id-1 
SET a.distance = IFNULL(DISTANCE(a.coord,b.coord),0);
```



{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:01/09/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><geometry>', 'Title': 'Distance between two POINT values in MySQL in a loop', 'ViewCount': 544, 'id': 41545540, 'user_AboutMe': '', 'user_AccountId': 3013788.0, 'user_DisplayName': 'ManuKaracho', 'user_Reputation': 1150.0, 'user_Views': 87.0, 'user_id': 2556555.0}}