Mysql 5.7 GIS GEOMETRY 의 거리계산방식
DROP TABLE test_geom;
CREATE TABLE test_geom (
`mem_geo` GEOMETRY NOT NULL COMMENT 'Geo좌표',
SPATIAL KEY `i_geomidx` (`mem_geo`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='data';
INSERT INTO test_geom (mem_no,mem_geo) VALUES (1,GEOMFROMTEXT(CONCAT('POINT(126.847676 35.145623)')));
INSERT INTO test_geom (mem_no,mem_geo) VALUES (1,GEOMFROMTEXT(CONCAT('POINT(128.064211 35.003676)')));
SELECT X(mem_geo) AS geo_longitude,Y(mem_geo) AS geo_latitude
,ST_Distance_Sphere(
point(-87.6770458, 41.9631174),
point(X(mem_geo), Y(mem_geo))
)
FROM test_geom;
select ST_Distance_Sphere(
point(-87.6770458, 41.9631174),
point(126.847676, 35.145623)
) ;
#거리를계산하는 방식
일반 계산방식 :
6371 * ACOS( COS( RADIANS(35.145623) ) * COS( RADIANS( latitude ) ) * COS( RADIANS(longitude ) - RADIANS(126.847676) ) + SIN( RADIANS(35.145623) ) * SIN( RADIANS( latitude ) ) ) AS distance
# GEOMETRY 컬럼 이용 방식
,ST_Distance(GEOMFROMTEXT(CONCAT('POINT(',",slongitude,",' ', ",slatitude,", ')')), GEOMFROMTEXT(CONCAT('POINT(',X(mem_geo),' ', Y(mem_geo), ')'))) distance
5.6에서는 가까운거리순의 정렬에서의 속도는 크게 차이가 나지 않아서 아쉬움.
# 참고
Mysql 5.6 : https://dev.mysql.com/doc/refman/5.6/en/spatial-function-reference.html
Mysql 5.7 :
https://tighten.co/blog/a-mysql-distance-function-you-should-know-about
https://tighten.co/blog/a-mysql-distance-function-you-should-know-about
https://www.percona.com/blog/2016/02/03/new-gis-features-in-mysql-5-7/