2011|08|
2013|10|11|12|
2014|01|02|03|04|05|06|07|08|09|10|11|12|
2015|01|02|03|05|06|07|08|09|10|11|12|
2016|01|03|04|05|06|07|08|09|10|11|12|
2017|01|02|03|04|05|06|07|08|09|10|11|12|
2018|01|02|03|04|05|06|07|08|09|10|11|12|
2019|01|02|03|04|05|06|07|08|09|10|11|12|
2020|01|02|03|04|

2018-11-01 便利なpostGIS関数

_ 便利な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 行)

■任意の座標に近いノードを抽出

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

(単位はメートル)


2019-11-01 体感デモで分からなくなったら、ここへ行け

/home/kobore/www/soft/hitachi_ride_hailing_go

musubu_hitachi_ride_hailing_go - hitachi_ride_hailing_go - Musubu Inc. code repository.pdf