postgreSql使用postgis实现空间聚簇_postgis 聚合

postgreSql使用postgis实现空间聚簇

一.如何获取聚簇

/* 
使用ST_ClusterDBSCAN函数,ST_ClusterDBSCAN是窗口函数用来对数据进行聚簇
第一个参数是字段,第二个参数是距离,他是用的是度,这里写0.000179大约就是20米,minpoints表示多少个聚合才算是一个簇,比如两台车就算是聚集,那就设置为2
*/
CREATE TEMP TABLE temp_vehicleGatherInfoByAreNumber as
 SELECT *, ST_ClusterDBSCAN(pt, eps := 0.000179, minpoints := 2) OVER () AS cluster_id FROM vehicle_last_location
 
    
1
2
3
4
5
6
7
8

二.如何获取聚簇中心点

/*ST_Centroid(ST_Collect(pt))*/
SELECT cluster_id,ST_Centroid(ST_Collect(pt)) as center_point,
    st_x(ST_Centroid(ST_Collect(pt))) as clusterCentLng,st_y(ST_Centroid(ST_Collect(pt))) as clusterCentLat FROM temp_vehicleGatherInfoByAreNumber GROUP BY cluster_id
1
2
3

三.如何获取一个包裹某个聚簇下的的最小多边形

/*ST_ConvexHull(ST_Collect(pt))*/
SELECT  ST_AsText(ST_ConvexHull(ST_Collect(pt))) AS cluster_pt, cluster_id,COUNT(1) FROM  temp_vehicleGatherInfoByAreNumber16867070588788
WHERE cluster_id is not null
GROUP BY cluster_id
1
2
3
4

效果

四.测试数据与表

建表sql

CREATE TABLE "public"."vehicle_last_location" (
  "id" int4 NOT NULL DEFAULT nextval('vehicle_last_location_id_seq'::regclass),
  "vehid" int4 NOT NULL,
  "pt" geometry(POINT, 4326),
  "pt_time" timestamp(0) NOT NULL,
  "speed" int2,
  "direction" int2,
  "biz_status" int2,
  "acc_on" int2,
  "create_on" timestamp(0),
  "are_number" varchar(32) COLLATE "pg_catalog"."default",
  CONSTRAINT "vehicle_last_location_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "vehicle_last_location_vehid_unique" UNIQUE ("vehid")
)
;

ALTER TABLE "public"."vehicle_last_location" 
  OWNER TO "gis";

COMMENT ON COLUMN "public"."vehicle_last_location"."id" IS '主键';

COMMENT ON COLUMN "public"."vehicle_last_location"."vehid" IS '车辆id';

COMMENT ON COLUMN "public"."vehicle_last_location"."pt" IS '定位';

COMMENT ON COLUMN "public"."vehicle_last_location"."pt_time" IS '定位时间';

COMMENT ON COLUMN "public"."vehicle_last_location"."speed" IS '速度';

COMMENT ON COLUMN "public"."vehicle_last_location"."direction" IS '方向';

COMMENT ON COLUMN "public"."vehicle_last_location"."biz_status" IS '营运状态。1. 载客 2.接单 3.空驶 4.停运';

COMMENT ON COLUMN "public"."vehicle_last_location"."are_number" IS '行政区划代码';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182342, 3382, '0101000020E6100000E0254BC0C2135B40CD58349D9DC43640', '2023-06-14 15:19:06', 0, 128, 3, 1, '2023-06-14 15:20:39', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182702, 4767, '0101000020E6100000D86CD25ABE135B406AFB57569AC43640', '2023-06-14 15:19:20', 0, 136, 3, 0, '2023-06-14 15:20:41', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182422, 4051, '0101000020E61000004E9CDCEF50175B4011018750A5CE3640', '2023-06-14 15:19:20', 0, 220, 3, 0, '2023-06-14 15:20:39', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186056, 236, '0101000020E6100000F8215CEEB9145B4072F1CA0882CE3640', '2023-06-14 15:20:30', 0, 92, 3, 1, '2023-06-14 15:21:14', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186293, 3737, '0101000020E61000008990CD68BE135B401E40AC249FC43640', '2023-06-14 15:20:14', 0, 0, 3, 0, '2023-06-14 15:21:15', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181810, 3771, '0101000020E6100000A30E1811B2145B40D66B8D1B91CE3640', '2023-06-14 15:19:49', 0, 128, 3, 1, '2023-06-14 15:21:36', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181314, 2235, '0101000020E610000052448655BC135B406E61F043B8C43640', '2023-06-14 15:20:33', 0, 146, 3, 0, '2023-06-14 15:21:34', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184881, 7278, '0101000020E61000007586BCF8B5145B403779B79F79CE3640', '2023-06-14 15:20:35', 0, 100, 1, 1, '2023-06-14 15:21:01', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184890, 7101, '0101000020E610000007E3BA3CB0145B4070DBE4DD7ECE3640', '2023-06-14 15:20:25', 13, 114, 3, 0, '2023-06-14 15:21:01', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181988, 1517, '0101000020E61000000E9905EDE8195B40F508480EEBD83640', '2023-06-14 15:20:27', 0, 162, 3, 1, '2023-06-14 15:21:37', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186052, 2590, '0101000020E6100000867AE73DBB135B40F8AA9509BFC43640', '2023-06-14 15:20:27', 0, 264, 3, 0, '2023-06-14 15:21:14', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184927, 7148, '0101000020E6100000A110018750175B40E1D90BF2A0CE3640', '2023-06-14 15:20:27', 0, 50, 3, 0, '2023-06-14 15:21:02', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (185000, 7238, '0101000020E610000052448655BC145B408A7615527ECE3640', '2023-06-14 15:20:27', 0, 184, 3, 1, '2023-06-14 15:21:02', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182384, 4257, '0101000020E610000063A404EABA135B4052CDBF70C1C43640', '2023-06-14 15:19:14', 0, 176, 3, 1, '2023-06-14 15:20:39', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (183017, 4937, '0101000020E6100000350708E6E8195B4037C30DF8FCD83640', '2023-06-14 15:19:18', 0, 10, 3, 1, '2023-06-14 15:20:42', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186326, 2721, '0101000020E6100000F3127664BF135B40993FB980AAC43640', '2023-06-14 15:20:36', 0, 0, 3, 1, '2023-06-14 15:21:15', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186214, 2301, '0101000020E61000001B6CD771E9195B40130A117008D93640', '2023-06-14 15:20:41', 0, 358, 3, 1, '2023-06-14 15:21:15', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186301, 1754, '0101000020E610000039B4C876BE135B40AEAE1D9FA3C43640', '2023-06-14 15:19:47', 0, 0, 3, 0, '2023-06-14 15:21:15', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186396, 1696, '0101000020E61000000A01E647E9195B401D45C34F09D93640', '2023-06-14 15:20:38', 0, 2, 3, 1, '2023-06-14 15:21:16', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186389, 2829, '0101000020E610000000DBD453BE135B402056924FA2C43640', '2023-06-14 15:20:35', 0, 234, 3, 0, '2023-06-14 15:21:16', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186400, 2264, '0101000020E6100000249C16BCE8195B4058662455EED83640', '2023-06-14 15:20:38', 0, 356, 3, 1, '2023-06-14 15:21:16', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186418, 2832, '0101000020E6100000D027F224E9195B4091C85193F3D83640', '2023-06-14 15:20:35', 0, 320, 3, 1, '2023-06-14 15:21:16', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (183065, 12477, '0101000020E61000007725DC3EBE135B40715AF0A2AFC43640', '2023-06-14 15:19:14', 0, 302, 3, 0, '2023-06-14 15:20:42', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186376, 2437, '0101000020E610000017E958B8BF135B404B345B8C94C43640', '2023-06-14 15:20:33', 0, 204, 3, 1, '2023-06-14 15:21:16', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (183181, 877, '0101000020E6100000D3AC5954B1145B4093B1C7317FCE3640', '2023-06-14 15:19:20', 0, 312, 3, 0, '2023-06-14 15:20:43', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186173, 3257, '0101000020E6100000DD0F6595BF135B4046425BCEA5C43640', '2023-06-14 15:05:40', 0, 0, 3, 1, '2023-06-14 15:06:18', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (185957, 78, '0101000020E61000002004F716E9195B40D93A4BA3DCD83640', '2023-06-14 15:20:30', 0, 252, 3, 1, '2023-06-14 15:21:13', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186002, 2004, '0101000020E6100000673C248FBA135B4099966BE4CDC43640', '2023-06-14 15:20:14', 0, 120, 3, 0, '2023-06-14 15:21:13', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186100, 705, '0101000020E610000099D36531B1145B4040B4697F7ACE3640', '2023-06-14 15:20:38', 0, 0, 3, 1, '2023-06-14 15:21:14', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (186285, 3483, '0101000020E6100000867AE73DBB145B406BAF188878CE3640', '2023-06-14 15:20:27', 0, 0, 3, 1, '2023-06-14 15:21:15', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181329, 1912, '0101000020E6100000E292E34EE9195B405B5F24B4E5D83640', '2023-06-14 15:20:33', 0, 114, 3, 1, '2023-06-14 15:21:34', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181406, 7, '0101000020E610000050C24CDBBF135B40599BD9C19BC43640', '2023-06-14 15:20:05', 0, 0, 3, 0, '2023-06-14 15:21:34', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181415, 3186, '0101000020E61000008C08D98CE6195B408A869F12EAD83640', '2023-06-14 15:20:19', 0, 4, 3, 1, '2023-06-14 15:21:34', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181561, 3176, '0101000020E61000000A70674CAE145B40BEAC763A7DCE3640', '2023-06-14 15:20:30', 8, 110, 3, 1, '2023-06-14 15:21:35', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181605, 3203, '0101000020E610000062105839B4145B4069B6182981CE3640', '2023-06-14 15:19:58', 0, 0, 3, 0, '2023-06-14 15:21:35', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181606, 3204, '0101000020E6100000E6B66AC4B9135B4060343EA6C8C43640', '2023-06-14 15:20:36', 1, 28, 3, 1, '2023-06-14 15:21:35', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181675, 1881, '0101000020E6100000D4BF11CAE8195B40D874173BF4D83640', '2023-06-14 15:20:14', 0, 338, 3, 1, '2023-06-14 15:21:36', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (181817, 3780, '0101000020E61000000E9905EDE8195B40D189171E0ED93640', '2023-06-14 15:20:33', 0, 358, 3, 1, '2023-06-14 15:21:36', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182076, 3453, '0101000020E6100000A32BFEDCBD135B400B0414FDB4C43640', '2023-06-14 15:20:36', 0, 170, 3, 0, '2023-06-14 15:21:38', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182162, 430, '0101000020E6100000F37EC9B3B8145B40242039AC83CE3640', '2023-06-14 15:20:33', 0, 0, 3, 1, '2023-06-14 15:21:39', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (182226, 2328, '0101000020E610000009168733BF135B409E4E9F0AA5C43640', '2023-06-14 15:20:33', 0, 262, 3, 0, '2023-06-14 15:21:40', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184145, 224, '0101000020E6100000D4D4B2B5BE135B40849688CA99C43640', '2023-06-14 15:20:38', 0, 140, 3, 0, '2023-06-14 15:20:57', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184304, 2139, '0101000020E6100000F168E388B5145B4072D4E43C76CE3640', '2023-06-14 15:20:29', 0, 88, 3, 0, '2023-06-14 15:20:58', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184353, 1655, '0101000020E6100000F594AFDEBB135B40EC76E3CAC6C43640', '2023-06-14 15:20:41', 0, 140, 3, 0, '2023-06-14 15:20:58', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184376, 5151, '0101000020E610000052448655BC145B40FE168A6174CE3640', '2023-06-14 15:20:29', 0, 278, 3, 0, '2023-06-14 15:20:58', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184383, 3221, '0101000020E61000000C864D77B1145B40CAFD0E4581CE3640', '2023-06-14 15:20:38', 24, 104, 1, 1, '2023-06-14 15:20:58', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184526, 2819, '0101000020E6100000AF5FB01BB6145B40D847A7AE7CCE3640', '2023-06-14 15:20:35', 0, 190, 3, 1, '2023-06-14 15:20:59', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184589, 448, '0101000020E610000065263E64B7145B403779B79F79CE3640', '2023-06-14 15:20:22', 5, 102, 3, 1, '2023-06-14 15:21:00', '450100');
        INSERT INTO "vehicle_last_location" ("id", "vehid", "pt", "pt_time", "speed", "direction", "biz_status", "acc_on", "create_on", "are_number") VALUES (184790, 4891, '0101000020E61000007BA01518B2145B40FE5056F98BCE3640', '2023-06-14 15:20:33', 0, 140, 3, 1, '2023-06-14 15:21:01', '450100');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50