MySQLで緯度経度を使う際の備忘録

MySQLで位置情報を扱う

緯度 = latitude = Y

経度 = longitude = X

//table

CREATE TABLE IF NOT EXISTS `geo_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`geometry` geometry NOT NULL COMMENT 'ジオメトリ',
PRIMARY KEY (`id`),
SPATIAL KEY `geometry` (`geometry`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

//insert

INSERT INTO `geo_table` (`geometry`) VALUES (PointFromText('POINT( longitude latitude)');

INSERT INTO `geo_table` (`geometry`) VALUES (PointFromText('POINT( x y)');

INSERT INTO `geo_table` (`geometry`) VALUES (PointFromText('POINT( 経度 緯度)');

//範囲内検索

 SELECT X(geometry) AS longitude , Y(geometry) AS latitude FROM geo_table WHERE MBRWithin( geometry , GeomFromText('LineString(longitudeA latitudeA , longitudeB latitudeB)');

緯度経度とYXの関係を間違えないように注意する