[postgis-users] Shortest Distance from Every Point

[Matthew Pulis]

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?

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 :

city_name | astext | distance | city_name | astext
-----------+---------------------------------------+------------------+--------------+--------------------------------------
Bismarck | POINT(-100.7833025517 46.79999918311) | 16.2288905625123 |
Indianapolis | POINT(-86.1350025517 39.81399918311)


which off course doesn't make sense :(

Any other ideas please?


On 11/3/07, David William Bitner <david.bitner at gmail.com> wrote:
>
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Matthew Pulis
www.solutions-lab.net // www.mepa-clan.info
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071103/cf103d96/attachment.html>