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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > > -- > ************************************ > David William Bitner > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- Matthew Pulis www.solutions-lab.net // www.mepa-clan.info
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
