Forum #23 - Topic #5 - Message List

Hi, I've made some modifications to this function in the past. This function use the srid stored in the geometry_column table. If you prefer to use something like GeometryType?(<my_geom>), then you could simply modify the function defined here after.

You'll note that there is also some other modifications which imply that you could use linestring rather than multilinestring to store roads.

There are also two more parameters to this funtion which let its users define the geometry column name and the identifer one. thos parameters could be made optional.

Here is the definition I currently use :

CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table varchar, 
       tolerance double precision,
	   geo_cname varchar,
	   gid_cname varchar)
       RETURNS VARCHAR AS
$$
DECLARE
	points record;
	i record;
	source_id int;
	target_id int;
	pre varchar;
	post varchar;

BEGIN

	BEGIN
		DROP TABLE vertices_tmp;
	EXCEPTION 
		WHEN UNDEFINED_TABLE THEN
	END;

	CREATE TABLE vertices_tmp ( id serial );	

	EXECUTE $q$ SELECT addGeometryColumn('vertices_tmp', 'the_geom', 
                                             (SELECT srid FROM geometry_columns WHERE f_table_name='$q$ || quote_ident(geom_table) || $q$') , 'POINT', 2) $q$;

	CREATE INDEX vertices_tmp_idx ON vertices_tmp USING GIST (the_geom);

        pre = '';
	post = '';
	FOR i in EXECUTE 'SELECT count(*) as t from ' || quote_ident(geom_table) || ' WHERE NumGeometries(' || quote_ident(geo_cname) || ') is not null'  loop
		IF (i.t > 0) THEN
			pre = 'geometryN(';
			post = ' , 1)';
		END IF;
	END LOOP;
	
	FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
		|| ' startPoint(' || pre || quote_ident(geo_cname) || post || ') AS source,'
		|| ' endPoint(' || pre || quote_ident(geo_cname) || post || ') as target'
		|| ' FROM ' || quote_ident(geom_table) loop

		source_id := point_to_id(points.source, tolerance);
		target_id := point_to_id(points.target, tolerance);

		EXECUTE 'update ' || quote_ident(geom_table) || 
                   ' SET source_id = ' || source_id || 
                   ', target_id = ' || target_id || 
                   ' WHERE ' || quote_ident(gid_cname) || ' =  ' || points.id;
	END LOOP;

	RETURN 'OK';
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT; 

ps: I've put some lines of code here but I don't know if it's appropriate. If it's not the right place, please tell me and forgive me.