So you are saying that this query cannot be done using simple SQL only? I need to do a function and pass data through it?<br><br>If i dont group by, this is the result, end up with 16 rows for each city, being compared to all the cities, if I group by all non-aggregate or use distinct on
c.city_name I end up with : <br><br> city_name | astext | distance | city_name | astext<br>-----------+---------------------------------------+------------------+--------------+--------------------------------------
<br> Bismarck | POINT(-100.7833025517 46.79999918311) | 16.2288905625123 | Indianapolis | POINT(-86.1350025517 39.81399918311)<br><br><br>which off course doesn't make sense :(<br><br>Any other ideas please?<br><br><br>
<div><span class="gmail_quote">On 11/3/07, <b class="gmail_sendername">David William Bitner</b> <<a href="mailto:david.bitner@gmail.com">david.bitner@gmail.com</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">
Absolutely anything to the left of the where statement has to either be wrapped in an aggregate function or also in the group by clause when using grouping. <br><br><div><div><span class="e" id="q_1160639bdd3ae114_1"><span class="gmail_quote">
On 11/3/07, <b class="gmail_sendername"> Matthew Pulis</b> <<a href="mailto:mpulis@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">mpulis@gmail.com</a>> wrote:</span></span></div><blockquote class="gmail_quote" style="border-left-color: #cccccc; border-left-width: 1px; border-left-style: solid; margin-top: 0pt; margin-right: 0pt; margin-bottom: 0pt; margin-left: 0.80ex; padding-left: 1ex">
<div><span class="e" id="q_1160639bdd3ae114_3"> Hi,<br><br>I am trying to make a query so it parses through all the 16 cities i have in a table called city, and for each city, picks the nearest city, and gives me 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 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 appear in the 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 enlighten me more on why I should group by the_geom and after all if it does make sense?
<br><br>Thanks<br clear="all"><span><br>-- <br>Matthew Pulis<br><a href="http://www.solutions-lab.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">www.solutions-lab.net</a> // <a href="http://www.mepa-clan.info" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
www.mepa-clan.info</a> </span><br></span></div>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
postgis-users@postgis.refractions.net </a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://postgis.refractions.net/mailman/listinfo/postgis-users
</a><br><br></blockquote></div><br><br clear="all"><br>-- <br>************************************<br><span class="sg">David William Bitner </span><br>_______________________________________________<br>postgis-users mailing list
<br><a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a onclick="return top.js.OpenExtLink(window,event,this)" href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">
http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><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>