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);
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;