Re: Shortest Distance from Every Point

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. 

On 11/3/07, Matthew Pulis <mpulis@...> wrote:

Hi,

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.

This is the query I made :

select
c.city_name, astext(c.the_geom), distance(c.the_geom, d.the_geom) AS Distance, d.city_name, astext(d.the_geom)
from city c, city d
where
c.city_name = (
select c.city_name order by c.city_name ASC
)
and
d.city_name = (
select d.city_name order by d.city_name DESC
)
group by c.city_name
order by Distance DESC
LIMIT 1;

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

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?

Thanks

--
Matthew Pulis
www.solutions-lab.net // www.mepa-clan.info

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

--
************************************
David William Bitner
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users