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="margin-top: 0; margin-right: 0; margin-bottom: 0; margin-left: 0; margin-left: 0.80ex; border-left-color: #cccccc; border-left-width: 1px; border-left-style: solid; padding-left: 1ex">
<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>