【PostgreSQL】距離が近い順にソートする

GISデータに対してある地点から100m圏内のデータで、かつ、１番近いデータを取得するということがやりたい

ある地点からの距離検索はGeometoryとGeographyのうち、Geographyを使うのがよさそうなのはわかった

uga-box.hatenablog.com

そして、100m圏内というのは以下のクエリで絞り込めることがわかった

where ST_Distance(geography g1, geography g2) <= #{distance}

http://postgres.cn/docs/postgis-2.3/ST_Distance.html

つぎに１番近いデータをどうやるか考える

このとき、ORDER BYで距離が近い順にソートし、LIMIT 1で１件に絞れがよいと考えた

ORDER BYで距離が近い順にソート

ドキュメントを読むと具体的な距離検索ではなく、距離が近い順検索は「最近傍検索」というらしい

postgis.net

※画像解析などのアルゴリズムの分野でも言われるので気をつけたい

とりあえず、PostgreSQLではORDER BY <-> 位置 ascで距離が近い順にソートができるみたい

なので、WHERE句で絞り込んだ後、ORDER BYでソートし、LIMIT 1で１件に絞るとよさそう

select *
from COORDINATE C
where ST_Distance(#{coordinate}, C.COORDINATE_GEOGRAPHY) <= #{distance}
order by C.COORDINATE_GEOGRAPHY <-> #{coordinate} asc
limit 1;