improving within()

[Marco Lechner - FOSSGIS e.V.]

hi list,

we are using within(geometry, geometry) to test if some (~1200) points
are within a polygon. the polygon is defined as buffer(linegeometry,
dist). the problem is, that the line is always the same and therefore
the result of buffer(linegeometry, dist)  is always the same. while
iterating through the points the within() calculates the
buffer(linegeometry) each time again. the function takes 40s. instead of
0.04s if i calculate the buffergeometry first and use it as "fixed
geometry" at within() - is this somehow improvable (within() recognizing
such a behaviour?, ...)?

Marco
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Suhr, Ralf]

What kind of SQL do you use?

without index scan:
SELECT point
FROM point_table
WHERE ST_Within(point, (SELECT ST_Buffer(line,5km) FROM line_table));

OR with index scan:
SELECT point
FROM point_table p
  RIGHT JOIN
  (
    SELECT ST_Buffer(line,5km)
    FROM line_table
  ) AS b ON (ST_Within(p.way,b.way));

--
Ralf Suhr

IT-Consult Halle GmbH
Bornknechstraße 5
06108 Halle (Saale)

-----Ursprüngliche Nachricht-----
Von: postgis-users-bounces@... [mailto:postgis-users-bounces@...] Im Auftrag von Marco Lechner - FOSSGIS e.V.
Gesendet: Montag, 29. Juni 2009 17:47
An: PostGIS Users Discussion
Betreff: [postgis-users] improving within()


hi list,

we are using within(geometry, geometry) to test if some (~1200) points are within a polygon. the polygon is defined as buffer(linegeometry, dist). the problem is, that the line is always the same and therefore the result of buffer(linegeometry, dist)  is always the same. while iterating through the points the within() calculates the
buffer(linegeometry) each time again. the function takes 40s. instead of 0.04s if i calculate the buffergeometry first and use it as "fixed geometry" at within() - is this somehow improvable (within() recognizing such a behaviour?, ...)?

Marco
_______________________________________________
postgis-users mailing list postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Fred Lehodey]

Hi Marco,

ST_DWithin(geometry, geometry, float)

Returns true if geometries are within the specified distance of one another. Uses indexes if available.

HTH,
Fred

On Mon, Jun 29, 2009 at 4:46 PM, Marco Lechner - FOSSGIS e.V. <marco.lechner@...> wrote:

hi list,

we are using within(geometry, geometry) to test if some (~1200) points
are within a polygon. the polygon is defined as buffer(linegeometry,
dist). the problem is, that the line is always the same and therefore
the result of buffer(linegeometry, dist)  is always the same. while
iterating through the points the within() calculates the
buffer(linegeometry) each time again. the function takes 40s. instead of
0.04s if i calculate the buffergeometry first and use it as "fixed
geometry" at within() - is this somehow improvable (within() recognizing
such a behaviour?, ...)?

Marco
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paul Ramsey-4]

Fred's got it, listen to Fred!!!

(Never build a buffer if you don't need to.)

On Mon, Jun 29, 2009 at 9:16 AM, Fred Lehodey<lehodey@...> wrote:

> Hi Marco,
> ST_DWithin(geometry, geometry, float)
>
> Returns true if geometries are within the specified distance of one another.
> Uses indexes if available.
>
> HTH,
> Fred
>
>
>
>
> On Mon, Jun 29, 2009 at 4:46 PM, Marco Lechner - FOSSGIS e.V.
> <marco.lechner@...> wrote:
>>
>> hi list,
>>
>> we are using within(geometry, geometry) to test if some (~1200) points
>> are within a polygon. the polygon is defined as buffer(linegeometry,
>> dist). the problem is, that the line is always the same and therefore
>> the result of buffer(linegeometry, dist)  is always the same. while
>> iterating through the points the within() calculates the
>> buffer(linegeometry) each time again. the function takes 40s. instead of
>> 0.04s if i calculate the buffergeometry first and use it as "fixed
>> geometry" at within() - is this somehow improvable (within() recognizing
>> such a behaviour?, ...)?
>>
>> Marco
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@...
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users