[postgis-users] Shortest Distance from Every Point

[David William Bitner]

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 at gmail.com> 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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
************************************
David William Bitner
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071103/55782f14/attachment.html>