PostGIS笔记（下） – 箱猫日和

上篇说了为什么用Geography，这篇具体讲怎么取“范围n米以内的数据”。

Rails里运行Migration，建Portal Model，以下是Schema

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

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);

PgSQL

SELECT id, name FROM portals WHERE ST_Distance(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’)) < 1000;

1
2

SELECT id, name FROM portals
WHERE 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);

PgSQL

SELECT id, name FROM portals WHERE ST_DWithin(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’), 1000);

1
2

SELECT id, name FROM portals
WHERE ST_DWithin(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’), 1000);

结果：

   id   |    name
——–+————
 188930 | 常盤橋公園
 188941 | 坂本町公園
 188946 | 水谷橋公園
 188952 | 常盤公園
 188959 | 楓川公園
 188983 | 本石町公園

id | name

——–+————

188930 | 常盤橋公園

188941 | 坂本町公園

188946 | 水谷橋公園

188952 | 常盤公園

188959 | 楓川公園

188983 | 本石町公園

虽然只运行了一条语句，但是两条语句的运行速度不同是完全可以直观感受出来的。
ST_DWithin 比 ST_Distance 快很多。
这里的结果并没有根据距离远近排序，只是一个单纯的结果。

接下来我们继续用 ST_Distance 来取按距离排序的portals

距离东京站1km内的所有portals（按近远距排序）

SELECT
    id,
    name,
    ST_Distance(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’)) AS distance
  FROM portals
  WHERE
    ST_DWithin(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’), 1000)
  ORDER BY distance;

SELECT

id,

name,

ST_Distance(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’)) AS distance

FROM portals

WHERE

ST_DWithin(lonlat, ST_GeogFromText(‘POINT(139.767052 35.681167)’), 1000)

ORDER BY distance;

排序后结果

   id   |    name    |   distance
——–+————+————–
 188930 | 常盤橋公園 | 571.35848232
 188946 | 水谷橋公園 | 799.62942609
 188959 | 楓川公園   |   818.184905
 188952 | 常盤公園   | 820.70891661
 188983 | 本石町公園 | 820.73453942
 188941 | 坂本町公園 | 913.49119403

id | name | distance

——–+————+————–

188930 | 常盤橋公園 | 571.35848232

188946 | 水谷橋公園 | 799.62942609

188959 | 楓川公園 | 818.184905

188952 | 常盤公園 | 820.70891661

188983 | 本石町公園 | 820.73453942

188941 | 坂本町公園 | 913.49119403

顺便，速度很快。

大家可能注意到了，不管是 ST_Distance 还是 ST_DWithin 的最后都有个参数 use_spheroid，默认是 false。
根据文档，设置为 true 的时候计算速度会变慢。
之前说过 Geography 是球面坐标系，那 Spheroid 是什么呢，是回转椭圆球体，地球本来的形状。
会慢多少我没试过，这次需求也没有要求那么精确，顺便即便为 true 算出来的距离好像也是一样的。

Model Scope

接下来就是把查询语句写到model里可以方便查询就ok了。

scope :nearby_in_distance, -> (lon, lat, distance = 1000) {
  geograghy_point = “ST_GeogFromText(‘POINT(#{lon} #{lat})’)”
  where(“ST_DWithin(lonlat, #{geograghy_point}, #{distance})”)
    .order(“ST_Distance(lonlat, #{geograghy_point})”)
}

scope :nearby_in_distance, –> (lon, lat, distance = 1000) {

geograghy_point = “ST_GeogFromText(‘POINT(#{lon} #{lat})’)”

where(“ST_DWithin(lonlat, #{geograghy_point}, #{distance})”)

.order(“ST_Distance(lonlat, #{geograghy_point})”)

}

以上，Controller 里就能用 Portal.nearby_in_distance(139.767052, 35.681167) 来取所有距离东京站1km以内所有portals的记录了。

注意事项

顺便这里说一下，Google Maps 的查询结果返回的是 {lat, lon}，PostGIS要求的参数是 (lon lat) ，不要写反了。

如果用ActiveAdmin作为管理界面的，:lonlat 的显示是字符串 POINT(139.767052 35.681167)。
但是编辑的时候会报错，具体错误不记得了，反正是说没有这个input类型。
因为 :lonlat 的类型是 geography。
解决方法是自己创建新的 Formtastic input ，方法见 Github。

$ ./bin/rails generate formtastic:input GeographyInput –extend string

1	$ ./bin/rails generate formtastic:input GeographyInput —extend string

inputs/geography_input.rb 里的代码：

class GeographyInput < Formtastic::Inputs::StringInput
  def input_html_options
    {
      class: ‘geography-input’
    }
  end
end

class GeographyInput < Formtastic::Inputs::StringInput

def input_html_options

{

class: ‘geography-input’

}

end

就可以编辑Geography类型的Column了。

总结

最后Production环境就用了一台db.t2.micro的RDS，放了Staging和Production环境两个数据库。
压测结果出乎意料的速度很快，相当满意，虽然写压测脚步花了好长时间。
（因为要随机出日本国内一个坐标，而且这个随机坐标要包含在数据密集的地方，最后看地图取四个顶点坐标花了很久）

这次项目Backend/Server只有我一个人，Leader帮我CR，但是最满意却不是Backend这块，而是前端这块。
除了这个游戏 App Backend/Server ，还一个人开发了一个用Vuex的坐标数据可视化的地图工具，
用来管理Portal坐标，比如给主坐标添加subMarker，拖拽编辑、删除、无效化数据等等。
另外还做了一个基于 Redux 的海报生成工具，写了Print专用CSS。
这两个系统产品设计(?)/设计/前后端/server都是自己一个人做的，相当自由，不过都是给客户用的，所以没有公开过。
还记得 Leader 看我在那里用Photoshop给GoogleMaps做Marker图标的时候那(；´∀｀)的表情www
总之很开心，2个月的时间里从0开始学PostgreSQL/PostGIS/Vue.js/Redux，有种无与伦比充实感。

在这次开发过程中发现了RubyChina的强大之处，这之后搜东西除了Stackoverflow之外又多了一个选项。

当然这blog也不是白写的，之后要翻译成日文放到公司技术博客上，被逼的……这也是写这blog的初衷。
但是写都写了，就好好记录一下这两个月的成果，当然因为业务原因换掉了很多词汇，删了很多字段不能写在博客里，也是leader要求的。
App六月份就要下线了，下线后估计就可以公开两个内部系统的截图了。

最后吐槽一下，
本来leader说可以写中文放技术博客上的，但是我和他说因为有墙放中文博客也没有多少人能搜到这里来，然后他就让我翻译成日文算了。
我们小组的美国人好像写了一篇英文的给了leader，但leader和我说肯定是英文的所以他拖到现在还没审阅。
那最初干嘛让我写中文的呢……你又看不懂……心好累……_(:3 」∠ )_

最后的最后，感谢看过的朋友们，感谢看完的朋友们。