postgis 求离点最近的线

With inputPoint AS
(Select st_geomfromtext('POINT(114.19061431884767 22.543833325111848)', 4326) as pnt )
select buffer.* from (
select r.* from road as r,inputPoint as p
      where st_intersects(r.geom,ST_Buffer(Geography(p.pnt), 100,'quad_segs=8')::geometry) ) as buffer,inputPoint as p
order by ST_distance(geom,p.pnt) limit 1

取buffer 100米的范围的道路，按点到道路的距离排序，取第一条

代码升级，获得最近的线以及最近的点

With inputPoint AS
(select st_geomfromtext('POINT(114.19061431884767 22.543833325111848)', 4326) as pnt ),
nearestLine AS
(select buffer.* from (
select road.* from shenzhen_osm_shp_graph as road,inputPoint as p
      where st_intersects(road.geom,ST_Buffer(Geography(p.pnt), 100,'quad_segs=8')::geometry) ) as buffer,inputPoint as p
order by ST_distance(geom,p.pnt) limit 1
)
 
select st_astext(ST_ClosestPoint(line.geom,point.pnt)), st_astext(line.geom) from nearestLine as line ,inputPoint as point