便利なpostGIS関数
■2点間の距離の算出
float ST_Distance(geometry g1, geometry g2);
float ST_Distance(geography gg1, geography gg2);
============
139.46383010 35.6078055 と 139.48004430 35.58936550 の距離を求めろ
============
SELECT ST_Distance('SRID=4326;POINT(139.46383010 35.6078055)'::GEOGRAPHY,'SRID=4326;POINT(139.48004430 35.58936550)'::GEOGRAPHY);
st_distance
---------------
2518.87992511 (単位はメートル)
(1 行)
SELECT ST_Distance(ST_Transform(ST_GeomFromText('POINT(139.46383010 35.6078055)',4326),26986),ST_Transform(ST_GeomFromText('POINT(139.48004430 35.58936550)',4326),26986));
st_distance
-----------------
2534.9890933572 (単位はメートル)
(1 行)
============
点(139.46507, 35.59577)と source 608 の距離を求めろ
============
SELECT ST_Distance('SRID=4326;POINT(139.46507 35.59577)'::GEOGRAPHY, the_geom) from ways where source = 608;
st_distance
-------------
48.64958043 (単位はメートル)
(1 行)
■点と線の最短距離の算出
===================
点(139.46383010 35.6078055) と
線(139.47364070 35.59500190),(139.47500790 35.59561250),(139.47618950 35.59667510) の # 3点は繋がっている
最短距離を求めろ
===================
SELECT ST_Distance('SRID=4326;POINT(139.46383010 35.6078055)'::GEOGRAPHY,'SRID=4326;LINESTRING(139.47364070 35.59500190,139.47500790 35.59561250, 139.47618950 35.59667510)'::GEOGRAPHY);
st_distance
---------------
1667.13188667 (単位はメートル)
(1 行)
■任意の座標に近いノードを1つ抽出
SELECT source, x1 as longitude, y1 as latitude, ST_Distance('SRID=4326;POINT(139.78468208 35.64553503)'::GEOGRAPHY, the_geom) as dist FROM ways ORDER BY dist limit 1;
;
source | longitude | latitude | dist
--------+-------------+------------+-------------
742 | 139.7831864 | 35.6469072 | 20.44846388
(1 row)
■任意の座標に近いノードを抽出
boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);
===============
点(139.47500790 35.59561250)から半径300メートル以内の全部のノードを、近い順に出せ
===============
SELECT source, x1 as longitude, y1 as latitude, ST_Distance('SRID=4326;POINT(139.47500790 35.59561250)'::GEOGRAPHY, the_geom) as dist FROM ways WHERE ST_DWithin(the_geom, ST_GeographyFromText('SRID=4326;POINT(139.47500790 35.59561250)'), 300.0) ORDER BY dist;
source | longitude | latitude | dist
--------+-------------+------------+--------------
277 | 139.4742202 | 35.5952626 | 0
342 | 139.4737614 | 35.5960846 | 0
554 | 139.4750079 | 35.5956125 | 0
554 | 139.4750079 | 35.5956125 | 0
465 | 139.4756076 | 35.5956018 | 54.35238655
465 | 139.4756076 | 35.5956018 | 54.35883189
148 | 139.4753681 | 35.5942035 | 62.47683957
488 | 139.4755625 | 35.595278 | 62.47683957
277 | 139.4742202 | 35.5952626 | 78.20859781
309 | 139.4758617 | 35.5957534 | 78.9363046
309 | 139.4758617 | 35.5957534 | 78.9363046
163 | 139.4736407 | 35.5950019 | 81.2567669
597 | 139.4728928 | 35.5961536 | 81.2567669
406 | 139.4760808 | 35.5958841 | 101.79071989
211 | 139.4761077 | 35.5959027 | 104.73755527
580 | 139.4761706 | 35.5959419 | 111.52418485
201 | 139.4762056 | 35.5959637 | 115.32014973
(単位はメートル)