Nearest-Neighbour Searching
기본적으로 근접 검색 function은 없다.
인터넷을 찾다보면, 거리를 구해서 소팅을 하는 방법이 주로 보이는데.. 이건 데이터가 많을 때는 문제가 크다.
https://postgis.net/workshops/postgis-intro/knn.html
29. Nearest-Neighbour Searching — Introduction to PostGIS
29.2. Nearest Neighbor Join The index assisted order by operator has one major draw back: it only works with a single geometry literal on one side of the operator. This is fine for finding the objects nearest to one query object, but does not help for a sp
postgis.net
그래서.. 먼저 반경검색으로 데이터를 추린후에 거리로 소팅을 했다.
select near.st_nd_id, ST_Distance(near.shape, ('SRID=5179;' || ST_ASText(st_transform(ST_SetSRID( ST_Point(127.29 36.4794), 4326),
'+proj=tmerc +lat_0=38 +lon_0=127.5 +k=0.9996 +x_0=1000000 +y_0=2000000 +ellps=GRS80 +units=m +no_defs')))::geometry) as dist
from (select shape, st_nd_id, ed_nd_id from t1.m_link where ST_Intersects(shape , ('SRID=5179;' || ST_AsText(ST_BUFFER(ST_Transform(ST_SetSRID( ST_Point(127.29 36.4794), 4326),
'+proj=tmerc +lat_0=38 +lon_0=127.5 +k=0.9996 +x_0=1000000 +y_0=2000000 +ellps=GRS80 +units=m +no_defs')::geometry, 5000, 'quad_segs=8')))::geometry)) as near order by dist asc limit 1