Just wanted to say that I managed to write this Query :
SELECT DISTINCT ON (c1)
c1.city_name AS "c1",
c2.city_name AS "c2",
distance(c1.the_geom, c2.the_geom),
makeline(c1.the_geom, c2.the_geom)
FROM
city c1
JOIN
city c2
ON (
c1.city_name <> c2.city_name
)
ORDER BY c1, distance ASC
;
It works perfectly, however how much do you think it can scale ? On 16 rows it didnt take long, however or 28,000 rows? Will it use the O(n^2) scalability?
Thanks
Yancho 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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
