一、數(shù)據(jù)

https://lbs.amap.com/api/javascript-api/example/marker/adaptive-show-multiple-markers

從高德開(kāi)放API中獲取一些路口、路段的經(jīng)緯度坐標(biāo)，經(jīng)偏移轉(zhuǎn)換坐標(biāo)系等處理后得到以下結(jié)果：

1.路口表

DROP TABLE IF EXISTS "public"."hangzhou_node_data";
CREATE TABLE "public"."hangzhou_node_data" (
"id" varchar(50) COLLATE "default" NOT NULL,
"node_name" varchar(50) COLLATE "default",
"geom" "public"."geometry"
)
WITH (OIDS=FALSE)
;
INSERT INTO "public"."hangzhou_node_data" VALUES ('2726e53b-1e23-4a93-a972-5f8b004adc7c', '平海路-東坡路', '0101000020E6100000594390C66C0A5E404DB52E8502413E40');

2.路段表

DROP TABLE IF EXISTS "public"."hangzhou_roadsect_data";
CREATE TABLE "public"."hangzhou_roadsect_data" (
"id" varchar(50) COLLATE "default" NOT NULL,
"up_node" varchar(50) COLLATE "default" NOT NULL,
"down_node" varchar(50) COLLATE "default" NOT NULL,
"geom" "public"."geometry"
)
WITH (OIDS=FALSE)
;
INSERT INTO "public"."hangzhou_roadsect_data" VALUES ('037ab2e8-e738-4a05-ae1d-36d84f6bb308', '平海路-東坡路', '解放路-南山路', '0102000020E610000002000000151AD0736B0A5E4035F3AC3E21403E401EA7BC4E6B0A5E40FB89D7DCFC403E40');
INSERT INTO "public"."hangzhou_roadsect_data" VALUES ('0bc08a18-457b-4ddc-9b5c-0d6bde8c0c3a', '解放路-南山路', '平海路-東坡路', '0102000020E610000002000000EFE34B406E0A5E405A54C6E2FC403E40E2565F656E0A5E40D4CA9B4421403E40');
INSERT INTO "public"."hangzhou_roadsect_data" VALUES ('d612579c-a8b1-45e4-8543-a56075ae03fe', '平海路-東坡路', '平海路-延安路', '0102000020E610000002000000B5BFFD4A7F0A5E400ACDD7A600413E4050A8977A6E0A5E40888E93B9FD403E40');
INSERT INTO "public"."hangzhou_roadsect_data" VALUES ('fc595cee-c408-409c-b24d-e24030aaa927', '平海路-延安路', '平海路-東坡路', '0102000020E6100000020000008341AD546E0A5E408889FCE107413E40E75813257F0A5E408A7A40CF0A413E40');

二、PostGIS函數(shù)實(shí)踐

1.ST_Centroid 找中點(diǎn)

SELECT
    hrd.up_node,
    st_astext (hnd1.geom) up_coor,
    hrd.down_node,
    st_astext (hnd2.geom) down_coor,
    st_astext (hrd.geom) roadsect_coor,
    st_astext (ST_Centroid(hrd.geom)) center_coor
FROM
    hangzhou_roadsect_data hrd
LEFT JOIN hangzhou_node_data hnd1 ON hnd1.node_name = hrd.up_node
LEFT JOIN hangzhou_node_data hnd2 ON hnd2.node_name = hrd.down_node

結(jié)果：

平海路-東坡路 POINT(120.162889138109 30.253944705871) 平海路-延安路 POINT(120.164115046587 30.2539980542926)    LINESTRING(120.164019344141 30.2539161946161,120.162993095496 30.2538715348478) POINT(120.163506219818 30.253893864732)

展示：
2為1、3中點(diǎn)

中點(diǎn).png

2.ST_ClosestPoint 線上離某點(diǎn)最近的點(diǎn)

SELECT
    hnd.node_name,
    st_astext (hnd.geom) node_coor,
    st_astext (
        '0102000020E61000000200000083DD1858830A5E408F1A04365D3F3E4011F22A1F690A5E408AA7ED36673F3E40'
    ) line_coor,
    st_astext (
        ST_ClosestPoint (
            '0102000020E61000000200000083DD1858830A5E408F1A04365D3F3E4011F22A1F690A5E408AA7ED36673F3E40',
            hnd.geom
        )
    ) center
FROM
    hangzhou_node_data hnd
WHERE
    hnd."id" = 'cf796fd3-c6df-4bd3-b560-d56b360bcb36'

結(jié)果：

西湖大道-勞動(dòng)路    POINT(120.162552724743 30.2459615269731)    LINESTRING(120.164266609461 30.2475160369891,120.162666122378 30.2476686792575) POINT(120.162715094481 30.2476640086713)

展示：
3、2為路段，1為目標(biāo)路口，4為最近目標(biāo)

某路段上距離某路口最近的坐標(biāo).png

3.ST_Distance 兩點(diǎn)間距離

SELECT
    hrd.up_node,
    hrd.down_node,
    round(
        ST_Distance (
            ST_Transform (hnd1.geom, 3857),
            ST_Transform (hnd2.geom, 3857)
        ) :: NUMERIC,
        2
    )
FROM
    hangzhou_roadsect_data hrd
LEFT JOIN hangzhou_node_data hnd1 ON hnd1.node_name = hrd.up_node
LEFT JOIN hangzhou_node_data hnd2 ON hnd2.node_name = hrd.down_node

結(jié)果：

西湖大道-延安路    開(kāi)元路-延安路 201.89
勞動(dòng)路-開(kāi)元路 開(kāi)元路-延安路 201.47
南山路-開(kāi)元路 勞動(dòng)路-開(kāi)元路 214.42
南山路-開(kāi)元路 西湖大道-南山路    237.18
西湖大道-南山路    南山路-開(kāi)元路 237.18

展示：
以西湖大道-延安路到開(kāi)元路-延安路為例測(cè)量結(jié)果為例：

兩個(gè)路口間的距離.png