[postgis-users] Fucntion that find if the x, y, z for any points in the table are the same

[Kevin Neufeld]

Hi ihab,

This function is from the pgRouting package, right?  The function below 
is actually not creating an id for every point in a line.  Rather, it is 
creating an id just for the endpoints of the line.  In this manner, 
pgRouting is trying to build a node network it can traverse in order to 
perform dijkstra's shortest path algorithm.  (I've always thought this 
function should be named assign_node_id ... oh well).

Towards the end of the function, you'll see that the startpoints and 
endpoints of the line are passed into the function point_to_id(geom, 
tolerance) function.  I think that it's in this function you should be 
focusing your efforts, not this function.

The point_to_id function currently looks like this in the latest build.
CREATE OR REPLACE FUNCTION point_to_id(point geometry, tolerance double 
precision)
   RETURNS integer AS
$BODY$
DECLARE
	row record;
	point_id int;
BEGIN
	LOOP
		-- TODO: use && and index	
		SELECT INTO row id, the_geom FROM vertices_tmp WHERE
                    distance(the_geom, point) < tolerance;

		point_id := row.id;

		IF NOT FOUND THEN
			INSERT INTO vertices_tmp (the_geom) VALUES (point);
		ELSE
			EXIT;
		END IF;
	END LOOP;
	RETURN point_id;
END;
$BODY$
   LANGUAGE 'plpgsql' VOLATILE STRICT
   COST 100;

You'll want to change the filter on the SQL query from "WHERE 
distance(the_geom, point) < tolerance" to something else (this one 
currently doesn't use any index operator nor does it compute in 3D - 
distance(geom, geom) does not currently use 3D values).  I think it's 
here you could compute the 3D distance between two points.  If the 
distance3D is less than some tolerance, they could be considered the same.

Hope that helps.
Cheers,
Kevin


eehab hamzeh wrote:
 > Hello,
 >
 > I see your direction for index in 3d ... i have the function below which
 > creating  id for points of line. it assign the same id for points that
 > have same x,y coordinates... i need it consider the z value ... i have
 > no experience with plgsql can you please help me to develop it to
 > consider the z values and assign the same id for points has same x,y,z
 >
 > Thanks
 > ihab
 >
 >
 >
 >
eehab hamzeh wrote:
> Hello,
>  
> I have the following function which check if two (or more) points are 
> one, the function just consider the x,y coordinates. can any body help 
> me to modify it to check z-index also.
>  
> Thanks
>  
> Ihab
>  
> below is the function
>  
> -- 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;
> 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', 4) $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?_ 
> <http://pgrouting.postlbs.org/wiki/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; $BODY$
> LANGUAGE 'plpgsql' VOLATILE STRICT COST 100;
> ALTER FUNCTION assign_vertex_id(character varying, double precision, 
> character varying, character varying) OWNER TO postgres;
> 
> ------------------------------------------------------------------------
> Discover the new Windows Vista Learn more! 
> <http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE>
> ------------------------------------------------------------------------
> Connect to the next generation of MSN Messenger  Get it now! 
> <http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmailtagline>
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users