postgis/postgres ST_Distance and GROUP BY

[المشرف العام]

Trying to find the nearest road to a postcode point and order the results by distance. The roads are named according to UK convention so A606 etc and there are multiple entries for many road numbers (different bits of A606 may occur 5 or 6 times with differing OID).

So far:

SELECT MIN(ST_Distance(geom1, geom2):: NUMERIC, 2), q.postcode, a.number AS "a_road" FROM env.a_road, env.postcode q, pdetails.p WHERE q."postcode" = p."PostCode" GROUP BY a.number ORDER BY ST_Distance(geom1, geom2) ASC; I know this is not correct with respect to the aggregate function and GROUP BY clause but how to get around it?

I want to return a table as |postcode|a_road|distance_m
with only one postcode entry, one nearest road and one distance to that nearest road.

My closest attempt has been this:

SELECT DISTINCT ON (q.postcode)q.postcode, a.number, ROUND(ST_Distance(q.geom, a.geom)::NUMERIC, 2) as distance_m FROM env.a_road, env.postcode q, pdetails.p WHERE q.postcode = p.Postcode ORDER BY q.postcode, ST_Distance(q.geom, a.geom) ASC; Returns the right results but I can't sort by distance because of that same initial problem of having q.postcode in the GROUP BY.



أكثر...