<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<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=902462813-05112007><FONT face=Arial
color=#0000ff size=2>Matt,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2>The expand function creates a bbox that expands out mmd
units. So the EXPAND && basically limits your search to only those
cities that fall within the expand box and since && is an indexable
operator, the search is indexable. The only issue with the EXPAND is that
you have to guess at an expand box that will cover all your cities (e.g.
guarantee that no near result would fall outside your EXPAND guess) and yet
limit the size of your expand box to something that doesn't trap too many fish
so to speak. the more fish you trap, the more checks your distance
function has to do.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2>One way to guess at an expand is to do the following query
(start fairly small) and keep on increasing the size of your expand until
you get no results. The exception query I think runs fairly fast since it
doesn't do the costly distance check.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
size=2> SELECT <BR> c1.city_name
<BR> FROM<BR> city c1<BR> LEFT
JOIN<BR> city c2<BR> ON
(<BR> c1.city_name <>
c2.city_name AND EXPAND(c1.the_geom, 10000) && c2.the_geom
<BR> ) <BR>WHERE c2.city_name IS
NULL</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2>You know I completely forgot about using DISTINCT ON to do
more than one neighbor search for a set of records at a time. I guess I
don't use that much. So I second Paul's comment "very nice
query".</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=902462813-05112007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV><BR>
<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>Matthew Pulis<BR><B>Sent:</B> Sunday, November 04, 2007 7:21
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users]
Shortest Distance from Every Point<BR></FONT><BR></DIV>
<DIV></DIV>Can you please explain further why u used the EXPAND? Didn't much get
what is its use? And is mmd a thing which has to do with PostGis ?<BR><BR>
<DIV><SPAN class=gmail_quote>On 11/4/07, <B class=gmail_sendername>Paul Ramsey
</B><<A href="mailto:pramsey@refractions.net">pramsey@refractions.net</A>>
wrote:</SPAN>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #cccccc 1px solid"><BR>Well,
you have to build the cartesian product of every city<BR>combination and then
measure every distance in that virtual table, so<BR>it's not going to scale
well at all as the input table gets bigger.<BR><BR>However, if you know the
"maximum minimum distance" (?mmd?) you can <BR>add a spatial constraint that
should at least keep the calculations<BR>in the O(n*log(n)) range... (you'll
need a spatial index on the table<BR>for best effect as the table gets
larger)<BR><BR>SELECT DISTINCT ON (c1) <BR>
c1.city_name AS "c1",<BR> c2.city_name AS
"c2",<BR> distance(c1.the_geom,
c2.the_geom),<BR> makeline(c1.the_geom,
c2.the_geom)<BR>FROM<BR> city
c1<BR>JOIN<BR> city c2<BR> ON
( <BR> c1.city_name <>
c2.city_name AND<BR>
c1.the_geom && ST_Expand(c2.the_geom,
?mmd?)<BR> )<BR>ORDER BY c1, distance
ASC<BR>;<BR><BR>Paul<BR><BR>PS - Nice query, BTW.<BR><BR>On 4-Nov-07, at 9:15
AM, Yancho wrote: <BR><BR>><BR>> Just wanted to say that I managed to
write this Query :<BR>><BR>> SELECT DISTINCT ON
(c1)<BR>> c1.city_name AS
"c1",<BR>> c2.city_name AS
"c2",<BR>> distance( c1.the_geom,
c2.the_geom),<BR>> makeline(c1.the_geom,
c2.the_geom)<BR>> FROM<BR>> city c1<BR>>
JOIN<BR>> city c2<BR>>
ON (<BR>> c1.city_name
<> c2.city_name<BR>> ) <BR>> ORDER BY c1,
distance ASC<BR>> ;<BR>><BR>> It works perfectly, however how much do
you think it can scale ? On<BR>> 16 rows<BR>> it didnt take long,
however or 28,000 rows? Will it use the O(n^2)<BR>>
scalability?<BR>><BR>> Thanks<BR>><BR>><BR>> Yancho
wrote:<BR>>><BR>>> Hi,<BR>>><BR>>> I am trying to make
a query so it parses through all the 16 cities<BR>>> i have<BR>>>
in <BR>>> a table called city, and for each city, picks the nearest
city,<BR>>> and gives<BR>>> me<BR>>> the distance between
both cities.<BR>>><BR>>> This is the query I made :<BR>>>
<BR>>> select<BR>>> c.city_name, astext(c.the_geom),
distance(c.the_geom, d.the_geom) AS<BR>>> Distance, d.city_name,
astext(d.the_geom)<BR>>> from city c, city d<BR>>>
where<BR>>> c.city_name = (<BR>>> select c.city_name order by
c.city_name ASC<BR>>> )<BR>>> and<BR>>> d.city_name =
(<BR>>> select d.city_name order by d.city_name DESC<BR>>>
)<BR>>> group by c.city_name <BR>>> order by Distance
DESC<BR>>> LIMIT 1;<BR>>><BR>>> But I am getting this error
: ERROR: column "c.the_geom" must<BR>>> appear in<BR>>>
the<BR>>> GROUP BY clause or be used in an aggregate function
<BR>>><BR>>> I am seeing no reason why I should add c.the_geom,
anyone can<BR>>> enlighten me<BR>>> more on why I should group by
the_geom and after all if it does make<BR>>> sense?<BR>>>
<BR>>> Thanks<BR>>><BR>>> --<BR>>> Matthew
Pulis<BR>>> <A
href="http://www.solutions-lab.net">www.solutions-lab.net</A> // <A
href="http://www.mepa-clan.info">www.mepa-clan.info</A><BR>>>
<BR>>> _______________________________________________<BR>>>
postgis-users mailing list<BR>>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>>
<A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>><BR>>><BR>><BR>>
--<BR>> View this message in context: <A
href="http://www.nabble.com/Shortest-">http://www.nabble.com/Shortest-</A><BR>>
Distance-from-Every-Point-tf4743229.html#a13575499<BR>> Sent from the
PostGIS - User mailing list archive at <A
href="http://Nabble.com">Nabble.com</A>.<BR>><BR>>
_______________________________________________ <BR>> postgis-users mailing
list<BR>> <A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>>
<A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</A><BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR><BR
clear=all><BR>-- <BR>Matthew Pulis<BR><A
href="http://www.solutions-lab.net">www.solutions-lab.net</A> // <A
href="http://www.mepa-clan.info">www.mepa-clan.info</A> </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>