Your question can be also answered by a single (albeit complex) query like the following which returns the whole record and the distance to the reference geometry.
Please note that if more than one record matches the min distance they are all returned.
SELECT
i.*,
md.min_distance
FROM
address AS i,
(SELECT
ga.address_geom,
min( ST_Distance(
ga.address_geom,
gb.address_geom)
) AS min_distance
FROM
address AS ga,
address AS gb
WHERE
ga.id <> gb.id
AND
ga.id = 3
GROUP BY
ga.address_geom
) AS md
WHERE
ST_Distance( i.address_geom, md.address_geom) = md.min_distance;
I have tested this query on table of addresses and it works.
In the query above I am looking for nearest point to that with id=3.
To measure distances in Km you need to transform your geometry's coordinates from long/lat (spherical) to x/y (planar). In my example I am using UTM zone 35N, EPSG 32635 - you will have to pick something appropriate based on your data's location.
The query below can be optimized by avoiding the st_transform call (e.g. by creating an intermediate table with geometry in EPSG 32635).
The other thing that can be improved is that distances are computed as a query over a cartesian product (the table is joined with itself) so you unnecessarily compute both the distance from A to B and the distance from B to A.
select a.outletname, b.outletname,
st_distance(st_transform(a.geom, 32635), st_transform(b.geom, 32635))
from
table_name a,
table_name b,
where a.outletname != b.outletname
and a.outletname in ('A', 'B', 'C', 'D')
and b.outletname in ('A', 'B', 'C', 'D')
and st_distance(st_transform(a.geom, 32635), st_transform(b.geom, 32635)) > 50000
order by st_distance(st_transform(a.geom, 32635), st_transform(b.geom, 32635)) desc;
whuber's idea with the convex hull makes sense when the data set is large enough to prevent computing distances on the cartesian product.
I've checked on my dataset and, as expected, the greatest distance is equal when computed by the two different methods.
For reference, the distances between the convex hull points query:
select st_distance(a.geom, b.geom) from
(select (st_dumppoints(st_convexhull(st_collect(st_transform(way, 32635))))).geom from planet_osm_point a where a.amenity='pharmacy' and st_contains((select way from planet_osm_polygon where name='Sector 1'), a.way)) a,
(select (st_dumppoints(st_convexhull(st_collect(st_transform(way, 32635))))).geom from planet_osm_point a where a.amenity='pharmacy' and st_contains((select way from planet_osm_polygon where name='Sector 1'), a.way)) b
order by st_distance(a.geom, b.geom) desc;
There's an additional problem with the convex hull approach - it works on aggregated geometry hence it anonymizes which point belongs to which feature, making it more difficult to compute outlet names.
The solution would probably be to compute the convex hull and then identify (by spatial queries), which feature geometries belong to the convex hull and run the distance query on those features only.
E.g.:
The geometry of the first point of the convex hull:
osm=# select (st_dumppoints(st_convexhull(st_collect(way)))).geom from planet_osm_point a where a.amenity='pharmacy' limit 1;
geom
----------------------------------------------------
010100002031BF0D00D120685EC2064241D3F3D3FDA9AE5541
(1 row)
The corresponding feature from the table:
osm=# select name, osm_id, way from planet_osm_point a where a.way = '010100002031BF0D00D120685EC2064241D3F3D3FDA9AE5541';
name | osm_id | way
-----------+------------+----------------------------------------------------
Dentafarm | 1395848741 | 010100002031BF0D00D120685EC2064241D3F3D3FDA9AE5541
(1 row)
Best Answer
You need to reference your table twice, giving it different aliases: