Find distance between cities in spatial table-CodePudding

I need to find out the minimum distance between cities that have a shape_area greater than 1000m^2. I'm using Postgresql and PostGis and my table looks like this:

CREATE TABLE "cities" (gid serial,
"city_id" int4,
"city" varchar(21),
"shape_area" numeric);

SELECT AddGeometryColumn('','cities','geom','26986','MULTIPOLYGON',2);

CodePudding user response：

Assuming that EPSG:26986 already has metres as unit, just join the table with itself and use ST_Area to filter only the records that have an area greater than 1000sqm. After that use min() with a GROUP BY to get only the shortest distance:

SELECT t1.city, t2.city, min(ST_Distance(t1.geom,t2.geom))
FROM cities t1 
JOIN cities t2 ON 
  t1.gid <> t2.gid AND
  ST_Area(t1.geom) > 1000 AND
  ST_Area(t2.geom) > 1000
GROUP BY 1,2;