[postgis-users] Postgis query slowing down with polygon bounds

[Rob Young]

That did it.  Thanks Mark!

On 1/23/07, Mark Cave-Ayland <mark.cave-ayland at ilande.co.uk> wrote:
> On Tue, 2007-01-23 at 10:29 +1300, Rob Young wrote:
> > I developed a pL/pgsql function that creates a bounding box for
> > lat/long data.  Given a latitude, long and distance in metres, it
> > returns a box that bounds a circle at that point with a radius equal
> > to the distance parameter.  Here's an example:
> >
> > select AsText(expand_sphere( 5, 3, 600 * 1000 ));
> >
> > "POLYGON((8.41054153442383 -0.389891535043716,8.41054153442383
> > 10.3898906707764,-2.41054105758667 10.3898906707764,-2.41054105758667
> > -0.389891535043716,8.41054153442383 -0.389891535043716))"
> >
> > However, when I use this in  a query (below) in my testing database,
> > it runs significantly slower than when I run the query without
> > bounding the data using expand_sphere.
> >
> >
> > select *, astext(point) from test
> > WHERE
> > point && expand_sphere( 5, 3, 600 * 1000 ) AND
> > distance_sphere( setsrid( makepoint( 3, 5 ), 4326 ), point ) < 600 * 1000;
> >
> >
> > This has me puzzled, any suggestions as to what I should change to
> > speed up this query?  The table test has a one geometry column, point,
> > which contains point data.  There is a GIST index on point, and I
> > anazlysed the database after adding the index.
> >
> > Also, if there's interest I would be happy to share my expand_sphere code.
> >
> > Regards
> > Robert Young  :-)
>
>
> Hi Robert,
>
> Did you mark your expand_sphere() function as IMMUTABLE as part of its
> CREATE FUNCTION definition? If not, the PostgreSQL planner assumes that
> the function is VOLATILE and hence will not optimise the result as a
> constant.
>
> http://www.postgresql.org/docs/8.2/interactive/sql-createfunction.html
>
>
> Kind regards,
>
> Mark.
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>