Ikhtisar
Data trajektori mencatat informasi lokasi yang terus diperbarui dari fitur bergerak, seperti kendaraan atau individu. Data ini merupakan jenis data spatio-temporal yang khas dan dapat digunakan untuk analisis mendalam.
GanosBase Trajectory adalah ekstensi dari AnalyticDB for PostgreSQL. GanosBase Trajectory menyediakan tipe data, fungsi, dan prosedur tersimpan yang memungkinkan Anda mengelola, menanyakan, dan menganalisis data trajektori spatio-temporal.
Panduan cepat
Buat ekstensi.
Create extension ganos_spatialref; Create extension ganos_geometry; Create Extension Ganos_trajectory;Buat tipe enumerasi untuk trajektori.
CREATE TYPE leaftype AS ENUM ('STPOINT', 'STPOLYGON');Buat tabel trajektori.
Create Table traj_table (id integer, traj trajectory) DISTRIBUTED BY (id);Masukkan data trajektori ke dalam tabel trajektori.
insert into traj_table values (1, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange, '{"leafcount": 3,"attributes" : {"velocity" : {"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')), (2, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '2010-01-01 14:30'::timestamp, '2010-01-01 15:30'::timestamp, '{"leafcount": 3,"attributes" : {"velocity" : {"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')), (3, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326),ARRAY['2010-01-01 14:30'::timestamp, '2010-01-01 15:00'::timestamp, '2010-01-01 15:30'::timestamp], '{"leafcount": 3,"attributes" : {"velocity" : {"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')), (4, ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326), '[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange, null));Buat indeks trajektori pada tabel trajektori.
-- Buat indeks trajektori untuk mempercepat penyaringan data spasio-temporal. create index tr_index on traj_table using gist (traj); -- Gunakan indeks trajektori untuk mempercepat penyaringan data spasial dalam kueri pada data spasial. select id, traj_id from traj_test where st_3dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))')); -- Gunakan indeks trajektori untuk mempercepat penyaringan data temporal dalam kueri pada data temporal. select id, traj_id from traj_text where st_TContains(traj,'2008-02-02 13:30:44'::timestamp,'2008-02-03 17:30:44'::timestamp); -- Gunakan indeks trajektori untuk mempercepat penyaringan data spasio-temporal dalam kueri pada data spasio-temporal. select id, traj_id from traj_test where st_3dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'),'2008-02-02 13:30:44'::timestamp,'2008-02-03 17:30:44'::timestamp);Buat indeks trajektori di dimensi tertentu pada tabel trajektori.
-- Jika Anda ingin menganalisis data trajektori hanya dari dimensi tertentu, buat indeks trajektori di dimensi tersebut. Misalnya, jika Anda tidak ingin menganalisis data trajektori di dimensi z, buat indeks trajektori temporal dua dimensi dengan menggunakan trajgist_op_2dt. create index tr_timespan_time_index on traj_table using gist (traj trajgist_op_2dt); -- Lakukan kueri pada data spasio-temporal dua dimensi. Kueri dipercepat. select id, traj_id from traj_test where st_2dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'),'2008-02-02 13:30:44'::timestamp,'2008-02-03 17:30:44'::timestamp); -- Buat lebih banyak indeks trajektori berdasarkan kebutuhan bisnis Anda. Jika Anda membuat beberapa indeks trajektori, AnalyticDB for PostgreSQL akan memilih indeks trajektori optimal saat Anda melakukan kueri. create index tr_timespan_time_index on trajtab using gist (traj trajgist_op_2d); select id, traj_id from traj_test where st_2dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'));Kueri waktu mulai dan waktu akhir trajektori.
select st_startTime(traj), st_endTime(traj) from traj_table ; st_starttime | st_endtime ---------------------+--------------------- 2010-01-01 14:30:00 | 2010-01-01 15:30:00 2010-01-01 14:30:00 | 2010-01-01 15:30:00 2010-01-01 14:30:00 | 2010-01-01 15:30:00 2010-01-01 14:30:00 | 2010-01-01 15:30:00 2010-01-01 14:30:00 | 2010-01-01 15:30:00 2010-01-01 11:30:00 | 2010-01-01 15:00:00 2010-01-01 11:30:00 | 2010-01-01 15:00:00 2010-01-01 11:30:00 | 2010-01-01 15:00:00 (8 rows)Kueri informasi tentang trajektori.
-- Gunakan fungsi interpolasi untuk menanyakan atribut titik trajektori. Select ST_velocityAtTime(traj, '2010-01-01 12:45') from traj_table where id > 5; st_velocityattime ------------------- 5 5 4.16666666666667 (3 rows)Analisis kedekatan antar trajektori.
Select ST_euclideanDistance((Select traj From traj_table Where id = 6), (Select traj From traj_table Where id = 7)); st_euclideandistance ---------------------- 0.0334968923954815 (1 row)Hapus ekstensi.
DROP Extension Ganos_Raster; DROP extension ganos_geometry; Drop Extension Ganos_trajectory;
Referensi SQL
Untuk informasi lebih lanjut, lihat Konsep Dasar.