Cool! I need to rethink the way I do things! -Eric
Eric Randall -----Original Message----- From: [email protected] [mailto:[email protected]]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: [email protected] > [mailto:[email protected]] 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: [email protected] > [mailto:[email protected]]on > Behalf Of dassouki > Sent: Thursday, October 01, 2009 11:38 AM > To: [email protected] > 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 [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
