上篇说了为什么用Geography,这篇具体讲怎么取“范围n米以内的数据”。
Rails里运行Migration,建Portal Model,以下是Schema
1 2 3 4 |
t.geography "lonlat", limit: {:srid=>4326, :type=>"point", :geography=>true}, null: false t.datetime "created_at", null: false t.datetime "updated_at", null: false t.index ["lonlat"], name: "index_parks_on_lonlat", using: :gist |
距离东京站1km内所有的portals
- ST_Distance
float ST_Distance(geography gg1, geography gg2, boolean use_spheroid);
12SELECT id, name FROM portalsWHERE ST_Distance(lonlat, ST_GeogFromText('POINT(139.767052 35.681167)')) < 1000; - ST_DWithin
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);
12SELECT id, name FROM portalsWHERE ST_DWithin(lonlat, ST_GeogFromText('POINT(139.767052 35.681167)'), 1000);
结果: