Thanks for the explanation both Paul and Regina :) thanks :)<br><br><div><span class="gmail_quote">On 11/5/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">
The "maximum minimum distance" has nothing to do with PostGIS, it's<br>just an idea: is there a distance you expect all your minimum<br>distances to be smaller than? If so, you can winnow out pairings of<br>
cities that will *not* yield a minimum distance. This is what the<br>ST_Expand does, it turns a point into a box, with a distance of ?mmd?<br>from each side to the center. And the && tests containment in that<br>
box, using the spatial index operator.<br><br>P<br><br>On 4-Nov-07, at 4:21 PM, Matthew Pulis wrote:<br><br>> Can you please explain further why u used the EXPAND? Didn't much<br>> get what is its use? And is mmd a thing which has to do with PostGis ?
<br>><br>> On 11/4/07, Paul Ramsey <<a href="mailto:pramsey@refractions.net">pramsey@refractions.net</a>> wrote:<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,<br>> 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
<br>> 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>><br>><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>> 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>