PostGIS轨迹分析

Gps轨迹分析

应用场景主要是监管日常环卫、浇水车辆停留以及道路覆盖相关业务。

停留点分析

统计同一里程的最大最小时间 根据时间差来判断停留

SELECT
	*  FROM
	(
	SELECT
		plate_no,
		mileage,
		mintime,
		maxtime,
		round( date_part( 'epoch', maxtime - mintime ) / 60 ) stayminute,
		st_astext ( st_centroid ( extent ) ) 
	FROM
		(
		SELECT
			plate_no,
			mileage,
			MAX ( device_time ) maxtime,
			MIN ( device_time ) mintime,
			st_extent ( sp_geometry ) extent 
		FROM
			jt808."location" 
		WHERE
			device_time > '2023-02-14 00:00:00' 
			AND device_time < '2023-02-15 00:00:00' 
			AND speed = 0 
		GROUP BY
			plate_no,
			mileage 
		) A 
	) aa 
WHERE
	stayminute > 15 

统计轨迹

SQL SELECT
st_asgeojson (
ST_MakeLine ( ARRAY ( SELECT st_point ( longitude, latitude ) FROM gps.gpsinfo WHERE sendtime > CURRENT_DATE AND plateno = 'xxxxx' ORDER BY sendtime ) ) 
);

移除轨迹重复点

SELECT
	st_asgeojson ( ST_RemoveRepeatedPoints ( st_makeline ( sp_geometry ) ) ) 
FROM
	gps.gpsinfo 
WHERE
	createdate > '2022-05-08 00:00:00' 
	AND createdate < '2022-05-09 00:00:00' 
	AND plateno = 'xxxxx'

道路覆盖率思路

1、道路采用单线
2、通过gps定位拉到所在道路上的点 
3、ST_LineLocatePoint计算所在线上的位置  0-1
4、根据车在该道路上的最小和最大区间来计算对应覆盖范围    
ST_LineSubstring 获取对应区域的线路
ST_LocateBetween  根据开始和结束比例返回线路
ST_InterpolatePoint  获取点在线上的距离

--创建表
CREATE TABLE gps_cover (
	wgmc VARCHAR ( 100 ),
	sp_geomety geometry,
	plateno VARCHAR ( 100 ),
	minlocate VARCHAR ( 100 ),
	maxlocate VARCHAR ( 100 ),
	gpsline geometry 
) 

--创建索引
CREATE INDEX gps_cover_sp_geomety_idx ON gps_cover USING GIST ( sp_geomety );

CREATE INDEX gps_cover_gpsline_idx ON gps_cover USING GIST ( gpsline );
--清空表
TRUNCATE TABLE gps_cover;
--插入数据
INSERT INTO gps_cover 
SELECT
re.wgmc,
re.geom AS sp_geomety,
re.plateno,
re.minlocate,
re.maxlocate,
st_linesubstring ( re.geom, re.minlocate, re.maxlocate ) AS gpsline 
FROM
	(
	SELECT
		aaa.wgmc,
		aaa.geom,
		aaa.plateno,
		MIN ( aaa.locate ) AS minlocate,
		MAX ( aaa.locate ) AS maxlocate,
		count(*) pointnum
	FROM
		(
		SELECT
			aa.locate,
			aa.longitude,
			aa.latitude,
			aa.plateno,
			aa.createdate,
			aa.geom,
			aa.qxmc,
			aa.wgmc 
		FROM
			(
			SELECT
				st_linelocatepoint ( b.geom, A.sp_geometry ) AS locate,
				st_distance ( A.sp_geometry, b.geom ) AS locatedistance,
				ST_NumGeometries(b.geom) num,
				A.longitude,
				A.latitude,
				A.plateno,
				A.createdate,
				st_setsrid ( b.geom, 4326 ) AS geom,
				b.qxmc,
				b.wgmc 
			FROM
				gps.gpsinfo A,
				gps.gaoxi_road_merge b 
			WHERE
 				A.createdate > '2022-05-08 00:00:00' 
				AND A.createdate < '2022-05-09 00:00:00' 
				AND plateno = 'XXXXX' 
			) aa 
		WHERE
			aa.locate <> 0 :: DOUBLE PRECISION 
			AND aa.locate <> 1 :: DOUBLE PRECISION 
			AND locatedistance < 0.0001 
				and num=1
		) aaa 
		 
		GROUP BY
		aaa.wgmc,
		aaa.geom,
		aaa.plateno 
		) re  where pointnum>10
		
		--创建表
		 CREATE TABLE gps_cover_locate (
		locateline NUMERIC ( 8, 7 ),
		longitude "numeric" ( 10, 6 ),
		latitude "numeric" ( 10, 6 ),
		plateno VARCHAR ( 100 ),
		createdate DATE,
		sp_geometry geometry,
		qxmc VARCHAR ( 100 ),
		wgmc VARCHAR ( 100 ) 
	) 
	
	--创建索引
	CREATE INDEX gps_cover_locate_sp_geometry_idx ON gps_cover_locate USING GIST ( sp_geometry );
	--清空表
	TRUNCATE TABLE gps_cover_locate;
	
	--插入数据
INSERT INTO gps_cover_locate ( locateline, longitude, latitude, plateno, createdate, qxmc, wgmc, sp_geometry )
 SELECT
* 
FROM
	(
	SELECT
		aa.locateline,
		aa.longitude,
		aa.latitude,
		aa.plateno,
		aa.createdate,
		aa.qxmc,
		aa.wgmc,
		aa.sp_geometry 
	FROM
		(
		SELECT
			st_linelocatepoint ( b.geom, A.sp_geometry ) AS locateline,
			st_distance ( A.sp_geometry, b.geom ) AS locatedistance,
			ST_NumGeometries(b.geom) num,
			A.longitude,
			A.latitude,
			A.plateno,
			A.createdate,
			A.sp_geometry,
			b.qxmc,
			b.wgmc 
		FROM
			gps.gpsinfo A,
			gps.gaoxi_road_mid_single b 
		WHERE
			A.createdate > '2022-05-08 00:00:00' 
				AND A.createdate < '2022-05-09 00:00:00' 
				AND plateno = 'XXXX' 
		) aa 
	WHERE
		aa.locateline <> 0 
		and num=1
		AND aa.locateline <> 1 
	AND aa.locatedistance < 0.0001
	) aaa