便利な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

(単位はメートル)

2021/02,江端さんの技術メモ

Posted by ebata