| 66 | | END; |
|---|
| 67 | | $$ |
|---|
| 68 | | LANGUAGE 'plpgsql' VOLATILE STRICT; |
|---|
| 69 | | |
|---|
| 70 | | ----------------------------------------------------------------------- |
|---|
| 71 | | -- This function should not be used directly. Use assign_vertex_id instead |
|---|
| 72 | | -- |
|---|
| 73 | | -- Inserts a point into a temporary vertices table, and return an id |
|---|
| 74 | | -- of a new point or an existing point. Tolerance is the minimal distance |
|---|
| 75 | | -- between existing points and the new point to create a new point. |
|---|
| 76 | | -- |
|---|
| 77 | | -- Last changes: 14.02.2008 |
|---|
| 78 | | ----------------------------------------------------------------------- |
|---|
| 79 | | CREATE OR REPLACE FUNCTION point_to_id(point geometry, |
|---|
| 80 | | tolerance double precision) |
|---|
| 81 | | RETURNS INT AS |
|---|
| 82 | | $$ |
|---|
| 83 | | DECLARE |
|---|
| 84 | | row record; |
|---|
| 85 | | point_id int; |
|---|
| 86 | | BEGIN |
|---|
| 87 | | LOOP |
|---|
| 88 | | -- TODO: use && and index |
|---|
| 89 | | SELECT INTO row id, the_geom FROM vertices_tmp WHERE |
|---|
| 90 | | distance(the_geom, point) < tolerance; |
|---|
| 91 | | |
|---|
| 92 | | point_id := row.id; |
|---|
| 93 | | |
|---|
| 94 | | IF NOT FOUND THEN |
|---|
| 95 | | INSERT INTO vertices_tmp (the_geom) VALUES (point); |
|---|
| 96 | | ELSE |
|---|
| 97 | | EXIT; |
|---|
| 98 | | END IF; |
|---|
| 99 | | END LOOP; |
|---|
| 100 | | RETURN point_id; |
|---|
| 101 | | END; |
|---|
| 102 | | $$ |
|---|
| 103 | | LANGUAGE 'plpgsql' VOLATILE STRICT; |
|---|
| 104 | | |
|---|
| 105 | | |
|---|
| 106 | | ----------------------------------------------------------------------- |
|---|
| 107 | | -- Fill the source and target_id column for all lines. All line ends |
|---|
| 108 | | -- with a distance less than tolerance, are assigned the same id |
|---|
| 109 | | ----------------------------------------------------------------------- |
|---|
| 110 | | CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table varchar, |
|---|
| 111 | | tolerance double precision, |
|---|
| 112 | | geo_cname varchar, |
|---|
| 113 | | gid_cname varchar) |
|---|
| 114 | | RETURNS VARCHAR AS |
|---|
| 115 | | $$ |
|---|
| 116 | | DECLARE |
|---|
| 117 | | points record; |
|---|
| 118 | | i record; |
|---|
| 119 | | source_id int; |
|---|
| 120 | | target_id int; |
|---|
| 121 | | pre varchar; |
|---|
| 122 | | post varchar; |
|---|
| 123 | | |
|---|
| 124 | | srid integer; |
|---|
| 125 | | |
|---|
| 126 | | countids integer; |
|---|
| 127 | | |
|---|
| 128 | | BEGIN |
|---|
| 129 | | |
|---|
| 130 | | BEGIN |
|---|
| 131 | | DROP TABLE vertices_tmp; |
|---|
| 132 | | EXCEPTION |
|---|
| 133 | | WHEN UNDEFINED_TABLE THEN |
|---|
| 134 | | END; |
|---|
| 135 | | |
|---|
| 136 | | EXECUTE 'CREATE TABLE vertices_tmp (id serial)'; |
|---|
| 137 | | |
|---|
| 138 | | FOR i IN EXECUTE 'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||'''' LOOP |
|---|
| 139 | | END LOOP; |
|---|
| 140 | | |
|---|
| 141 | | srid := i.srid; |
|---|
| 142 | | |
|---|
| 143 | | FOR i IN EXECUTE 'SELECT count(*) as countids FROM '|| quote_ident(geom_table) LOOP |
|---|
| 144 | | END LOOP; |
|---|
| 145 | | |
|---|
| 146 | | countids := i.countids; |
|---|
| 147 | | |
|---|
| 148 | | EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 2)'; |
|---|
| 149 | | |
|---|
| 150 | | CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom); |
|---|
| 151 | | |
|---|
| 152 | | pre = ''; |
|---|
| 153 | | post = ''; |
|---|
| 154 | | |
|---|
| 155 | | FOR i in EXECUTE 'SELECT count(*) as t from ' || quote_ident(geom_table) || ' WHERE NumGeometries(' || quote_ident(geo_cname) || ') is not null' loop |
|---|
| 156 | | IF (i.t > 0) THEN |
|---|
| 157 | | pre = 'geometryN('; |
|---|
| 158 | | post = ' , 1)'; |
|---|
| 159 | | END IF; |
|---|
| 160 | | END LOOP; |
|---|
| 161 | | |
|---|
| 162 | | -- FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,' |
|---|
| 163 | | -- || ' startPoint(' || pre || quote_ident(geo_cname) || post || ') AS source,' |
|---|
| 164 | | -- || ' endPoint(' || pre || quote_ident(geo_cname) || post || ') as target' |
|---|
| 165 | | -- || ' FROM ' || quote_ident(geom_table) loop |
|---|
| 166 | | -- |
|---|
| 167 | | FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,' |
|---|
| 168 | | || ' PointN('|| quote_ident(geo_cname) ||', 1) AS source,' |
|---|
| 169 | | || ' PointN('|| quote_ident(geo_cname) ||', NumPoints('|| quote_ident(geo_cname) ||')) as target' |
|---|
| 170 | | || ' FROM ' || quote_ident(geom_table) || ' ORDER BY ' || quote_ident(gid_cname) loop |
|---|
| 171 | | |
|---|
| 172 | | IF points.id%10=0 THEN |
|---|
| 173 | | RAISE NOTICE '% out of % edges processed', points.id, countids; |
|---|
| 174 | | END IF; |
|---|
| 175 | | |
|---|
| 176 | | source_id := point_to_id(setsrid(points.source, srid), tolerance); |
|---|
| 177 | | target_id := point_to_id(setsrid(points.target, srid), tolerance); |
|---|
| 178 | | |
|---|
| 179 | | |
|---|
| 180 | | EXECUTE 'update ' || quote_ident(geom_table) || |
|---|
| 181 | | ' SET source = ' || source_id || |
|---|
| 182 | | ', target = ' || target_id || |
|---|
| 183 | | ' WHERE ' || quote_ident(gid_cname) || ' = ' || points.id; |
|---|
| 184 | | END LOOP; |
|---|
| 185 | | |
|---|
| 186 | | RETURN 'OK'; |
|---|
| 187 | | |
|---|
