-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathtest_detectstops.sql
153 lines (140 loc) · 5.55 KB
/
test_detectstops.sql
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
CREATE OR REPLACE FUNCTION test.two_stops_with_gap() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 0 1, 5 5 1.5, 2 2 2, 2 2 3)'),1,1) stop )
SELECT Count(stop) FROM temp INTO n0;
IF n0 = 2
THEN RAISE INFO 'PASSED - Two stops with gap';
ELSE RAISE INFO 'FAILED - Two stops with gap';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.two_stops() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 0 1, 2 2 2, 2 2 3)'),1,1) stop )
SELECT Count(stop) FROM temp INTO n0;
IF n0 = 2
THEN RAISE INFO 'PASSED - Two stops';
ELSE RAISE INFO 'FAILED - Two stops';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.too_short() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 1 1 1, 2 2 2, 9 9 3)'),2,3) stop )
SELECT Count(stop) FROM temp INTO n0;
IF n0 = 0
THEN RAISE INFO 'PASSED - Too short';
ELSE RAISE INFO 'FAILED - Too short';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.stop_at_end() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 2 1, 0 2 2, 0 2.1 3)'),1,1) stop )
SELECT ST_M(ST_StartPoint((stop).geom)), ST_NPoints((stop).geom) FROM temp INTO t0, n0;
IF t0 = 1 AND n0 = 3
THEN RAISE INFO 'PASSED - Stop at the end of the trajectory';
ELSE RAISE INFO 'FAILED - Stop at the end of the trajectory';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.stop_at_beginning() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 0 1, 0.1 0.1 2, 2 2 3)'),1,1) stop )
SELECT ST_M(ST_StartPoint((stop).geom)), ST_NPoints((stop).geom) FROM temp INTO t0, n0;
IF t0 = 0 AND n0 = 3
THEN RAISE INFO 'PASSED - Stop at the beginning of the trajectory';
ELSE RAISE INFO 'FAILED - Stop at the beginning of the trajectory';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.remove_pts_from_stop_beginning() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
-- while the distance between (0 0 0) and (0 1 1) would qualify as a stop, it doesn't last long enough
-- therefore (0 0 0) has to be removed in order to detect the stop from (0 1 1) to (0 1.2 3)
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 1 1, 0 1.1 2, 0 1.2 3)'),1,2) stop )
SELECT ST_M(ST_StartPoint((stop).geom)), ST_NPoints((stop).geom) FROM temp INTO t0, n0;
IF t0 = 1 AND n0 = 3
THEN RAISE INFO 'PASSED - Remove point from beginning of stop';
ELSE RAISE INFO 'FAILED - Remove point from beginning of stop';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.no_stops() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 1 1 1, 2 2 2, 3 3 3)'),1,1) stop )
SELECT Count(stop) FROM temp INTO n0;
IF n0 = 0
THEN RAISE INFO 'PASSED - No stops';
ELSE RAISE INFO 'FAILED - No stops';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.move_stop_start() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
-- while the distance between (0 0 0) and (0 1 1) would qualify as a stop, it doesn't last long enough
-- therefore (0 0 0) has to be removed in order to detect the stop from (0 1 1) to (0 1.2 3)
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 1 1, 0 1.1 2, 0 1.2 3)'),1,2) stop )
SELECT ST_M(ST_StartPoint((stop).geom)), ST_NPoints((stop).geom) FROM temp INTO t0, n0;
IF t0 = 1 AND n0 = 3
THEN RAISE INFO 'PASSED - Move start of stop';
ELSE RAISE INFO 'FAILED - Move start of stop';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.move_stop_beginning() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
-- while the distance between (0 0 0) and (0 1 1) would qualify as a stop, it doesn't last long enough
-- therefore (0 0 0) has to be removed in order to detect the stop from (0 1 1) to (0 1.2 3)
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 1 1, 0 1.1 2, 0 1.2 3)'),1,2) stop )
SELECT ST_M(ST_StartPoint((stop).geom)), ST_NPoints((stop).geom) FROM temp INTO t0, n0;
IF t0 = 1 AND n0 = 3
THEN RAISE INFO 'PASSED - Move start of stop';
ELSE RAISE INFO 'FAILED - Move start of stop';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.long_stop_segment() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
DECLARE t0 integer; n0 integer;
BEGIN
WITH temp AS ( SELECT AG_DetectStops(ST_GeometryFromText('LinestringM(0 0 0, 0 1 1, 0 0 2, 0 1 3, 0 0 4, 5 5 5)'),1,1) stop )
SELECT ST_M(ST_StartPoint((stop).geom)), ST_NPoints((stop).geom) FROM temp INTO t0, n0;
IF t0 = 0 AND n0 = 5
THEN RAISE INFO 'PASSED - Long distance stop segment';
ELSE RAISE INFO 'FAILED - Long distance stop segment';
END IF;
END;
$BODY$;
CREATE OR REPLACE FUNCTION test.detectstops() RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
PERFORM test.stop_at_beginning();
PERFORM test.stop_at_end();
PERFORM test.move_stop_beginning();
PERFORM test.two_stops();
PERFORM test.two_stops_with_gap();
PERFORM test.no_stops();
PERFORM test.too_short();
PERFORM test.long_stop_segment();
END;
$BODY$;
SELECT test.detectstops();