developer (#13) - New implementation of assign_vertex_id and point_to_id (#189) - Message List

Hi,

I've been using pgrouting and postgis for a VERY short time, so I am not an expert in postgis neither in pgrouting.

However I was very bored when I had to wait 1 hour to assing_vertex_id, stops processing, so I implemented a custom version of the two involved functions.

Now I can run assign_vertex_id in 2 minutes (50000 points).

Here are the implementation... Remember, I'm completely new to postgis, and I don't know how to use spatial indexes. But I think, the idea is clear.

Cheers.

-- Function: point_to_id(geometry, double precision)
-- DROP FUNCTION point_to_id(geometry, double precision);
CREATE OR REPLACE FUNCTION point_to_id(point geometry, tolerance double precision)
  RETURNS integer AS
$BODY$
DECLARE
	row record;
	point_id int;
	xd1 double precision;
	xd2 double precision;
	yd1 double precision;
	yd2 double precision;
BEGIN
	LOOP
	    xd1 := x(point) - tolerance/2;
	    xd2 := x(point) + tolerance/2;
	    yd1 := y(point) - tolerance/2;
	    yd2 := y(point) + tolerance/2;
		-- TODO: use && and index
		SELECT INTO row id, the_geom FROM vertices_tmp
		      WHERE x > xd1 AND x < xd2
		        AND y > yd1 AND y < yd2
                        AND distance(the_geom, point) < tolerance;
		point_id := row.id;
		IF NOT FOUND THEN
			INSERT INTO vertices_tmp (the_geom,x,y) VALUES (point, x(point), y(point));
		ELSE
			EXIT;
		END IF;
	END LOOP;
	RETURN point_id;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT;
ALTER FUNCTION point_to_id(geometry, double precision) OWNER TO postgres;
---------------------------------------------------------
-- Function: assign_vertex_id(character varying, double precision, character varying, character varying)
-- DROP FUNCTION assign_vertex_id(character varying, double precision, character varying, character varying);
CREATE OR REPLACE FUNCTION assign_vertex_id(geom_table character varying, tolerance double precision, geo_cname character varying, gid_cname character varying)
  RETURNS character varying AS
$BODY$
DECLARE
      points record;
      i record;
      source_id int;
      target_id int;
      pre varchar;
      post varchar;
      srid integer;
      countids integer;
      BEGIN
	    BEGIN
	        DROP TABLE vertices_tmp;
        	EXCEPTION
			WHEN UNDEFINED_TABLE THEN
                END;
		EXECUTE 'CREATE TABLE vertices_tmp (id serial, x double precision, y double precision)';
		EXECUTE 'CREATE INDEX vertices_tmp_idx_x ON vertices_tmp using BTREE(x)';
        EXECUTE 'CREATE INDEX vertices_tmp_idx_y ON vertices_tmp using BTREE(y)';
		FOR i IN EXECUTE 'SELECT srid FROM geometry_columns WHERE f_table_name='''|| quote_ident(geom_table)||'''' LOOP
		END LOOP;
		srid := i.srid;
		FOR i IN EXECUTE 'SELECT count(*) as countids FROM '|| quote_ident(geom_table) LOOP
		END LOOP;
		countids := i.countids;
		EXECUTE 'SELECT addGeometryColumn(''vertices_tmp'', ''the_geom'', '||srid||', ''POINT'', 2)';
                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
--
		FOR points IN EXECUTE 'SELECT ' || quote_ident(gid_cname) || ' AS id,'
			|| ' PointN('|| quote_ident(geo_cname) ||', 1) AS source,'
			|| ' PointN('|| quote_ident(geo_cname) ||', NumPoints('|| quote_ident(geo_cname) ||')) as target'
			|| ' FROM ' || quote_ident(geom_table) || ' ORDER BY ' || quote_ident(gid_cname) loop
			IF points.id%10=0 THEN
			  RAISE NOTICE '% out of % edges processed', points.id, countids;
			END IF;
				source_id := point_to_id(setsrid(points.source, srid), tolerance);
				target_id := point_to_id(setsrid(points.target, srid), tolerance);
				EXECUTE 'update ' || quote_ident(geom_table) ||
				    ' SET source = ' || source_id ||
                        	    ', target = ' || target_id ||
                                    ' WHERE ' || quote_ident(gid_cname) || ' =  ' || points.id;
                END LOOP;
RETURN 'OK';
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE STRICT;
ALTER FUNCTION assign_vertex_id(character varying, double precision, character varying, character varying) OWNER TO postgres;
}}