<HTML dir=ltr><HEAD><TITLE>[postgis-users] Question: How can I improve the performance of thefunction DISTANCE_SPHERE?</TITLE>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3157" name=GENERATOR></HEAD>
<BODY>
<DIV id=idOWAReplyText79152 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Florian,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>Two points </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>1) You have a cartesian product here which is very slow with the <FONT face="Times New Roman">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. </FONT></FONT><FONT face=Arial size=2><FONT face="Times New Roman"> Just thought I would point it out.</FONT></FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>I'm thinking you are looking for </FONT></DIV>
<DIV dir=ltr>
<DIV dir=ltr><FONT face=Arial><FONT face="Times New Roman" size=2>"All points whose closest line is > 50 meters away" which doesn't require a slow cartesian product.</FONT></FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>But I could read your question a couple of ways.</FONT></DIV></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><FONT size=2>2)</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=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</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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. </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>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. </FONT></DIV></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><FONT size=2>SELECT distinct point.field1, point.field2</FONT></DIV>
<DIV dir=ltr><FONT size=2>FROM tab_point point LEFT JOIN </FONT></DIV>
<DIV dir=ltr><FONT size=2> (SELECT point.field1 as pfield1, point.field2 as pfield2 FROM tab_point point, tab_line line WHERE<BR> Expand(line.the_geom, 0.01) && point.the_geom AND<BR>DISTANCE_SPHEROID(point.the_geom, StartPoint(line.the_geom), 'SPHEROID["WGS 84",6378137,298.257223563]') < 50.00) As ce </FONT></DIV>
<DIV dir=ltr><FONT size=2> ON (ce.pfield1 = point.field1 and ce.pfield2 = point.field2)</FONT></DIV>
<DIV dir=ltr><FONT size=2>WHERE ce.pfield1 IS NULL</FONT><FONT size=2>;</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2>---What I mean by combination check</FONT></DIV>
<DIV dir=ltr><FONT size=2></FONT><FONT size=2></FONT> </DIV>
<DIV dir=ltr>
<DIV dir=ltr><FONT size=2>SELECT distinct point.field1, point.field2</FONT></DIV>
<DIV dir=ltr><FONT size=2>FROM tab_point point LEFT JOIN </FONT></DIV>
<DIV dir=ltr><FONT size=2> (SELECT point.field1 as pfield1, point.field2 as pfield2 FROM tab_point point, tab_line line WHERE<BR> Expand(line.the_geom, 0.01) && point.the_geom AND<BR>(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 </FONT></DIV>
<DIV dir=ltr><FONT size=2> ON (ce.pfield1 = point.field1 and ce.pfield2 = point.field2)</FONT></DIV>
<DIV dir=ltr><FONT size=2>WHERE ce.pfield1 IS NULL</FONT><FONT size=2>;</FONT></DIV></DIV>
<P><FONT size=2></FONT> </P>
<P><FONT size=2>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.<BR></P></FONT>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr><FONT size=2></FONT> </DIV>
<DIV dir=ltr>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr><FONT face=Tahoma size=2><B>From:</B> postgis-users-bounces@postgis.refractions.net on behalf of Reichle, Florian<BR><B>Sent:</B> Mon 9/17/2007 11:04 AM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Question: How can I improve the performance of thefunction DISTANCE_SPHERE?<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Hi,<BR><BR>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.<BR>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?<BR><BR>Here my example:<BR><BR>SELECT distinct point.field1, point.field2, line.field1, line.field2 FROM tab_point point, tab_line line WHERE<BR>point.field1 NOT IN (SELECT distinct point.field1, point.field2, line.field1, line.field2 FROM tab_point point, tab_line line WHERE<BR>point.the_geom && line.the_geom AND<BR>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 );<BR><BR>Greetz<BR><BR><BR><BR><BR><BR>_______________________________________________<BR>postgis-users mailing list<BR>postgis-users@postgis.refractions.net<BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></FONT></P></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>