[postgis-users] Slow Postgres Query

[Paragon Corporation]

You should use ST_DWithin if you can.  That select limit 1 will be really
slow with a large table.

 
(SELECT gid
	from twothousandlines
		WHERE ST_DWithin(twothousandlines.geom,sixtypoints.geom,
some_reasonable_buffer_dist)
	order by st_distance(twothousandlines.geom,sixtypoints.geom)
	limit 1
)

Leo

-----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 4:24 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Slow Postgres Query

Thanks Stanly! So to make it a single step I guess...

create table lines_from_points_and_points_projected_onto_centerlines as
select gid as point_id, (SELECT gid
	from twothousandlines
	order by st_distance(twothousandlines.geom,sixtypoints.geom)
	limit 1
) as line_id,
(SELECT
st_makeline(sixtypoints.geom,st_line_interpolate_point(st_geometryn(twothous
andlines.geom,1),st_line_locate_point(st_geometryn(twothousandlines.geom,1),
sixtypoints.geom)))
	from twothousandlines
	order by st_distance(twothousandlines.geom,sixtypoints.geom)
	limit 1
) as geom
from sixtypoints




-Eric



Eric Randall

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net]On Behalf Of
Sufficool, Stanley
Sent: Friday, October 02, 2009 3:32 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Slow Postgres Query


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
> 
_______________________________________________
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