緯度経度の地図情報をMySQLとPythonのMySQLdbを使って扱う

地図情報（緯度経度）をMySQLで扱う。
MySQLはPythonからMySQLdbを使って扱う。
緯度はlatitude、経度はlongitude

テーブル定義

これはpython関係ないsql文です。

create database geosample;
use geosample;
create table place (
	id int auto_increment,
	name varchar(255),
	latlng geometry not null,
	primary key (id),
	spatial key spot_latlng_index(latlng)
) engine=MyISAM default character set utf8;

DBエンジンはMyISAMで。
InnoDBだとspatial keyが使えないらしいので。
geometry型にしたカラムはnot nullを設定し、空間インデックスを使うためにspatial keyを設定します。

SQL

ここからMySQLdbで使っていきます。
まず準備

import MySQLdb
# DBに接続
con = MySQLdb.connect(db="geosample", host="localhost", user="root", passwd="root", charset="utf-8")
cur = con.cursor()

insert

# MySQLでのsql文エスケープは、入れる値の型に関わらず、%sを使う
sql = "insert into sample (name, latlng) values (%s, GeomFromText('POINT(%s %s)'))"
# executemanyで複数のinsert文を一度に行う
cur.executemany(sql, values)
# values => [["kyoto", 135.7808218, 35.0262444], ["ku", 135.757755, 34.985458]]

カラム名が"latlng"なため、"POINT(lat, lng)"とするべきかと思いきや、そうではないらしく、POINT(lng, lat)で。
lng(経度)でlat(緯度)だからx軸がlng、y軸がlatだと思えば自然。

GeomFromText("POINT(lng, lat)")

で緯度経度をgeometry型に直してくれる。

select

lat, lng = 35.0, 135.77
margin = 0.1
# tlはtop left、brはbottom right
sql = ("select name, X(latlng) as lng, Y(latlng) as lat, AsText(latlng), "
"GLength(GeomFromText(Concat('LineString("
"{lng} {lat}', X(latlng), ' ', Y(latlng), ')'))) as len "
"from place where MBRContains(GeomFromText('LineString("
"{tl_lng} {tl_lat}, {br_lng} {br_lat})'), latlng) order by len")\
.format(lng = lng, lat = lat, tl_lng = lng - margin, \
tl_lat = lat - margin, br_lng = lng + margin, br_lat = lat + margin)
count = cur.execute(sql)
results = cur.fetchall()

順番が上と違いますが、説明のためです。

X(latlng), Y(latlng)はlatlngのxとyを求める
AsText(latlng)は文字化けしてるように見えるgeometry型のデータを読める形に変換
LineStringは2点間を結ぶ直線を文字列表現する
- lngとlatの間にカンマはいらない
GLengthはLineStringで取得した表現を倍精度の長さにして返す
Concatは文字列を連結させる関数
- 特に使う意味はないが、素のsqlを触るときには知ってたほうが良さそう
MBRContains(A, B)は矩形エリアA内にある点をBから取得する
- AにLineStringを用いているが、これはLineStringが直線を含む最小の矩形を表現してくれるからである
- BにはDBのカラムであるlatlngをいれる

上のsql文は、「(lng - margin, lat - margin)から(lng +margin, lat + margin)に引いた直線を含む矩形内に存在するデータ(name, 経度、緯度、緯度経度、(lng, lat)からの距離)をテーブルplaceから(lng, lat)からの距離順に取得する」ということになる。

ほとんど、MySQLdbの説明はありませんが、なんとなくわかるはず。