GEOMAP()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SQL(`SELECT time, lat, lon FROM TRIP
     WHERE name = 'firenze' ORDER BY time`)
SCRIPT({
    // 時刻をエポックナノ秒に変換してDate（JavaScript）を生成
    var timestamp = new Date($.values[0].unixNano()/1000000); 
    // coordinate [lat, lon]
    var coord = [$.values[1], $.values[2]]; 
    $.yield({
        type:"circle",
        coordinates: coord,
        properties: {
            radius: 15,
            tooltip: {
                content: ""+timestamp
            }
        }
    });
})
GEOMAP()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

// CSV形式： TIME, LAT, LON
CSV(file("https://docs.machbase.com/assets/example/data-trajectory-firenze.csv"))

DROP(1) // ヘッダーを除外

SCRIPT({
    var timestamp = $.values[0];
    var coord = [
        parseFloat($.values[1]), 
        parseFloat($.values[2])
    ];
    $.yield({
        type:"circle",
        coordinates: coord,
        properties: {
            radius: 15,
            tooltip: {
                content: timestamp
            }
        }
    });
})

GEOMAP()

 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

SQL(`SELECT time, lat, lon FROM TRIP
     WHERE name = 'firenze' ORDER BY time`)
// 距離と速度を計算
SCRIPT({
    var EarthRadius = 6378137.0; // meters
    function degreesToRadians(d) { return d * Math.PI / 180; }
    function distance(p1, p2) {  // ハバーサインの公式による距離
        lat1 = degreesToRadians(p1[0]);
        lon1 = degreesToRadians(p1[1]);
        lat2 = degreesToRadians(p2[0]);
        lon2 = degreesToRadians(p2[1]);
        diffLat = lat2 - lat1;
        diffLon = lon2 - lon1;
        a = Math.pow(Math.sin(diffLat/2), 2) + Math.cos(lat1)*Math.cos(lat2)*Math.pow(Math.sin(diffLon/2), 2);
        c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
        return c * EarthRadius;
    }
    var prevLoc, prevTs, dist;
},{
    var ts = $.values[0].unix(); // Unixエポック秒
    var coord = [$.values[1], $.values[2]];
    dist = prevLoc === undefined ? 0 : distance(prevLoc, coord);
    speed = prevTs === undefined ? 0 : dist*3.600 / (ts - prevTs);
    prevLoc = coord;
    prevTs = ts;
    $.yield({
        type:"circleMarker",
        coordinates: coord,
        properties: {
            radius: 4,
            tooltip: {
                content: "speed: "+speed.toFixed(0)+" KM/H<br/>"+
                         "dist: "+dist.toFixed(0)+" m",
            }
        }
    });
})
GEOMAP()

 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

// CSV形式： TIME("23-04-21 16:53:21:568000"), LAT, LON
CSV(file("https://docs.machbase.com/assets/example/data-trajectory-firenze.csv"))

// 先頭行のヘッダーを除外
DROP(1) 

// 文字列から時刻と座標を解析
SCRIPT({
    function parseTime(str) { // parse '23-04-21 16:53:21'
        y = str.substr(0,2)+2000;
        m = str.substr(3,2) - 1;
        d = str.substr(6,2);
        hours = str.substr(9, 2);
        mins = str.substr(12,2);
        secs = str.substr(15, 2);
        var D = new Date(y, m, d,hours, mins, secs);
        return (D.getFullYear() == y && D.getMonth() == m && D.getDate() == d) ? D : 'invalid date';
    }
},{ 
    var ts = parseTime($.values[0]).getTime()/1000; // エポック秒
    var lat = parseFloat($.values[1]);
    var lon = parseFloat($.values[2]);
    $.yield(ts, lat, lon);
})

// 距離と速度を計算
SCRIPT({
    var EarthRadius = 6378137.0; // meters
    function degreesToRadians(d) { return d * Math.PI / 180; }
    function distance(p1, p2) {  // ハバーサインの公式による距離
        lat1 = degreesToRadians(p1[0]);
        lon1 = degreesToRadians(p1[1]);
        lat2 = degreesToRadians(p2[0]);
        lon2 = degreesToRadians(p2[1]);
        diffLat = lat2 - lat1;
        diffLon = lon2 - lon1;
        a = Math.pow(Math.sin(diffLat/2), 2) + Math.cos(lat1)*Math.cos(lat2)*Math.pow(Math.sin(diffLon/2), 2);
        c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
        return c * EarthRadius;
    }
    var prevLoc, prevTs, dist;
},{
    var ts = $.values[0];
    var coord = [$.values[1], $.values[2]];
    dist = prevLoc === undefined ? 0 : distance(prevLoc, coord);
    speed = prevTs === undefined ? 0 : dist*3.600 / (ts - prevTs);
    prevLoc = coord;
    prevTs = ts;
    $.yield({
        type:"circleMarker",
        coordinates: coord,
        properties: {
            radius: 4,
            tooltip: {
                content: "speed: "+speed.toFixed(0)+" KM/H<br/>"+
                         "dist: "+dist.toFixed(0)+" m",
            }
        }
    });
})
GEOMAP()