Category: Ruby

• PostGIS笔记（上）
• PostGIS笔记（中）

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

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

距离东京站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);

结果：

Continue reading “PostGIS笔记（下）”