CODEWITHSUNDEEP
mysqlsql
Kevin Gardenhire
Kevin Gardenhire

Reputation: 656

MySQL St_distance_Sphere function

I am trying to query a small table to find all the records in the table that are within 1Km of the inputted coordinate point. Each record in the table has a 'Lat' and 'Long' column with the respective points. My query is

var sql = `SELECT * FROM GeoTest WHERE st_distance_sphere(POINT(-100,35),POINT(Long,Lat)) <= 1000`

error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Long,Lat)) <= 1000\'

Data in the table 

id        Lat                 Long

1   30.5092060000000000 -97.6046760000000000
2   31.1310390000000000 -97.7805610000000000

I do not know how to use the returned latitude and longitude from the selected row to compare the distance and then only return the record if it is within 1Km. How can I substitute the Long and Lat fields from the record into the st_distance_sphere function? I am sure I am missing something very simple here. Thanks for helping this SQL noob!

Upvotes: 5

Views: 3072

Answers (2)

ItDepends
ItDepends

Reputation: 66

As of 2024 your original query should work (at least for MySQL 8.0.40) where I just tried:

mysql> SELECT ST_Distance_Sphere(POINT(-120.3502278, 40.3867781), POINT(-120.3502778,  40.3867781)) as distance;
+-------------------+
| distance          |
+-------------------+
| 4.234781930151073 |
+-------------------+
1 row in set (0.00 sec)

Upvotes: 0

nbk
nbk

Reputation: 49373

Use this works with 5.7 and 8.0

   Select st_distance_sphere(POINT(-100,35),
   POINT ( `Long` , `Lat`) ) From GeoTest;

Upvotes: 4

Related Questions