[postgis-users] Slow Postgres Query

[dassouki]

Emilie Laffray wrote:
>
>
> 2009/10/1 dassouki <dassouki at gmail.com <mailto:dassouki at gmail.com>>
>
>             / 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
>     />>/
>     />>/
>     /> Hum, I am no expert but it looks like your 3000000 is way too
>     high.
>     I tried 1, 100, and 10000 and it didn't seem to speed up the query
>
>
> Ok but what is your SRID? You have to pick a meaningful value based on 
> your SRID coordinates, with a reasonable amount of slices. It takes a 
> bit of trial of error to find the sweet spot for your dataset.
> It depends a lot on the density of points etc...
>
I'm using SRID 32767 since the input prj file has the following proj:
PROJCS["NAD_1983_CSRS98_New_Brunswick_Stereographic",GEOGCS["GCS_North_American_1983_CSRS98",DATUM["D_North_American_1983_CSRS98",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Double_Stereographic"],PARAMETER["False_Easting",2500000.0],PARAMETER["False_Northing",7500000.0],PARAMETER["Central_Meridian",-66.5],PARAMETER["Scale_Factor",0.999912],PARAMETER["Latitude_Of_Origin",46.5],UNIT["Meter",1.0]]

The area is about 150 sq km, i have roughly 90 nodes, and 2,100 lines
> Emilie Laffray
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>