とりあえずメモ(宿題)
CREATE TABLE trips_test (
id serial PRIMARY KEY,
user_id integer,
distance double precision,
place point[],
speed double precision[],
prediction_type integer,
start_at timestamp,
end_at timestamp
);
INSERT INTO trips_test (user_id, distance, place) VALUES (1, 24.66249845798845, ARRAY[point '(34.7241, 135.1294)', point '(34.7241, 135.1294)', point '(34.7241, 135.1294)']);
moove=# INSERT INTO trips_test (user_id, distance, place)
moove-# VALUES (1, 24.66249845798845, ARRAY[point '(34.7241, 135.1294)', point '(34.7241, 135.1294)', point '(34.7241, 135.1294)']);
INSERT 0 1
moove=# select * from trips_test;
id | user_id | distance | place | speed | prediction_type | start_at | end_at
----+---------+-------------------+------------------------------------------------------------------+-------+-----------------+----------+--------
1 | 1 | 24.66249845798845 | {"(34.7241,135.1294)","(34.7241,135.1294)","(34.7241,135.1294)"} | | | |
(1 row)
上記のテーブルから、座標情報を一つ一つ取り出すにはどうしたら良いですか
SELECT id, user_id, unnest(place) AS single_point
FROM trips_test;
moove=# SELECT id, user_id, unnest(place) AS single_point
moove-# FROM trips_test;
id | user_id | single_point
----+---------+--------------------
1 | 1 | (34.7241,135.1294)
1 | 1 | (34.7241,135.1294)
1 | 1 | (34.7241,135.1294)