Select layer by location in PostgreSQL

Question

I have a footprint of some buildings (see the picture).

The selected buildings have an area below 30 sqm. As you can in the picture see some buildings share almost borders with a building bigger than 30 sqm. When others don't. The building below 30 sqm but shares a border with a bigger building I want to include in my calculation. The others below 30 sqm should be sorted away.

I have succeeded with this through desktop GIS, and the solution looks like this:

But I want to do the same process in postgres.

I can select the buildings pending on their area and I have put them in to two new tables:

Create TABLE building_below as Select * from building where area<30 and Create TABLE building_above as Select * from building where area>30

I can not figure out how to select layer by location in PostgreSQL.

I have tried with inspiration from: Join polygons by location PostgreSQL/PostGIS Merging adjacent polygons in shapefile that has been split at tile boundaries?

But it didn't work.

I have tried this:

SELECT DISTINCT ON (s.gid) s.gid,*

 FROM building_below s

LEFT JOIN building_above h ON ST_DWithin(s.geom, h.geom, 0.01)

ORDER BY s.gid, ST_Distance(s.geom, h.geom);

But it just select all the rows/buildings in building_below.

I know from desktop GIS, that it should not do that.

Answer 1

Try

SELECT a.*
FROM  building AS a
JOIN  building AS b
  ON  ST_Intersects(a.geom, b.geom)
WHERE a.area <= 30
  AND b.area > 30;

Make sure you have a spatial index in place and run VACUUM ANALYZE building; prior to execution!

If your buildings only almost share a border, i.e. don't actually intersect, use

... ST_DWithin(a.geom, b.geom, <threshold>) ...

instead.

Note that the latter will need your geom either projected or in type GEOGRAPHY to make sense (i.e. if your geom is in EPSG:4326 or any geographic CRS, units will be in degrees and thus rather useless. You can then, if in EPSG:4326, however, easily cast to GEOGRAPHY to implicitly use meter as unit).