CODEWITHSUNDEEP
mysqlsqlgis
kms
kms

Reputation: 2014

using st_distance in sql to find nearby rows

I have an SQL table with lat, long columns. I am trying to search through the table to find rows that are nearby a POINT.

First step I did was to create a new column coords with Lat, Long coordinates.

Table1

name   Lat        Long         
post   37.788151  -122.407570  
sutter 37.789551  -122.408302  
oak    37.815730  -122.288810  


ALTER TABLE schema_name.table1 ADD coords Point;

UPDATE schema_name.table1 SET coords = Point(`Long`, Lat);

Query to find rows that are within <500ft distance condition:

select * from schema_name.table1 
where st_distance(Point(-122.4104907, 37.789551), coords) <= 500;

First question is, what unit do I express the distance in? How do I convert from miles? I used meters and do not get any results. The Point is within 500ft distance.

https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-distance

Upvotes: 0

Views: 704

Answers (1)

Akina
Akina

Reputation: 42611

CREATE TABLE test (id INT, name VARCHAR(255), Latitude DECIMAL(10,6), Longtitude DECIMAL(10,6));
INSERT INTO test VALUES
(1, 'post',   37.788151,  -122.407570),  
(2, 'sutter', 37.789551,  -122.408302),  
(3, 'oak',    37.815730,  -122.288810);
ALTER TABLE test ADD coordinates Point;
UPDATE test SET coordinates = Point(Longtitude, Latitude);
SELECT id, name, Latitude, Longtitude, ST_AsText(coordinates) FROM test;
id name Latitude Longtitude ST_AsText(coordinates)
1 post 37.788151 -122.407570 POINT(-122.40757 37.788151)
2 sutter 37.789551 -122.408302 POINT(-122.408302 37.789551)
3 oak 37.815730 -122.288810 POINT(-122.28881 37.81573) 
SELECT t1.name, t2.name, ST_Distance_Sphere(t1.coordinates, t2.coordinates)
FROM test t1
JOIN test t2 ON t1.id < t2.id
name name ST_Distance_Sphere(t1.coordinates, t2.coordinates)
post sutter 168.4384736143564
sutter oak 10894.417360668858
post oak 10875.419213829204

db<>fiddle here

The distance is calculated in meters. The values seems to be correct (checked with Yandex.Map ruler).

Upvotes: 2

Related Questions