<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML dir=ltr><HEAD><TITLE>postgis-users Digest, Vol 59, Issue 17</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=447502916-24092007><FONT face=Arial
color=#0000ff size=2>What area does your data cover? Does it cover the
whole world or just a limited region like a country? If you can transform
to a meter based projection, that would be the most efficient. Taht won't
work unfortunately if you need to cover the whole world. In which case you
may want to dissect your data into different quadrants and project each
separately.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=447502916-24092007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=447502916-24092007><FONT face=Arial
color=#0000ff size=2>Also would be helpful if you could show us the Explain
analyze of your query.</FONT></SPAN></DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2>something like</FONT></SPAN></DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=447502916-24092007><SPAN class=447502916-24092007><FONT
face=Arial color=#0000ff size=2>EXPLAIN SELECT ... if explain
analyze takes too long.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=447502916-24092007><SPAN class=447502916-24092007><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=447502916-24092007><SPAN class=447502916-24092007><FONT
face=Arial color=#0000ff size=2>or </FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=447502916-24092007><SPAN class=447502916-24092007><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2>EXPLAIN ANALYZE SELECT .....</FONT></SPAN></DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff size=2>So we
can see what indexes it is using etc. EXPLAIN Analyze would be more
informative, but sounds like it would take too long.</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff size=2>Hope
that helps,</FONT></SPAN></DIV>
<DIV><SPAN class=447502916-24092007><FONT face=Arial color=#0000ff
size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2></FONT><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Reichle, Florian<BR><B>Sent:</B> Monday, September 24, 2007 12:19
PM<BR><B>To:</B> postgis-users@postgis.refractions.net<BR><B>Subject:</B> RE:
Question: How can I improve the performance of the function
DISTANCE_SPHERE?<BR></FONT><BR></DIV>
<DIV></DIV>
<P><FONT size=2>Hi,</FONT></P>
<P><FONT size=2>i have tried this at my DB, but the query is even
slow:<BR><BR>SELECT distinct point.field1, point.field2<BR>FROM tab_point point
LEFT JOIN<BR>
(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 <BR> ON
(ce.pfield1 = point.field1 and ce.pfield2 = point.field2)<BR>WHERE ce.pfield1 IS
NULL;<BR></FONT><BR><FONT size=2><EM>I'm thinking you are looking for<BR>"All
points whose closest line is > 50 meters away" which doesn't require a slow
cartesian product.</EM> --> Yes, that's right. I' am searching for all points
whose closest line is > 50 meters. But how is the trick do not use
the<BR>cartesian product?<BR><BR>I have many datas in the DB and it takes moren
than 10 hours. </FONT></P>
<P><FONT size=2><BR>Date: Mon, 17 Sep 2007 15:38:08 -0400<BR>From: "Obe, Regina"
<robe.dnd@cityofboston.gov><BR>Subject: RE: [postgis-users] Question: How
can I improve the<BR> performance
of thefunction DISTANCE_SPHERE?<BR>To: "PostGIS Users Discussion"
<postgis-users@postgis.refractions.net><BR>Message-ID:<BR>
<53F9CF533E1AA14EA1F8C5C08ABC08D201979F8A@ZDND.DND.boston.cob><BR>Content-Type:
text/plain; charset="iso-8859-1"<BR><BR>Florian,<BR><BR>Two points<BR>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.<BR><BR>I'm thinking you are looking for<BR>"All points whose
closest line is > 50 meters away" which doesn't require a slow cartesian
product.<BR><BR>But I could read your question a couple of ways.<BR><BR>2)<BR>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<BR><BR><BR>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. <BR><BR>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. <BR><BR>SELECT distinct
point.field1, point.field2<BR>FROM tab_point point LEFT
JOIN<BR>
(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 <BR> ON
(ce.pfield1 = point.field1 and ce.pfield2 = point.field2)<BR>WHERE ce.pfield1 IS
NULL;<BR><BR>---What I mean by combination check<BR><BR>SELECT distinct
point.field1, point.field2<BR>FROM tab_point point LEFT
JOIN<BR>
(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 <BR> ON
(ce.pfield1 = point.field1 and ce.pfield2 = point.field2)<BR>WHERE ce.pfield1 IS
NULL;<BR><BR><BR><BR>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><BR><BR><BR><BR>________________________________<BR><BR>From:
postgis-users-bounces@postgis.refractions.net on behalf of Reichle,
Florian<BR>Sent: Mon 9/17/2007 11:04 AM<BR>To:
postgis-users@postgis.refractions.net<BR>Subject: [postgis-users] Question: How
can I improve the performance of thefunction
DISTANCE_SPHERE?<BR><BR><BR><BR>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</FONT></P></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>