Coverage for aisdb/database/sql_query_strings.py: 85%

1import warnings 

2 

3from aisdb.gis import dt_2_epoch, shiftcoord 

4 

5 

6# callback functions 

7def in_bbox(*, alias, xmin, xmax, ymin, ymax, **_): 

8 ''' SQL callback restricting vessels in bounding box region 

9 

10 args: 

11 alias (string) 

12 the 'alias' in a 'WITH tablename AS alias ...' SQL statement 

13 xmin (float) 

14 minimum longitude 

15 xmax (float) 

16 maximum longitude 

17 ymin (float) 

18 minimum latitude 

19 ymax (float) 

20 maximum latitude 

21 

22 returns: 

23 SQL code (string) 

24 ''' 

25 if not -180 <= xmin <= 180: 

26 warnings.warn(f'got {xmin}') 

27 xmin = shiftcoord([xmin])[0] 

28 if not -180 <= xmax <= 180: 

29 warnings.warn(f'got {xmax}') 

30 xmax = shiftcoord([xmax])[0] 

31 if not -90 <= ymin <= 90: 

32 warnings.warn(f'got {ymin=}') 

33 if not -90 <= ymax <= 90: 

34 warnings.warn(f'got {ymax=}') 

35 assert ymin < ymax, f'got {ymin=} {ymax=}' 

36 

37 if xmin == -180 and xmax == 180: 37 ↛ 38line 37 didn't jump to line 38, because the condition on line 37 was never true

38 return f'''({alias}.longitude >= {xmin} AND {alias}.longitude <= {xmax}) AND 

39 {alias}.latitude >= {ymin} AND 

40 {alias}.latitude <= {ymax}''' 

41 

42 #if xmin < xmax: 

43 assert xmin < xmax 

44 #if xmin < -180 and xmax > 180: 

45 # raise ValueError(f'xmin, xmax are out of bounds! {xmin=} < -180,{xmax=} > 180') 

46 #elif -180 <= xmin <= 180 and -180 <= xmax <= 180: 

47 s = f'''{alias}.longitude >= {xmin} AND 

48 {alias}.longitude <= {xmax} AND ''' 

49 """ 

50 elif xmin < -180: 

51 s = f'''( 

52 ({alias}.longitude >= -180 AND {alias}.longitude <= {xmax}) OR 

53 ({alias}.longitude <= 180 AND {alias}.longitude >= {shiftcoord([xmin])[0]}) 

54) AND ''' 

55 elif xmax > 180: 

56 s = f'''( 

57 ({alias}.longitude <= 180 AND {alias}.longitude >= {shiftcoord([xmax])[0]}) OR 

58 ({alias}.longitude >= -180 AND {alias}.longitude <= {xmin}) 

59) AND ''' 

60 else: 

61 raise ValueError( 

62 f'Error creating SQL query in longitude bounds {xmin=} {xmax=}' 

63 ) 

64 """ 

65 

66 query_args = f'''{s} 

67 {alias}.latitude >= {ymin} AND 

68 {alias}.latitude <= {ymax}''' 

69 

70 return query_args 

71 

72 #else: 

73 ''' 

74 if xmin < -180: 

75 x0 = shiftcoord([xmin])[0] 

76 else: 

77 x0 = xmin 

78 if xmax > 180: 

79 x1 = shiftcoord([xmax])[0] 

80 else: 

81 x1 = xmax 

82 ''' 

83 

84 #assert x0 <= x1 

85 

86 #return f'''({alias}.longitude >= {xmin} OR {alias}.longitude <= {xmax}) AND {alias}.latitude >= {ymin} AND {alias}.latitude <= {ymax}''' 

87 

88 

89def in_timerange(*, alias, start, end, **_): 

90 ''' SQL callback restricting vessels in temporal range. 

91 

92 args: 

93 alias (string) 

94 the 'alias' in a 'WITH tablename AS alias ...' SQL statement 

95 start (datetime) 

96 end (datetime) 

97 

98 returns: 

99 SQL code (string) 

100 ''' 

101 return f'''{alias}.time >= {dt_2_epoch(start)} AND 

102 {alias}.time <= {dt_2_epoch(end)}''' 

103 

104 

105def has_mmsi(*, alias, mmsi, **_): 

106 ''' SQL callback selecting a single vessel identifier 

107 

108 args: 

109 alias (string) 

110 the 'alias' in a 'WITH tablename AS alias ...' SQL statement 

111 mmsi (int) 

112 vessel identifier 

113 

114 returns: 

115 SQL code (string) 

116 ''' 

117 return f'''CAST({alias}.mmsi AS INT) = {mmsi}''' 

118 

119 

120def in_mmsi(*, alias, mmsis, **_): 

121 ''' SQL callback selecting multiple vessel identifiers 

122 

123 args: 

124 alias (string) 

125 the 'alias' in a 'WITH tablename AS alias ...' SQL statement 

126 mmsis (tuple) 

127 tuple of vessel identifiers (int) 

128 

129 returns: 

130 SQL code (string) 

131 ''' 

132 return f'''{alias}.mmsi IN 

133 ({", ".join(map(str, mmsis))})''' 

134 

135 

136def valid_mmsi(*, alias='m123', **_): 

137 ''' SQL callback selecting all vessel identifiers within the valid vessel 

138 mmsi range, e.g. (201000000, 776000000) 

139 

140 args: 

141 alias (string) 

142 the 'alias' in a 'WITH tablename AS alias ...' SQL statement 

143 

144 returns: 

145 SQL code (string) 

146 ''' 

147 return f'''{alias}.mmsi >= 201000000 AND 

148 {alias}.mmsi < 776000000 '''