Florian,
Two points
1) You have a cartesian product here which is very slow with the tab_point
point, tab_line line but it sounds like from your description of your desired
result that may be intentional but its hard to tell. Just thought I would
point it out.
I'm thinking you are looking for
"All points whose closest line is > 50 meters away" which doesn't require a
slow cartesian product.
But I could read your question a couple of ways.
2)
In general NOT IN is intuitive but much slower than doing a left join. Also I
think you want to do a compound check. Try doing the following instead
The below query should give you "all points who are > 50 meters away from the
closest starting point the psuedo closest line". Note I added an Expand of a
percent of a degree because it is not guaranteed your point will be in the
bounding box of the line and can still be within 50 meters but take that out if
you want. Also just going by the startpoint is faulty - it might be better to
use Centroid() instead of startpoint or you could do (centroid or startpoint or
endpoint) combination check - see the second example below.
If you could transform to a non-degree projection, that would be a lot more
efficient too and then you can use distance which would give you distance from
closest point on the line.
SELECT distinct point.field1, point.field2
FROM tab_point point LEFT JOIN
(SELECT point.field1 as pfield1, point.field2 as pfield2 FROM
tab_point point, tab_line line WHERE
Expand(line.the_geom, 0.01) && point.the_geom AND
DISTANCE_SPHEROID(point.the_geom, StartPoint(line.the_geom), 'SPHEROID["WGS
84",6378137,298.257223563]') < 50.00) As ce
ON (ce.pfield1 = point.field1 and ce.pfield2 = point.field2)
WHERE ce.pfield1 IS NULL;
---What I mean by combination check
SELECT distinct point.field1, point.field2
FROM tab_point point LEFT JOIN
(SELECT point.field1 as pfield1, point.field2 as pfield2 FROM
tab_point point, tab_line line WHERE
Expand(line.the_geom, 0.01) && point.the_geom AND
(DISTANCE_SPHEROID(point.the_geom, StartPoint(line.the_geom), 'SPHEROID["WGS
84",6378137,298.257223563]') < 50.00 OR DISTANCE_SPHEROID(point.the_geom,
Centroid(line.the_geom), 'SPHEROID["WGS 84",6378137,298.257223563]') < 50.00 OR
DISTANCE_SPHEROID(point.the_geom, EndPoint(line.the_geom), 'SPHEROID["WGS
84",6378137,298.257223563]') < 50.00) ) As ce
ON (ce.pfield1 = point.field1 and ce.pfield2 = point.field2)
WHERE ce.pfield1 IS NULL;
Caveat - the above examples wors reliably only if field1 and field2 are never
null of your point table are never null. It would be better to use the primary
key of the table.
________________________________
From: [EMAIL PROTECTED] on behalf of Reichle, Florian
Sent: Mon 9/17/2007 11:04 AM
To: [email protected]
Subject: [postgis-users] Question: How can I improve the performance of
thefunction DISTANCE_SPHERE?
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
