[postgis-users] Slow Postgres Query

[Sufficool, Stanley]

I usually avoid cursors when possible:

insert into linespoints (point_id, nearest_line_id)
select gid, (
	SELECT gid
	from twothousandlines
	order by st_distance(twothousandlines.geom,sixtypoints.geom)
	limit 1
)
from sixtypoints


> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Randall, Eric
> Sent: Friday, October 02, 2009 10:20 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Slow Postgres Query
> 
> 
> 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_geom
> etryn(twothousandlines.geom,1),st_line_locate_point(st_geometr
> yn(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
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>