SELECT "MMSI","TS","LAT","LON","SHAPE" FROM AIS_DEMO.AIS_2017;
SELECT "MMSI" FROM "AIS_2017", "PARK_BOUNDARIES"
WHERE "AIS_2017"."SHAPE".ST_Within("PARK_BOUNDARIES"."SHAPE") = 1;
SELECT "MMSI", "SHAPE", "TS",
CAST("SHAPE".ST_DISTANCE(LAG("SHAPE", 1) OVER(PARTITION BY "MMSI" ORDER BY "TS"), 'meter') AS DECIMAL(10,2)) AS "DELTA_S",
SECONDS_BETWEEN(LAG("TS", 1) OVER(PARTITION BY "MMSI" ORDER BY "TS"), "TS") AS "DELTA_T",
ST_MAKELINE(LAG("SHAPE", 1) OVER(PARTITION BY "MMSI" ORDER BY "TS"), "SHAPE") AS "LINE",
RANK() OVER(PARTITION BY "MMSI" ORDER BY "TS") AS "FWD_RANK",
RANK() OVER(PARTITION BY "MMSI" ORDER BY "TS" DESC) AS "BWD_RANK"
FROM "AIS_2017"
SELECT SUM("DELTA_S") OVER(PARTITION BY "MMSI" ORDER BY "TS" ASC) AS "TOTAL_DISTANCE",
SUM("DELTA_T") OVER(PARTITION BY "MMSI" ORDER BY "TS" ASC) AS "TOTAL_TIMESPAN",
"DELTA_S"/"DELTA_T" AS "SPEED_M/S", *
FROM "V_MOTION_STATS_1"
SELECT ST_CLUSTERID() AS "ID",
ST_CLUSTERCELL() AS "SHAPE",
COUNT(*) FROM "AIS_2017" AS "COUNT"
GROUP CLUSTER BY "SHAPE" USING HEXAGON Y CELLS 400;
INSERT INTO "ROUTE_NETWORK_VERTICES" ("ID", "HEXAGON", "CENTROID", "COUNT")
(SELECT ST_CLUSTERID() AS "ID",
ST_CLUSTERCELL() AS "HEXAGON",
ST_CLUSTERCELL().ST_CENTROID() AS "CENTROID",
COUNT(*) AS "COUNT"
FROM "AIS_2017" GROUP CLUSTER BY "SHAPE" USING HEXAGON Y CELLS 400);
--CARGO_TRANSIT_COST_FACTORで混み具合を追加
MERGE INTO "ROUTE_NETWORK_VERTICES" R USING (
SELECT R."ID",
1/MAX(C."COUNT") AS "CARGO_TRANSIT_COST_FACTOR" FROM "ROUTE_NETWORK_VERTICES" AS R
INNER JOIN "CLUSTER_CARGO"
ON "ROUTE_NETWORK_VERTICES"."CENTROID".ST_INTERSECTS("CLUSTER_CARGO"."SHAPE") = 1
GROUP BY R."ID") AS C
ON R."ID" = C."ID"
WHEN MATCHED THEN UPDATE SET R."CARGO_TRANSIT_COST_FACTOR" = C."CARGO_TRANSIT_COST_FACTOR";
INSERT INTO "ROUTE_NETWORK_EDGES"("SOURCE", "TARGET", "LINE", "LENGTH", "AVG_COUNT", "AVG_CARGO_TRANSIT_COST_FACTOR")
SELECT T1.ID AS "SOURCE",
T2.ID AS "TARGET",
ST_MAKELINE(T1."CENTROID", T2."CENTROID") AS "LINE",
CASE WHEN T1.ID != T2.ID THEN ST_MAKELINE(T1."CENTROID", T2."CENTROID").ST_LENGTH() ELSE 0 END AS "LENGTH",
(T1."COUNT" + T2."COUNT")/2 AS "AVG_COUNT",
(T1."CARGO_TRANSIT_COST_FACTOR" + T2."CARGO_TRANSIT_COST_FACTOR")/2 AS "AVG_CARGO_TRANSIT_COST_FACTOR"
FROM "ROUTE_NETWORK_VERTICES" AS T1, "ROUTE_NETWORK_VERTICES" AS T2
WHERE T1."CENTROID".ST_WITHINDISTANCE(T2."CENTROID", 1322, 'meter') = 1;
CREATE GRAPH WORKSPACE "ROUTE_NETWORK_GRAPH"
EDGE TABLE "ROUTE_NETWORK_EDGES"
SOURCE COLUMN "SOURCE"
TARGET COLUMN "TARGET"
KEY COLUMN "ID"
VERTEX TABLE "ROUTE_NETWORK_VERTICES"
KEY COLUMN "ID";
SELECT * FROM F_SHORTEST_PATH(29117, 28448, 0.4)
SELECT * FROM "ST_CUBE" WHERE "CLUSTER_ID" = 562;
DO BEGIN
-- 変数を設定
DECLARE lt_param0 TABLE("PARAM_NAME" VARCHAR (100), "INT_VALUE" INTEGER, "DOUBLE_VALUE" DOUBLE, "STRING_VALUE" VARCHAR (100));
:lt_param0.INSERT(( 'FUNCTION', NULL, NULL,'MAESM'), 1);
:lt_param0.INSERT(( 'THREAD_RATIO', NULL, 1.0, NULL), 2);
:lt_param0.INSERT(( 'FORECAST_NUM', 12, NULL, NULL), 3);
:lt_param0.INSERT(( 'MODELSELECTION', 1, NULL, NULL), 4);
:lt_param0.INSERT(( 'MAX_ITERATION', 500, NULL, NULL),5);
:lt_param0.INSERT(( 'MEASURE_NAME', NULL, NULL,'MAPE'),6);
-- 変数、データを定義
lt_param = SELECT DAT."GROUP_ID", P.* FROM :lt_param0 AS P CROSS JOIN (SELECT DISTINCT "CLUSTER_ID" AS "GROUP_ID" FROM "UES_DATA") AS DAT;
lt_data = SELECT "CLUSTER_ID", "ELEMENT_NUMBER", "OBSERVED_VALUE" FROM "UES_DATA";
-- Uniied Exponential Smoothing 時系列予測アルゴリズムを実行
CALL _SYS_AFL.PAL_UNIFIED_EXPONENTIALSMOOTHING(:lt_data, :lt_param, t_forecast, t_stats, f_errmsge, pl1, pl2);
--結果を保存
INSERT INTO "UES_FORECAST" ("CLUSTER_ID", "ELEMENT_NUMBER", "FORCASTED_VALUE", "PI1_LOWER", "PI1_UPPER", "PI2_LOWER", "PI2_UPPER")
SELECT "GROUP_ID" AS "CLUSTER_ID",
"TIMESTAMP" AS "ELEMENT_NUMBER",
"VALUE",
"PI1_LOWER",
"PI1_UPPER",
"PI2_LOWER",
"PI2_UPPER"
FROM :t_forecast;
-- 統計情報
SELECT "GROUP_ID", "STAT_NAME", "STAT_VALUE" FROM :t_stats WHERE "STAT_NAME" IN ('FORECAST_MODEL_NAME', 'MSE');
END;
CALL ESH_CONFIG('
[{"uri": "~/$metadata/EntitySets", "method": "PUT",
"content":{
"Fullname": "AIS/V_ESH_VESSELS",
"EntityType": {
"@Search.searchable": true,
"@EnterpriseSearch.enabled": true,
"@EnterpriseSearchHana.passThroughAllAnnotations":true,
"@EnterpriseSearchHana.processing.ignoreInvalidSearchOptions": true,
"Properties": [
{
"Name": "MMSI",
"@UI.identification": { "position": 1 },
"@EnterpriseSearch.key": true,
"@Search.defaultSearchElement": true
},{
"Name": "VESSELNAME",
"@UI.identification": { "position": 2 },
"@EnterpriseSearch.highlighted.enabled": true,
"@Search.defaultSearchElement": true,
"@EnterpriseSearch.defaultValueSuggestElement": true,
"@Search.fuzzinessThreshold": 0.8,
"@EnterpriseSearch.searchOptions": "similarCalculationMode=substringsearch"
},{
"Name": "VESSELTYPE",
"@UI.identification": { "position": 3 },
"@EnterpriseSearch.highlighted.enabled": false,
"@Search.defaultSearchElement": true,
"@EnterpriseSearch.filteringFacet.default": true,
"@EnterpriseSearch.filteringFacet.displayPosition": 1,
"@Search.fuzzinessThreshold": 0.8,
"@EnterpriseSearch.searchOptions": "similarCalculationMode=substringsearch"
}, ...]}}}]',?);
CALL SYS.ESH_SEARCH('[ "/v20411/AIS_DEMO/$all?$filter=Search.search(query=''ann'')" ]', ?);
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
12 | |
10 | |
9 | |
7 | |
7 | |
7 | |
6 | |
6 | |
5 | |
4 |