[postgis-users] Slow Postgres Query

[Randall, Eric]

Hi there dassouki,

Right, no way that should take so long.  I created 60 point and 2000 line tables from addresses and road centerlines
to do a test.  The tables are called "sixtypoints" and "twothousandlines".  Then created a "linespoints" table to store point and nearest line ids:

/*
create table linespoints (
point_id integer,
nearest_line_id integer );
*/

Then created a function to insert the point and nearest line ids:

/*
create or replace function insert_into_linespoints() returns integer as $$

	declare
		
        getrow cursor for select * from sixtypoints;
        
        point_row sixtypoints%rowtype;
        
        v_nearest_line_id integer;     
        max_rows integer;
	  rowcount integer;
		
	begin

	  delete from  linespoints;
	
        select into max_rows count(*) from sixtypoints;  

	  open getrow;
        rowcount := 0;
        
        while rowcount < max_rows LOOP   --of course, could have just said 60
               
        fetch getrow into point_row;
        rowcount := rowcount + 1;
        
        select into v_nearest_line_id 
        twothousandlines.gid
        from  twothousandlines
        order by st_distance(twothousandlines.geom,point_row.geom) 
        limit 1;        

        insert into linespoints values (point_row.gid, v_nearest_line_id );
	   
        end loop;
	  close getrow;
	  return(rowcount);
		
     end;

$$ language plpgsql

*/


Then ran it:

/*
select insert_into_linespoints()   --389ms
*/

Then made lines from info in the three tables: 

/*
select st_makeline(sixtypoints.geom,st_line_interpolate_point(st_geometryn(twothousandlines.geom,1),st_line_locate_point(st_geometryn(twothousandlines.geom,1),sixtypoints.geom)))
from twothousandlines, sixtypoints, linespoints
where sixtypoints.gid = linespoints.point_id
and twothousandlines.gid = linespoints.nearest_line_id
*/
--12ms


In retrospect, I probably would add a geometry column to the "linespoints" table and insert the line during the function rather than having the third step.  I didn't understand why you needed to "explode" to linestrings as you called it but I guess it has to do with your data.  The "order by st_distance() ... limit 1"  is what you needed mainly I think to give you each single nearest line.


Hopefully this is what you're trying to do.


Eric



Eric Randall

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of
dassouki
Sent: Thursday, October 01, 2009 11:38 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Slow Postgres Query


Hey All,

As some of you know, I've been trying to connect a point from a point 
table to the projection of that point on the nearest line for close to 
forever now.

I've managed to get the code working for a small set of points and 
lines; however, on a 60 node with 2,000 lines, the query is in its 11th 
hour and still no sign of ending.

I posted a question including code on stack overflow 
http://stackoverflow.com/questions/1504353/slow-postgres-query

I was wondering if anyone here has any idea on what I might be doing 
wrong ? and if there is a way to make the code more efficient


cheers,
dassouki


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users