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の関係を間違えないように注意する