1 -- PHOROS -- Photogrammetric Road Survey
2 -- Copyright (C) 2011 Bert Burgemeister
4 -- This program is free software; you can redistribute it and/or modify
5 -- it under the terms of the GNU General Public License as published by
6 -- the Free Software Foundation; either version 2 of the License, or
7 -- (at your option) any later version.
9 -- This program is distributed in the hope that it will be useful,
10 -- but WITHOUT ANY WARRANTY; without even the implied warranty of
11 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 -- GNU General Public License for more details.
14 -- You should have received a copy of the GNU General Public License along
15 -- with this program; if not, write to the Free Software Foundation, Inc.,
16 -- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
17 ----------------------------------------------------------------------
20 -- This is a working example for a PL/pgSQL trigger function body.
21 -- Once deployed, it is fired on changes to the user point table. It
22 -- can be deployed with your presentation project like this:
24 -- ./phoros --redefine-trigger-function=<your-presentation-project> --plpgsql-body=<name-of-this-file> ...
26 -- The code threads points from the user point table, grouped by
27 -- column description, to linestrings which are stored into the user
28 -- line table. The strings ~0@*~A and ~1@*~A are placeholders for the
29 -- names of user point table and user line table, respectively.
33 new_point point_bag%ROWTYPE;
34 tried_point point_bag%ROWTYPE;
35 previous_point point_bag%ROWTYPE;
36 starting_point GEOMETRY;
40 -- Set max_bend = 180 in order not to discard any points, and to get
41 -- crazy zigzag lines.
42 max_bend DOUBLE PRECISION DEFAULT 91; -- degrees
46 -- Muffle warnings about implicitly created stuff:
47 SET client_min_messages TO ERROR;
51 old_description := OLD.description;
52 new_description := NEW.description;
57 new_description := NEW.description;
62 old_description := OLD.description;
65 DROP TABLE IF EXISTS point_bag_table;
67 CREATE TEMPORARY TABLE point_bag_table
68 (id SERIAL primary key, coordinates GEOMETRY, description TEXT)
73 IN SELECT description FROM ~0@*~A
74 WHERE description = old_description OR description = new_description
77 INSERT INTO point_bag_table (coordinates, description)
78 SELECT coordinates, description
80 WHERE attribute = 'polyline'
82 description = point_group.description;
85 (SELECT st_centroid(st_collect(coordinates))
86 FROM point_bag_table);
89 (SELECT ROW(id, coordinates)
91 ORDER BY st_distance(point_bag_table.coordinates, starting_point)
94 DELETE FROM point_bag_table WHERE id = previous_point.id;
97 (SELECT ROW(id, coordinates)
99 ORDER BY st_distance(point_bag_table.coordinates,
100 previous_point.coordinates)
103 polyline := st_makeline(previous_point.coordinates,
104 new_point.coordinates);
106 DELETE FROM point_bag_table WHERE id = new_point.id;
108 <<add_or_discard_point>>
110 previous_point.coordinates := st_pointn(polyline,1);
113 (SELECT ROW(id, coordinates)
115 ORDER BY st_distance(coordinates, previous_point.coordinates)
118 EXIT WHEN new_point IS NULL;
120 IF bendedness(st_pointn(polyline, 2),
121 st_pointn(polyline, 1),
122 new_point.coordinates)
123 < bendedness(st_pointn(polyline, st_npoints(polyline) - 1),
124 st_pointn(polyline, st_npoints(polyline)),
125 new_point.coordinates)
127 bendedness(st_pointn(polyline, 2), st_pointn(polyline, 1),
128 new_point.coordinates)
131 polyline := st_addpoint(polyline, new_point.coordinates, 0);
132 DELETE FROM point_bag_table WHERE id = new_point.id;
135 polyline := st_reverse(polyline);
137 DELETE FROM point_bag_table WHERE id = tried_point.id;
139 tried_point := new_point;
140 END LOOP add_or_discard_point;
142 -- RAISE NOTICE '%', st_astext(polyline);
145 WHERE description = point_group.description;
147 IF polyline IS NOT NULL
151 VALUES (point_group.description, polyline);
154 END LOOP thread_one_line;