このトピックでは、ST_Split関数について説明します。 ST_Split関数は、空間ジオメトリオブジェクトに基づいて軌道を複数のサブ軌道に分割します。
構文
trajectory[] ST_Split(trajectory traj, geometry geom, float8 radius_of_buffer);
trajectory[] ST_Split(trajectory traj, text config);
trajectory[] ST_Split(trajectory traj, int[] インデックス); パラメーター
| パラメーター | 説明 |
| traj | 分割する軌道。 |
| geom | 軌道を分割するために使用される空間ジオメトリオブジェクト。 PointおよびMultiPointジオメトリオブジェクトのみがサポートされます。 |
| radius_of_バッファ | Pointジオメトリオブジェクトに基づいてビルドされるバッファーの半径。 単位: メートル。 |
| config | 軌道を分割する方法を指定するルール。 |
| インデックス | 分割する軌道ポイントのインデックス。 インデックスは0から始まります。 |
説明
- 設定1:
ST_Split(trajectory traj, geometry geom, float8 radius_of_buffer)この関数は、指定されたジオメトリオブジェクトに基づいて軌道を分割し、サブ軌道の配列を返します。 マルチポイント空間ジオメトリオブジェクトに基づいて軌道が分割される場合、軌道は複数のセグメントに分割され得る。
- 設定2:
ST_Split(trajectory traj, text config)この関数は、指定されたルールに基づいて軌道を分割し、サブ軌道の配列を返します。 configパラメーターで指定できるルールは1つだけです。 次の表に、configパラメーターを使用して指定できるルールを示します。パラメーター データ型 説明 cut_point.max_point 正の整数 このルールは、サンプリングポイント間の数量間隔を指定します。 この関数は、指定した数量間隔に基づいて、軌道をサブ軌道に分割します。 cut_point.even_divide 正の整数 このルールは、軌道が分割される部分の数を指定します。 この関数は、指定された数のサブ軌道に軌道を均等に分割します。 軌道のエッジの数がルールによって指定された値よりも小さい場合、関数は軌道の各エッジをサブ軌道と見なします。 cut_edge.time_interval 正の時間範囲 このルールは、時間間隔を指定します。 この関数は、指定した時間間隔に基づいて、軌道をサブ軌道に分割します。 cut_edge.geohash 正の偶数 このルールはGeohashを指定します。 この関数は、ルールで指定したGeohashに基づいて軌道を分割し、各サブ軌道が1つのGeohashグリッドに含まれるようにします。 このルールを指定する場合は、軌道のデータが緯度と経度で表されていることを確認してください。 drop_edge.temporal_length 時間範囲 このルールは、時間間隔を指定します。 この関数は、指定された時間間隔の間の部分を軌道から削除します。 drop_edge.spatial_distance_2d 浮動小数点 このルールは、空間距離を指定する。 この関数は、空間距離で指定された部分を軌跡から削除します。 空間距離は、2次元ユークリッド距離である。 - 設定3:
ST_Split(trajectory traj, int[] indexes)この関数は、指定されたインデックスに基づいて軌道を分割します。 n個のインデックスが与えられた場合、関数は軌道をn-1の部分に分割します。 軌跡の始点および終点は、インデックスに含まれる必要はない。
図
- ジオメトリオブジェクトに基づいて軌道を分割します。
- configパラメーターにcut_point.max_pointまたはcut_point.even_divideルールを指定した場合、関数は指定されたサンプリングポイントに基づいて軌道を分割します。 次の図に示すように、関数はポイントBで軌道を2つのサブ軌道に分割します。configパラメーターにcut_edge.time_intervalまたはcut_edge.geohashルールを指定すると、関数は指定したエッジに基づいて軌道を分割します。 次の図に示すように、関数はポイントCで軌道を2つのサブ軌道に分割します。configパラメーターにdrop_edge.temporal_lengthまたはdrop_edge.spatial_distance_2dルールを指定すると、指定されたサンプリングポイント間の指定されたエッジが削除されます。 次の図に示すように、この関数はポイントAとポイントBの間のエッジを削除します。
例
テーブルtr_split_traj(id整数、traj軌道) を作成します。INSERT INTO tr_split_traj VALUES(3、ST_MakeTrajectory('STPOINT '::leaftype、st_geomfromtext('LINESTRING(99.027 29.7555、99.313 29.9975、99.852 30.0745、109.906 35.0795、104.879 35.1235、105.044 35.0245))' 、105.187 35.0685、4326、110.071 35.1675、110.192 35.0795、110.544、2010年-'''''''''90': '''''''''1401 '-'-'-'-'-'-'、'2010-01-01 15:10' 、'2010-01-01 15:20 '、'2010-01-01 15:30' 、'2010-01-01 15:40 '、'2010-01-01 15:50' 、'2010-01-01 16:00 '、'2010-01-01 16:10' 、'2010-01-01 16:20 '、'2010-01-01-16:20' 11、"attributes": {"type": "integer" 、"length": 2、"nullable" : true、"value": [120、130、140、150、160、170、180、190、200、210、220]}}} ');
tr_split_trajからsubtrajとしてid, unnest(st_split(traj, st_geomfromtext('MULTIPOINT(100 30,105 35,110 35)')), 23000) を選択します。
id | subtraj
---- + ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Fri Jan 01 14:30:00 2010","end_time":"Fri Jan 01 15:10:00 2010","spatial":"SRID=4326;LINESTRING(99.027 29.7555,99.313 29.9975,99.852 30.0745)" "[" Fri Jan 01 14:30:00 2010 "," Fri Jan 01 15:00:00 2010 "," Fri Jan 01 15:10:00 2010 "]," attributes ":{" leafcount ":3," velocity ":{" type ":" integer "," length ":2," nullable ":true," value ":[120,130,140]}}}}}}
3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"Fri Jan 01 15:10:00 2010","end_time":"Fri Jan 01 15:20:00 2010","spatial":"SRID=4326;LINESTRING(99.852 30.0745、104.879 35.0795)" 、"タイムライン" ["Fri Jan 01 15:10:00 2010","Fri Jan 01 15:20:00 2010"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[140,150]}}}}}
3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"Fri Jan 01 15:40:00 2010","end_time":"Fri Jan 01 15:50:00 2010","spatial":"SRID=4326;LINESTRING(105.187 35.0685、109.906 35.0795)" 、"タイムライン" ["Fri Jan 01 15:40:00 2010","Fri Jan 01 15:50:00 2010"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[170,180]}}}
3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Fri Jan 01 16:10:00 2010","end_time":"Fri Jan 01 16:30:00 2010","spatial":"SRID=4326;LINESTRING(110.192 35.0355,110.544 35.0245,111.017 34.8045)" 、"タイムライン" タイムライン "[" Fri Jan 01 16:10:00 2010 "," Fri Jan 01 16:20:00 2010 "," Fri Jan 01 16:30:00 2010 "]," attributes ":{" leafcount ":3," velocity ":{" type ":" integer "," length ":2," nullable ":true," value ":[200,210,220]}}}}}}
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからunnest(ST_split(a, '{"cut_point.max_point":4}')) を選択します。
unnest
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01 00:01:19","end_time":"2000-01-01 00:01 46:27","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144)-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113)" 、"timeline":["2000-01-01 00:01:19" 、"2000-01-01 00:12:36" 、"2000-01-01 00:23:53" 、"2000-01-01 00:35:10" 、"2000-01-01 00:01 46:27"]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01 00:46:27","end_time":"2000-01-01-01 01:31:35","spatial":"LINESTRING(-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733)-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193)" 、"timeline":["2000-01-01 00:46:27" 、"2000-01-01 00:57:44" 、"2000-01-01 01:09:01" 、"2000-01-01 01:20:18" 、"2000-01-01-01 1:31:35"]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01-01 01:31:35","end_time":"2000-01-01 02:16:44","spatial":"LINESTRING(-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011)-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287)","timeline":["2000-01-01 01:31:35","2000-01-01 01:42:53","2000-01-01 01:54:10","2000-01-01 02:05:27","2000-01-01 02:16:44"]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":5,"start_time":"2000-01-01 02:16:44","end_time":"2000-01-01-01 03:01:52","空間":"LINESTRING(-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052) 0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794)" 、"タイムライン":["2000-01-01 02:16:44" 、"2000-01-01 02:28:01" 、"2000-01-01 02:39:18" 、"2000-01-01 02:50:35" 、"2000-01-01-01 03:01:01:52"]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2000-01-01 03:01:52","end_time":"2000-01-01 03:24:26","空間":"LINESTRING(6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)") "、":"[" 2000-01-01 03:01:52 "," 2000-01-01 03:13:09 "," 2000-01-01 03:24:26 "]}}
(5行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"cut_point.max_point":10}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":10,\" start_time\":\" Sat Jan 01 00:01:19 2000\",\" end_time\":\" Sat Jan 01 01:42:53 2000\"、\" \"\" LINESTRING(-100 -100 -100、-88.8925775739675 -86.6512698383691 -92.3767832526937、-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781)\"、\" timeline\":[\" 土1月00:01:19 2000\"、\" 土1月00:01 00:12:36 2000\"、\" 土1月00:23:53 2000\"、\" 土1月00:35:10 2000\"、\" 土1月00:01 00:46:27 2000\"、\" 土1月00:01 00:57:44 2000\"、\s01 1月2000\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 2000\"、\" 土1月01 01:42:53 2000\"]}}" 、"{\" 軌道 \":{\" version\":1、\" type\":\" STPOINT\"、\" leafcount\":10、\" start_time\"土1月01 01:42:53 2000\" 、\"end_time\":\"土1月01 03:24:26 2000\" 、\"空間 \":\"LINESTRING(-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)\" 、\"タイムライン \":[\"土1月01 01:42:53 2000\" 、\"土1月01 01:54:10 2000\" 、\"土1月01 02:05:27 2000\" 、\"土1月02:16:44 2000\" 、\"土1月01: 2000\" 、\"土1月01: 02:39:18 \" 、\"土1月01: 2000\" 、\"土1月01: 02:50:35 2000\" 、\"土1月01: 13:09 \" 、\"土1月01: 13:09 2000\" \"、\" 土1月01 :24:26 \"2000}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"cut_point.max_point":3}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":3,\" start_time\":\" Sat Jan 01 00:01:19 2000\",\" end_time\":\" Sat Jan 01 00:23:53 2000\",\" \"LINESTRING(-100 -100 -100、-88.8925775739675 -86.6512698383691 -92.3767832526937、-79.6904716538265 -80.6515727923252 -84.2357598245144)\" 、\"timeline\":[\"土曜日1月01 00:01:19 2000\" 、\"土曜日1月00:01 12:36 2000\" 、\"土曜日1月01 00:23:53 2000\"]} "、"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 01 00:23:53 2000\",\"end_time\":\"Sat Jan 01 00:57:44 2000\",\"spatial\":\"LINESTRING(-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172)\" 、\"timeline\":[\"1月1日土00:23:53 2000\" 、\"1月1日土00:35:10 2000\" 、\"1月1日土00:46:27 2000\" 、\"Sat Jan 00:57:44 2000\"]} ","{\"trajectory\" :{\ "version\":1,\"type\":\"STPOINT\",\"leafcount\":4,\"start_time\":\"Sat Jan 00:57:44 2000\",\"end_time\":\"土1月01 01:31:35 2000\" 、\"空間 \":\"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193)\" 、\"timeline\":[\"土1月01 00:57:44 2000\" 、\"土1月01 01:09:01 2000\" 、\"土1月01 01:20:18 2000\" 、\"土1月01 01:31:35 2000\"]} "、"{\"軌道 \" :{\ "バージョン \":1、\"type\":\"STPOINT\" 、\"leafcount\":4、\"start_time\":\"Sat Jan 01 1:31:35 \" \2000\"end_time\":\"土1月01 02:05:27 2000\" 、\"空間 \":\"LINESTRING(-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035)\" 、\"タイムライン \":[\"土1月01 01:31:35 2000\" 、\"土1月01 01:42:53 2000\" 、\"土1月01 01:54:10 2000\" 、\"土1月01 02:05:27 2000\"}}"、"{\"軌道 \" :{\ "バージョン \":1、\"type\" \"\" STPOINT\"、\" leafcount\":4、\" start_time\"土1月02:05:27 2000\" 、\"end_time\":\"土1月02:39:18 2000\" 、\"空間 \":\"LINESTRING(-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052)\" 、\"timeline\":[\"土1月02:05:27 2000\" 、\"土1月02:16:44 2000\" 、\"土1月01: 2000\" 、\"土1月02:28:01 2000\"]} "、"{\"軌道 \" :{\ "version\":1、\"type\" \"POINT\" \"\leafcount" 、"\" \"\" start_time\":\" 土1月01: 2000\"、\" end_time\":\" 土1月01: 03:13:09 2000\"、\" 空間 \":\" LINESTRING(-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472)\"、\" "\timeline":[\"Sat Jan 01 02:39:18 2000\" 、\"Sat Jan 01 02:50:35 2000\" 、\"Sat Jan 01 03:01:52 2000\" 、\"Sat Jan 01 03:13:09 2000\"} "、"{\"trajectory\":: :{\ "version\" version\"\" type\"\" STINT \leafart "\" 、\"2 \" start_time\":\" Sat Jan 01 03:13:09 2000\"、\" end_time\":\" Sat Jan 01 03:24:26 2000\",\" spatial\":\" LINESTRING(15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)\"、\" timeline\":[\" Sat Jan 01: 03:13:09 2000\"、\" 1月1日土03:24:26 2000\"]}"}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"cut_point.even_divide":100}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":2,\" start_time\":\" Sat Jan 01 00:01:19 2000\",\" end_time\":\" Sat Jan 01 12:00:36 2000\",\" spatial\":\" LINESTRING(-100 -100 -100、-88.8925775739675 -86.6512698383691 -92.3767832526937)\"、\" timeline\":[\" 土1月01 00:01:19 2000\"、\" 土1月01 00:36 2000\"]}" 、"{\" trajectory\": :{\" version\":1、\" type\":\" STINT \"、\" leafcount\":2、\" start_time\":\" 1月1日2000 00:12:36日土曜日 \"、\" end_time\":\" 1月01 00:23:53 2000\"、\" spatial\":\" LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937、-79.6904716538265 -80.6515727923252 -84.2357598245144)\"、\" \timeline\":[\" Sat Jan 01 00:12:36 2000\"、\" Sat Jan 01 00:23:53 2000\"]}" 、"{\" trajectory\":{\" version\":1、\" type\":\" STPOINT\"、\" leafcount\":2、\" start_time\":\" Sat Jan 00:23:53 \"\" end_time\":\" Sat Jan 00:35:10 2000\"、\" spatial\":\" LINESTRING(-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983)\"、\" timeline\":[\" Sat Jan 01 00:23:53 2000\"、\" Sat Jan 01 00:35:10 \"2000 \]}"} "、"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 00:35:10 2000\",\"end_time\":\"Sat Jan 01 00:46:27 2000\",\"spatial\":\"LINESTRING(-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113)\" 、\"timeline\":[\"土1月01 00:35:10 2000\" 、\"土1月01 00:46:27 2000\"]}}"、"{\"trajectory\" :{\ "version\":1、\"type\" \"STPOINT\" 、\"leafcount\":2、\"start_time\":\"1月1日2000 00:46:27 \" 、\"end_time\":\"1月1日土00:57:44 2000\" 、\"spatial\":\"LINESTRING(-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172)\" 、\"\timeline":[\"Sat Jan 01 00:46:27 2000\" 、\"Sat Jan 01 00:57:44 2000\"]} "、"{\"trajectory\" :{\ "version\":1、\"type\":\"STPOINT\" 、\"leafcount\":2、\"start_time\":\"Sat Jan 00:57:44 \" \"end_time\":\"土1月01 01:09:01 2000\" 、\"空間 \":\"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733)\" 、\"timeline\":[\"土1月01 00:57:44 2000\" 、\"土1月01 01:09:01 2000\"]} "}" 、"{\" trajectory\":{\" version\":1、\" type\"\" STPOINT\"、\" leafcount\":2、\" start_time\":\" Sat Jan 01 01:09:01 2000\"、\" end_time\":\" Sat Jan 01 01:20:18 2000\"、\" spatial\":\" LINESTRING(-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278)\"、\" timeline\":[\" 土1月01 01:09:01 2000\"、\" 土1月01 01:20:18 2000\"]}" 、"{\" trajectory\": :{\" version\":1、\" type\"\" STPOINT\"、\" leafcount\":2,\" start_time\"\" 土1月01 01:20:18 2000\"、\" end_time\":\" 土1月01 01:31:35 2000\"、\" 空間 \":\" LINESTRING(-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193)\"、\" \timeline ":[\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 2000\"]}" 、"{\" trajectory\":{\" version\":1、\" type\":\" STPOINT\"、\" leafcount\":: 2、\" start_time\":\" Sat Jan 01 1:31:35 \"\2000\" end_time\":\" Sat Jan 01 01:42:53 2000\",\" spatial\":\" LINESTRING(-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781)\",\" timeline\":[\" Sat Jan 01 01:31:35 2000\",\" Sat Jan 01 01:42:53 2000\"]},"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 01:42:53 2000\",\"end_time\":\"Sat Jan 01 01:54:10 2000\" 、\"\" \"LINESTRING(-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011)\" 、\"timeline\":[\"土1月01 01:42:53 2000\" 、\"土1月01 01:54:10 2000\"]} "、"{\"trajectory\" :{\ "version\":1、\"type\" \"STINT \" 、\"leafcount\":2,\"start_time\" \"土1月01: 01:54:10 2000\" 、\"end_time\":\"土1月01: 02:05:27 2000\",\"spatial\":\"LINESTRING(-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035)\" 、\"\timeline" [\"土1月01 01:54:10 2000\" 、\"土1月01 02:05:27 2000\"]} "、"{\"軌道 \" :{\ "バージョン \":1、\"タイプ \":\"STPOINT\" 、\"leafcount\":2、\"start_time\":\"土1月02:05:27 \" 2000\"end_time\":\"土1月01 02:16:44 2000\" 、\"空間 \":\"LINESTRING(-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287)\",\"timeline\":[\"土1月01 02:05:27 2000\",\"土1月01 02:16:44 \"]} "、" "{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":2,\" start_time\":\" Sat Jan 01 02:16:44 2000\",\" end_time\":\" Sat Jan 01 02:28:01 2000\",\" "\" LINESTRING(-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279)\"、\" timeline\":[\" 土1月01 02:16:44 2000\"、\" 土1月01 02:28:01 2000\"]}" 、"{\" trajectory\":{\" version\":1、\" type\"\" STINT \"、\" leafcount\":2,\" start_time\"\" 土1月1日02:28:01 2000\",\" end_time\":\" 土1月01日02:39:18 2000\",\" spatial\":\" LINESTRING(-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052)\"、\" タイムラインタイムライン "\[\" 土1月01: 2000\"、\" 土1月01: 02:39:18 \"]}" 、"{\" trajectory\": :{\" version\":1,\" type\":\" STPOINT\"、\" leafcount\":2,\" start_time\":\" Sat Jan 02:3901: 18 "\2000\" end_time\":\" 土1月01: 2000\"、\" 空間 \":\" LINESTRING(-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248)\"、\" timeline\":[\" 土1月01: 02:39:18 2000\"、\" 土1月02 :50:35 2000\"}"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:50:35 2000\",\"end_time\":\"Sat Jan 01 03:01:01:01:52 2000\" 、\"\" LINESTRING(0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794)\"、\" timeline\":[\" 土1月01: 2000\"、\" 土1月01: 03:01:52 2000\"]}}" 、"{\" trajectory\": :{\" version\":1、\" type\":\" STINT \"、\" leafcount\":2,\" start_time\"\" 土1月01 03:01:52 2000\",\" end_time\":\" 土1月01 03:13:09 2000\",\" spatial\":\" LINESTRING(6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472)\"\" 、\"タイムラインリング \":\"1月03 2000\" Sat Jan 01 03:13:09 2000\"]}","{\" trajectory\":{\" version\":1,\" type\":\" STPOINT\",\" leafcount\":2,\" start_time\":\" Sat Jan 01 03:13:09 2000\"、\" end_time\":\" 土1月01 03:24:26 2000\"、\" 空間 \":\" LINESTRING(15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)\"、\" timeline\":[\" 土1月01 03:13:09 2000\"、\" 土1月01 03:24:26 2000\"]}"}}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"cut_edge.geohash":2}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":2,\" start_time\":\" Sat Jan 01 00:01:19 2000\",\" end_time\":\" Sat Jan 01 00:09:46.164345 2000\",\" \"\" LINESTRING(-100 -100 -100、-91.679041907702 -90 -94.2891820757467)\"、\" timeline\":[\" 土1月01 00:01:19 2000\"、\" 土1月01 00:09:46.164345 2000\"]}" 、"{\" trajectory\":{\" version\":1、\" type\":\" STPOINT\",\" leafcount\":16,\" start_time\":\" 土1月00:09:46.164345 2000\",\" end_time\":\" 土1月02:49:17.421906 2000\",\" 空間 \":\" LINESTRING(-91.679041907702 -90 -94.2891820757467,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0 -4.14807548057343 -20.4562499111193)\"、\" timeline\":[\" 1月1日土曜日00:09:46.164345 2000\"、\" 1月1日土曜日00:12:36 2000\"、\" 1月1日土曜日00:23:53 2000\"、\" 1月1日土曜日00:23:35: 2000\"、\" 土1月00:46:27 2000\"、\" 土1月00:57:44 2000\"、\" 土1月01 01:09:01 2000\"、\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 2000\"、\" 土1月01 01:4:53 \"、\" 土1月01 01:42:2000 "\" 土1月01: 2000\"、\" 土1月01: 28:01 2000\"、\" 土1月01: 02:39:18 2000\"、\" 土1月01: 02:49:17.421906 2000\"}" 、"{\" 軌道 \":{\" バージョン \":1、\" type\"\" STINT \leafcount "\3、\" start_time\":\" 土1月02:49:17.421906 2000\"、\" end_time\":\" 土1月02:55:38.141408 2000\"、\" spatial\":\" LINESTRING(0 -4.14807548057343 -20.4562499111193、0.488159407706304 -3.68223926316326 -19.9478872027248、3.10579191624359 0 -17.7507280351428)\"、\" タイムラインタイムライン "\[\" 土1月02:49:17.421906 2000\"、\" 土1月02:50:35 2000\"、\" 土1月02:55:38.141408 2000\"]}}" 、"{\" 軌道 \":1,\" type\":\" STPOINT\"\" Sat Jan 01 02:55:38.141408 2000\"、\" end_time\":\" Sat Jan 01 03:24:26 2000\"、\" spatial\":\" LINESTRING(3.10579191624359 0 -17.7507280351428、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)\"、\" タイムライン \"タイムライン \":[\"Sat Jan 02:01: 55:38; 141408。2000。\" 土1月01 03:01:52 2000\"、\" 土1月01 03:13:09 2000\"、\" 土1月01 03:24:26 2000\"]}"}}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"cut_edge.geohash":20}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":2,\" start_time\":\" Sat Jan 01 00:01:19 2000\",\" end_time\":\" Sat Jan 01 00:09:46.164345 2000\",\" \"\" LINESTRING(-100 -100 -100、-91.679041907702 -90 -94.2891820757467)\"、\" timeline\":[\" 土1月01 00:01:19 2000\"、\" 土1月01 00:09:46.164345 2000\"]}" 、"{\" trajectory\":{\" version\":1、\" type\":\" STPOINT\",\" leafcount\":2,\" start_time\":\" 1月1日00:09:46.164345 2000\",\" end_time\":\" 1月1日00:00:28. 502343 2000\",\" 空間 \":\" LINESTRING(-91.679041907702 -90 -94.2891820757467,-90 -87.9821531498204 -93.1368264797063)\"\" timeline\":[\" 1月1日土曜日00:09:46.164345 2000\"、\" 1月1日土曜日00:11:28.502343 2000\"]}}" 、"{\" trajectory\":{\" version\":1、\" type\":\" STPOINT\"、\" leafcount\":3、\" start_time\"\" Sat Jan 01 00:11:28.502343 2000\"、\" end_time\":\" Sat Jan 01 00:23:13.683811 2000\"、\" spatial\":\" LINESTRING(-90 -87.9821531498204 -93.1368264797063、-88.8925775739675 -86.6512698383691 -92.3767832526937、-80.2248759822245 -81 -84.7085427065378)\"、\" timeline\":[\" Sat Jan 01 00:11:28.502343 2000\"、\" Sat Jan 00:01 00:12:36 2000\"、\" Sat Jan 01 00:23:13.683811 2000\"]}}" 、"{\" 軌道 \":{\" バージョン \":1、\" type\":\" STPOINT\"、\" leafcount\":4 \" 土1月00:23:13.683811 2000\"、\" end_time\":\" 土1月00:38:30.421485 2000\"、\" spatial\":\" LINESTRING(-80.2248759822245 -81 -84.7085427065378、-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-74.2982897989444 -72 -78.7459626278695)\"、\" \"timeline\":[\"土1月00:23:13.683811 2000\" 、\"土1月00:23:53 2000\" 、\"土1月00:35:10 2000\" 、\"土1月00:35:10 2000\" 、\"土1月00:38:30.421485 \"}}"、"{\"軌道 \": :{\ "バージョン \":1、\"type\" \"POINT\" 、\"leafcount\":2,\"start_time\" \"1月1日土00:38:30.421485 2000\",\"end_time\":\"1月1日土00:43:28.511363 2000\",\"空間 \":\"LINESTRING(-74.2982897989444 -72 -78.7459626278695,-72 -69.3863576001334 -76.1360603920884)\" 、\"タイムラインアップ" [\"土1月00:38:30.421485 2000\" 、\"土1月00:43:28.511363 2000\"]}}"、"{\"trajectory\" :{\ "version\":1、\"type\":\"STPOINT\" 、\"leafcount\":3、\"start_time\" \"土1月00:43:28.511363 2000\" 、\"end_time\":\"土1月00:54:30.015135 2000\" 、\"空間 \":\"LINESTRING(-72 -69.3863576001334 -76.1360603920884、-70.6238425321256 -67.8213750167439 -74.5733173238113、-64.1866960348325 -63 -69.8745194055467)\" 、\"timeline\":[\"土1月00:43:28.511363 2000\" 、\"土1月00:46:27 2000\" 、\"土1月00:54:30.015135 2000\"]}}"、"{\"軌道 \" :{\ "バージョン \":1,\"type\":\"STPOINT\",\"leafcount\":4 \"土1月00:54:30.015135 2000\" 、\"end_time\":\"土1月01 01:09:58.469202 2000\" 、\"空間 \":\"LINESTRING(-64.1866960348325 -63 -69.8745194055467、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-55.3348472217782 -54 -64.255912931582)\" 、\"\" timeline\":[\" 土1月00:54:30.015135 2000\"、\" 土1月00:57:44 2000\"、\" 土1月01 01:09:01 2000\"、\" 土1月01 01:09:58.469202 2000\"}}" 、"{\" trajectory\": :{\" version\":1、\" type\"\" STINT \"、\" leafcount\":2,\" start_time\"\" 土1月01 01:09:58.469202 2000\",\" end_time\":\" 土1月01 01:11:37.451871 2000\",\" spatial\":\" LINESTRING(-55.3348472217782 -54 -64.255912931582,-54 -53.2654837710153 -63.8341340031287)\"、\" timeline\"[\" 土1月01 01:09:58.469202 2000\"、\" 土1月01 01:11:37.451871 2000\"]}}" 、"{\" trajectory\":{\" version\":1、\" type\"\" STPOINT\"、\" leafcount\":4、\" start_time\"\" 土1月01 01:11:37.451871 2000\"、\" end_time\":\" 土1月01 01:33:24.965894 2000\"、\" 空間 \":\" LINESTRING(-54 -53.2654837710153 -63.8341340031287、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-40.7151936044906 -45 -55.3523052869103)\"、\" \"timeline" \timeline ":[\" 土1月01 01:11:37.451871 2000\"、\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 2000\"、\" 土1月01 01:33:24.965894 2000\"}}" 、"{\" trajectory\": :{\" version\":1、\" type\"\" \STINT \"、\" leafcount\":2,\" start_time\"\" 1月1日土曜日01:33:24.965894 2000\"、\" end_time\":\" 1月1日01:42:04.984069 2000\",\" 空間 \":\" LINESTRING(-40.7151936044906 -45 -55.3523052869103、-36 -38.7463268915818 -49.7910692902516)\"、\timeline" \:[\"Sat Jan 01 01:33:24.965894 2000\",\"Sat Jan 01 01:42:04.984069 2000\"]}}","{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":3,\"start_time\" \"土1月01: 01:42:04.984069 2000\" 、\"end_time\":\"土1月01: 349176 2000\" 、\"空間 \":\"LINESTRING(-36 -38.7463268915818 -49.7910692902516、-35.5646221285375 -38.1688933617746 -49.2775720101781、-33.7014373194637 -36 -46.8000630466357)\" 、\"timeline\":[\"Sat Jan 01 01:42:04.984069 2000\" 、\"Sat Jan 01 01:42:53 2000\" 、\"Sat Jan 01 01:48:21.349176 2000\"]}}"、"{\"trajectory\" :{\ "version: 1,\" type\":\" STPOINT\",\" leafcount\"\" \"start_time: 3 \" 土1月01: 349176 2000\"、\" end_time\":\" 土1月01: 04:47.906716 2000\"、\" 空間 \":\" LINESTRING(-33.7014373194637 -36 -46.8000630466357、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.6531311667283 -27 -40.8569704267813)\"、\" timeline\":[\" Sat Jan 01 01:48:21.349176 2000\"、\" Sat Jan 01 01:54:10 2000\"、\" Sat Jan 01 02:04:47.906716 2000\"]}}" 、"{\" trajectory\":{\" version: 1,\"type\":\"STPOINT\",\leaf "\" 土1月01: 04:47.906716 2000\"、\" end_time\":\" 土1月01: 14:14:27. 901294 2000\"、\" 空間 \":\" LINESTRING(-23.6531311667283 -27 -40.8569704267813、-23.1585765127093 -26.5895827477798 -40.6539742602035、-18 -22.6137624729753 -37.9786882742987)\"、\" timeline\":[\" 土1月02:04:47.906716 2000\"、\" 土1月02:05:27 2000\"、\" 土1月02:14:27.901294 2000\"]}}" 、"{\" 軌道 \":{\" バージョン \":1,\" type\":\" STPOINT\",\" leafcount\":3 \\" 土1月01: 14:27.901294 2000\"、\" end_time\":\" 土1月01: 22:22:10. 613419 2000\"、\" 空間 \":\" LINESTRING(-18 -22.6137624729753 -37.9786882742987、-16.7020264320696 -21.6133877349397 -37.3055470525287、-14.4839626118998 -18 -32.9268796268747)\"、\" timeline\":[\" 土1月01: 14:27.901294 2000\"、\" 土1月01: 16:44 2000\"、\" 土1月01: 22:10:10. 613419 2000\"]}}" 、"{\" trajectory\":1,\" type\":\" STPOINT\",\leaf" \"\" 土1月01: 22:10.613419 2000\"、\" end_time\":\" 土1月01: 37:05.008004 2000\"、\" 空間 \":\" LINESTRING(-14.4839626118998 -18 -32.9268796268747、-12.1044529232507 -14.1236051704424 -28.2295028120279、-5.40873786119054 -9 -25.1395922697196)\"、\" timeline\":[\" 土1月02:22:10.613419 2000\"、\" 土1月01: 2000\"、\" 土1月02:28:01 008004 \"土1月02:37:05. 2000\"]}}"、"{\"軌道 \":1,\"type\":\"STPOINT\",\"leafcount\" \"土1月01: 37:05.008004 2000\" 、\"end_time\":\"土1月01: 49:17.421906 2000\" 、\"空間 \":\"LINESTRING(-5.40873786119054 -9 -25.1395922697196、-3.77185660181567 -7.74744770256802 -24.3842111621052、0 -4.14807548057343 -20.4562499111193)\" 、\"timeline\":[\"土1月02:37:05.008004 2000\" 、\"土1月02:39:18 2000\" 、\"土1月02:49:17.421906 2000\"]}}"、"{\"軌道 \":1,\"type\":\"STPOINT\",\leafcount\"\" \start_time ":3 \" 土1月02:49:17.421906 2000\"、\" end_time\":\" 土1月02:55:38.141408 2000\"、\" 空間 \":\" LINESTRING(0 -4.14807548057343 -20.4562499111193、0.488159407706304 -3.68223926316326 -19.9478872027248、3.10579191624359 0 -17.7507280351428)\"、\" タイムライン \"、\" タイムライン \"タイムライン \":[\"Sat Jan01月02: 49:49:49:17... \" 土1月01: 50:35 2000\"、\" 土1月01: 55:38.141408 2000\"]}" 、"{\" trajectory\":{\" version\":1,\" type\":\" STPOINT\"、\" leafcount\":3,\" start_time\":\" \"Sat Jan 02:55" \141408 \"end_time\":\"土1月01 03:10:13.430886 2000\" 、\"空間 \":\"LINESTRING(3.10579191624359 0 -17.7507280351428、6.33406881305078 4.54123636645575 -15.0410129944794、13.2463610753472 9 -12.2531528591899)\" 、\"timeline\":[\"1月01の土02:55:38.141408 2000\" 、\"土1月01: 0:01:52 2000\" 、\"土1月01 03:10:13.430886 2000\"]} ","{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":3,\"start_time\":\"土1月01 03:10:13.430886 2000\",\"end_time \:\" Sat Jan 01 03:24:26 2000\"、\" spatial\":\" LINESTRING(13.2463610753472 9 -12.2531528591899、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)\"、\" timeline\":[\" Sat Jan 01 03:10:13.430886 2000\"、\" Sat Jan 01 03:13:13:09 2000\"、\" 1月1日土03:24:26 2000\"]}"}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"drop_edge.spatial_distance_2d":13}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":18,\" start_time\":\" Sat Jan 01 00:12:36 2000\",\" end_time\":\" Sat Jan 01 03:24:26 2000\",\" "\" LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937、-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、-0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)\"、\" タイムライン \":[\" 1月の土00:01 12:36 2000\"、\" 1月の土00:23:53 2000\"、\" 土1月00:35:10 2000\"、\" 土1月00:46:54:27 2000\"、\" 土1月00:57:44 2000\"、\" 土1月01 01:09:01 2000\"、\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 \"、\" 1: 2000年1: 425.10 "\" 土1月02:05:27 2000\"、\" 土1月01: 16:44 2000\"、\" 土1月01: 2000\"、\" 土1月01: 02:39:18 2000\"、\" 土1月01: 0:50:35 2000\"、\" 土1月01: 0:0:01/52 2000\"、\" 土1月01: 2000\"1月1日土03:24:26 2000\"]} "}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"drop_edge.spatial_distance_2d":10}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":3,\" start_time\":\" Sat Jan 01 00:23:53 2000\",\" end_time\":\" Sat Jan 01 00:46:27 2000\",\" \"LINESTRING(-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113)\" 、\"timeline\":[\"土1月01 00:23:53 2000\" 、\"土1月01 00:35:10 2000\" 、\"土1月00:46:27 2000\"]} "、"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 00:57:44 2000\",\"end_time\":\"Sat Jan 01 01:09:2000 \",\"\" LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733)\"、\" timeline\":[\" 土1月01 00:57:44 2000\"、\" 土1月01 01:09:01 2000\"]}" 、"{\" trajectory\": :{\" version\":1、\" type\"\" STPOINT\"、\" leafcount\":2,\" start_time\"\" 土1月01 01:20:18 2000\"、\" end_time\":\" 土1月01 01:31:35 2000\"、\" 空間 \":\" LINESTRING(-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193)\"、\" \timeline ":[\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 2000\"]}" 、"{\" trajectory\":{\" version\":1、\" type\":\" STPOINT\"、\" leafcount\":2、\" start_time\":\" Sat Jan 001 1:42:53 "\2000\" end_time\":\" 土1月01 01:54:10 2000\"、\" 空間 \":\" LINESTRING(-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011)\",\" timeline\":[\" 土1月01 01:42:53 2000\"、\" 土1月01 01:54:10 2000\"]} 、"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":3,\"start_time\":\"Sat Jan 01 02:05:27 2000\",\"end_time\":\"Sat Jan 01 02:28:01 2000\",\"" \"LINESTRING(-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279)\" 、\"timeline\":[\"土1月01: 02:27 2000\" 、\"土1月01: 02:16:44 2000\" 、\"土1月01: 28:01 2000\"]} "、"{\"trajectory\" :{\ "version\":1,\"type\" \"STPOINT\",\"leafcount\":2,\"start_time\":\"Sat Jan 01 02:39:18 2000\",\"end_time\":\"Sat Jan 01 02:50:35 2000\" 、\"\" \"LINESTRING(-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248)\" 、\"timeline\":[\"土1月01 02:39:18 2000\" 、\"土1月01 02:50:35 2000\"]} "、"{\"trajectory\" :{\ "version\":1、\"type\" \"STINT \" 、\"leafcount\":2,\"start_time\" \"土1月1 3:13:09 2000\",\"end_time\":\"土1月01 03:24:26 2000\",\"spatial\":\"LINESTRING(15.6666049417108 10.5611746329814 -11.2770220567472: 14 11 -10)\" \"、\" timeline\":\" 1月03 2000\"1月1日土03:24:26 2000\"]} "}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"drop_edge.spatial_distance_2d":1}') を選択します。
st_split
----------
{}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからST_split(a, '{"cut_edge.time_interval":"50 minute"}') を選択します。
st_split
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------
{"{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":6,\" start_time\":\" Sat Jan 01 00:01:19 2000\",\" end_time\":\" Sat Jan 01 00:51:19 2000\",\" spatial\"\" LINESTRING(-100 -100 -100、-88.8925775739675 -86.6512698383691 -92.3767832526937、-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-66.7323562440603 -64.9066806292278 -71.7327251954505)\"、\" timeline\":[\" 1月1日土00:01:19 2000\"、\" 1月1日土00:12:36 2000\"、\" 土1月00:23:53 2000\"、\" 土1月00:35:10 2000\"、\" 土1月00:46:27 2000\"、\" 土1月00:51:19 2000\"}}" 、"{\" trajectory\": :{\" version\":1、\" type\":\" STPOINT\"、" \leafcount "6 \" start_time\":\" 土1月00:51:19 2000\"、\" end_time\":\" 土1月01 01:41:19 2000\"、\" 空間 \":\" LINESTRING(-66.7323562440603 -64.9066806292278 -71.7327251954505、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193-36.4169542584517 -39.2993255279553 -50.2828372271723)\"、\" timeline\":[\" 土1月00:51:19 2000\"、\" 土1月00:57:44 2000\"、\" 土1月01 01:09:01 2000\"、\" 土1月01 01:20:18 2000\"、\" 土1月01 01:31:35 \"、\" Sat Jan 01 01:41:19 2000\"]}","{\" trajectory\":{\" version\":1,\" type\":\" STPOINT\",\" leafcount\":7,\" start_time\":\" Sat Jan 01 01:41:19 2000\",\" end_time\"\" 土1月01: 31:19 2000\"、\" spatial\":\" LINESTRING(-36.4169542584517 -39.2993255279553 -50.2828372271723、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279、-9.66744543190043 -12.2587910217879 -27.1048828021539)\"、\" timeline\":[\" 土1月01 01:41:19 2000\"、\" 土1月01 01:42:53 2000\"、\" 土1月01 01:54:10 2000\"、\" 土1月01 02:05:27 2000\"、\" 土1月01 02:16:44 2000\"、\" 土1月01: 02:01 2000\"\" 1月2000} "{\" trajectory\":{\" version\":1,\" type\"\" STPOINT\",\" leafcount\":6,\" start_time\":\" Sat Jan 01 02:31:19 2000\",\" end_time\":\" Sat Jan 01 03:21:19 2000\"、\" 空間 \":\" LINESTRING(-9.66744543190043 -12.2587910217879 -27.1048828021539、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14.4603473029541 10.8787882664217 -10.3527372593969)\"、\" timeline\":[\" Sat Jan 01 02:31:19 2000\"、\" Sat Jan 01 02:39:18 2000\"、\" Sat Jan 01 02:50:35 \"2000\" 土1月01 03:01:52 2000\"、\" 土1月01 03:13:09 2000\"、\" 土1月01 03:21:19 2000\"}}" 、"{\" 軌道 \":{\" バージョン \":1、\" type\"\" STPOINT\"、\" leafcount\":2" start_time "\" 土1月01 03:21:19 2000\"、\" end_time\":\" 土1月01 03:24:26 2000\"、\" 空間 \"LINESTRING(14.4603473029541 10.8787882664217 -10.3527372593969、14 11 -10)\" 、\"タイムライン \":[\"土1月01 03:21:19 2000\" 、\"\" 土1月01 03:2426 "}}
(1行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからunnest(ST_split(a, '{"cut_edge.time_interval":"1 hour, 2000-01-01"}')) を選択します。
unnest
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"Sat Jan 01 00:01:19 2000","end_time":"Sat Jan 01 01:00 2000","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144)-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-60.498271121079 -59.7303503400986 -67.2870085171893)" 、"timeline":["Sat Jan 01 00:01:19 2000" 、"Sat Jan 01 00:12:36 2000" 、"Sat Jan 01 00:23:53 2000" 、"Sat Jan 01 00:35:10 2000" 、"Sat Jan 01 00:46:27" 、"2000「土1月1日00:57:44 2000」、「土1月1日01:00:00 2000」]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"Sat Jan 01 01:00:00 2000","end_time":"Sat Jan 01 02:00:00 2000","spatial":"LINESTRING(-60.498271121079 -59.7303503400986 -67.2870085171893,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278)-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-27.2953324320126 -30.0225622652283 -42.3519576617594)" 、"timeline":["土1月01 01:00:00 2000" 、"土1月01 01:09:01 2000" 、"土1月01 01:20:18 2000" 、"土1月01 01:31:35 2000" 、"土1月01 01:42:53" 2000 "土1月01 01:54:10 2000" 、"土1月01 02:00:00 2000"]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":7,"start_time":"Sat Jan 01 02:00:00 2000","end_time":"Sat Jan 01 03:00:00 2000","spatial":"LINESTRING(-27.2953324320126 -30.0225622652283 -42.3519576617594,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287)-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、5.36694642989187 3.18077954146708 -15.8527853893442)" 、"timeline":["Sat Jan 01 02:00:00 2000" 、"Sat Jan 01 02:05:27 2000" 、"Sat Jan 01 02:16:44 2000" 、"Sat Jan 01 02:02:01 2000" 、"Sat Jan 01 :02:2000" 、"035 1 025 021" 1月1日土曜日03:00:00 2000 "]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":4,"start_time":"Sat Jan 01 03:00:00 2000","end_time":"Sat Jan 01 03:24:26 2000","spatial":"LINESTRING(5.36694642989187 3.18077954146708 -15.8527853893442,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 -11)" timeline ":[" Sat Jan 01 03:00:00 2000 "、" Sat Jan 01 03:01:52 2000 "、" Sat Jan 01 03:13:09 2000 "、" Sat Jan 01 03:24:26 2000 "]}}
(4行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからunnest(ST_split(a, '{1,3,5}'::int[])) を選択します。
unnest
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:12:36 2000","end_time":"Sat Jan 01 00:35:10 2000","spatial":"LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983)" timeline ":[" Sat Jan 01 00:12:36 2000 "、" Sat Jan 01 00:23:53 2000 "、" Sat Jan 01 00:35:10 2000 "]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:35:10 2000","end_time":"Sat Jan 01 00:57:44 2000","spatial":"LINESTRING(-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172)" timeline ":[" Sat Jan 01 00:35:10 2000 "、" Sat Jan 01 00:46:27 2000 "、" Sat Jan 01 00:57:44 2000 "]}}
(2行)
trajとして (
選択
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)-79.6904716538265 -80.6515727923252 -84.2357598245144、-75.8435507711644 -73.7572890928326 -80.5007370118983、-70.6238425321256 -67.8213750167439 -74.5733173238113、-61.6014582272619 -61.0636760429479 -67.9874239303172、-56.1098577060426 -54.4264591250879 -64.5007972046733、-46.9800617334743 -49.4026757289345 -61.6160059720278、-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10)" 、"timeline":["2000-01-01 00:01:19.067179" 、"2000-01-01 00:36. 116007" 、"2000-01-01 00:23:53.164835" 、「2000-01 00:35:10.213663」、「2000-01-01 00:46:27.262491」、「2000-01-01 00:57:44.311319」、「2000-01-01 01:09:01.360147」、「2000-01-01-01 01:20:18.408975」、「2000-01-01-01 01:31:35. 」、「2000-01-01.01 457803」、「425.01.01-0:01: 506631。「2000-01-01 01:54:09.555459」、「2000-01-01 02:05:26.604287」、「2000-01-01 02:16:43.653115」、「2000-01-01 02:28:00.701943」、「2000-01-01 02:39:17.750771」、「2000-01-01-01 02:50:34.. 」、「2000-01: 01. 051」 "2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}':::trajectory as a
)
trajからunnest(ST_split(a, '{0}'::int[] | | '{0,1,3,5}'::int[] | | ST_leafcount(a) - 1)) を選択します。
unnest
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"Sat Jan 01 00:01:19 2000","end_time":"Sat Jan 00:01 12:36 2000","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937)" timeline ":[" Sat Jan 01 00:01:19 2000 "、" Sat Jan 01 00:12:36 2000 "]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:12:36 2000","end_time":"Sat Jan 01 00:35:10 2000","spatial":"LINESTRING(-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983)" timeline ":[" Sat Jan 01 00:12:36 2000 "、" Sat Jan 01 00:23:53 2000 "、" Sat Jan 01 00:35:10 2000 "]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"Sat Jan 01 00:35:10 2000","end_time":"Sat Jan 01 00:57:44 2000","spatial":"LINESTRING(-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172)" timeline ":[" Sat Jan 01 00:35:10 2000 "、" Sat Jan 01 00:46:27 2000 "、" Sat Jan 01 00:57:44 2000 "]}}
{"trajectory":{"version":1,"type":"STPOINT","leafcount":14,"start_time":"Sat Jan 01 00:57:44 2000","end_time":"Sat Jan 01 03:24:26 2000","spatial":"LINESTRING(-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278)-41.7122942996211 -46.3224360072054 -56.5283147455193、-35.5646221285375 -38.1688933617746 -49.2775720101781、-31.7230528349367 -33.6970051738123 -44.1693710885011、-23.1585765127093 -26.5895827477798 -40.6539742602035、-16.7020264320696 -21.6133877349397 -37.3055470525287、-12.1044529232507 -14.1236051704424 -28.2295028120279、-3.77185660181567 -7.74744770256802 -24.3842111621052、0.488159407706304 -3.68223926316326 -19.9478872027248、6.33406881305078 4.54123636645575 -15.0410129944794、15.6666049417108 10.5611746329814 -11.2770220567472、14 11 -10" 、"タイムライン":["Sat Jan 01 00:57:44 2000" 、"Sat Jan 01 01:09:01 2000" 、"Sat Jan 01 01:20:18 2000" 、"Sat Jan 01 01:31:35 2000" 、"Sat Jan 01 01:42:53 2000" 、"Sat Jan 01 16:54:10 2000" 、"Sat Jan 01 02:01" 2000 :02:01 "、" 02:01: 2000 "" 土1月01 02:39:18 2000 "、" 土1月01 02:50:35 2000 "、" 土1月01 03:01:52 2000 "、" 土1月01 03:13:09 2000 "、" 土1月01 03:24:26 2000 "]}}
(4行)