d0puLG2Z

1SELECT id, 
2       x, y, z,
3       array_to_string(ARRAY_AGG(IC.CID ORDER BY IC.image_id), ' ') CID, 
4       array_to_string(ARRAY_AGG(IC.r ORDER BY IC.image_id), ' ') r, 
5       array_to_string(ARRAY_AGG(IC.g ORDER BY IC.image_id), ' ') g, 
6       array_to_string(ARRAY_AGG(IC.b ORDER BY IC.image_id), ' ') b, 
7       array_to_string(ARRAY_AGG(IC.image_id ORDER BY IC.image_id), ' ') IMG  
8FROM point3d P3D JOIN (SELECT C.id CID, r, g, b, point3d_no, image_no image_id
9		       FROM colorinfo C) IC ON IC.point3d_no = P3D.id
10GROUP BY P3D.id
11HAVING position('5 6 7 8' in array_to_string(ARRAY_AGG(IC.image_id ORDER BY IC.image_id), ' ')) != 0;
12
13
14--DROP TABLE IF EXISTS tmpxyz;
15CREATE TEMP TABLE IF NOT EXISTS tmpxyz AS (SELECT id, 
16       x, y, z, i
17       --array_to_string(ARRAY_AGG(IC.CID ORDER BY IC.image_id), ' ') CID, 
18       --array_to_string(ARRAY_AGG(IC.r ORDER BY IC.image_id), ' ') r, 
19       --array_to_string(ARRAY_AGG(IC.g ORDER BY IC.image_id), ' ') g, 
20       --array_to_string(ARRAY_AGG(IC.b ORDER BY IC.image_id), ' ') b, 
21       --array_to_string(ARRAY_AGG(IC.image_id ORDER BY IC.image_id), ' ') IMG  
22FROM point3d P3D JOIN (SELECT C.id CID, r, g, b, point3d_no, image_no image_id
23		       FROM colorinfo C) IC ON IC.point3d_no = P3D.id
24GROUP BY P3D.id
25HAVING position('6 7 8 9' in array_to_string(ARRAY_AGG(IC.image_id ORDER BY IC.image_id), ' ')) != 0); 
26
27DROP TABLE IF EXISTS pt;
28CREATE TEMP TABLE pt AS (SELECT x, y, z, r, 
29FROM tmpxyz XYZ JOIN colorinfo C ON C.point3d_no = XYZ.id
30WHERE C.image_no = 9);
31COPY pt TO '/Users/otakusaikou/Desktop/ptCloud/IMG_8706.txt' DELIMITER ';' CSV HEADER;