如何计算最近点到PostGIS中的线串?
问题描述:
我按照这个question来计算最近的道路(线串)的POI。我能够计算线串中的最近点,但是我无法找到从POI到线串上最近点(顶点)的距离。如何计算最近点到PostGIS中的线串?
这是我寻找最近POI的代码。
CREATE TABLE road(id serial PRIMARY KEY, the_geog geography(LINESTRING,4326));
CREATE TABLE poi(gid serial PRIMARY KEY, name varchar, city varchar, the_geog geography(POINT,4326))
值是:
INSERT INTO road (id, the_geog) VALUES (123, ST_GeographyFromText('SRID=4326;LINESTRING(85.280194 23.296728,85.281572 23.297479)'));
要计算最近点:
SELECT poi.name,poi.city,ST_AsTEXT(poi.the_geog), ST_Distance(road.the_geog, poi.the_geog)/1000.0 AS distance_km,ST_AsTEXT(road.the_geog::geometry) FROM road, poi WHERE road.id = 123 AND ST_DWithin(road.the_geog, poi.the_geog, 10000.0) ORDER BY ST_LineLocatePoint(road.the_geog::geometry, poi.the_geog::geometry), ST_Distance(road.the_geog, poi.the_geog);
如果线串是由这代表:[85.280194 23.296728,85.281572 23.297479]
,我要像结果:
poi vertex distance_km
85.280194 23.296728 85.280001 23.299876 3
85.289673 23.291987 85.281572 23.297479 5
答
U SE ST_DumpPoints()提取线的顶点:
SELECT id,the_geog, (ST_dumppoints(road.the_geog::GEOMETRY)).geom AS vertex FROM road;
你要投地理学几何,否则st_dumppoints不起作用。
你的整个查询可能看起来像这样:
SELECT road.id AS road_id,
ST_Astext(poi.the_geog) AS poi,
ST_astext(road.vertex) AS vertex,
ST_AsText(road.the_geog) AS line,
ST_Distance(vertex, poi.the_geog) AS distance
FROM (SELECT id,the_geog, (ST_dumppoints(road.the_geog::GEOMETRY)).geom AS vertex
FROM road
) as road,
poi
WHERE road.id = 123
ORDER by ST_Distance(vertex, poi.the_geog);