[postgis-users] Question: How can I improve the performance of the function DISTANCE_SPHERE?

[Reichle, Florian]

Hi,

I use the function DISTANCE_SPHERE to get a metric unit how far is a point away from a polygon. I have two tables, one for the points and one for the polygons. The polygons are MULTILINES.
The result of the query must be a list of all points, which are to far a away from any polygon. So I reduce in the first step the list with && which drop all points which are intersects with the geom of any polygon. After that i must proof if the distance of the rest to far away. Therefor I use the DISTANCE_SPHERE function. But at here I have the problem with the speed of the query, because the function take to much time to compare alle elements in the tables. Have someone any better idea?

Here my example:

SELECT distinct point.field1, point.field2, line.field1, line.field2 FROM tab_point point, tab_line line WHERE 
point.field1 NOT IN (SELECT distinct point.field1, point.field2, line.field1, line.field2 FROM tab_point point, tab_line line WHERE 
point.the_geom && line.the_geom AND 
DISTANCE_SPHEROID(point.the_geom, StartPoint(line.the_geom), 'SPHEROID["WGS 84",6378137,298.257223563]') < 50.00 GROUP BY point.field1, point.field2, line.field1, line.field2 from );

Greetz