PostGIS – Identifying Topological Relationships Using PostGIS

postgissqltopology

I'm trying to perform a basic typology analysis using PostGIS. My objective is to find any polygons that touch other polygons. To do this, I thought the ST_GetFaceEdges would work
(reference). I want to check every polygon in my database and list all the other polygons that touch it. In the image below I would expect the result to say that two of the polygons (which are buildings) touch one building, and the results of the other 4 to say that they touch 0 polygons.

enter image description here

However, I'm having some difficulty understanding what to do. When I tried to copy the example there were a few parts of it that I did not understand.

-- Returns the sequence, edge id
-- , and geometry of the edges that bound face 1
-- If you just need geom and seq, can use ST_GetFaceGeometry
SELECT t.seq, t.edge, geom
FROM topology.ST_GetFaceEdges('tt',1) As t(seq,edge)
    INNER JOIN tt.edge AS e ON abs(t.edge) = e.edge_id;

I'm not sure whether topology is the name of a table, column or a part of the function. I assumed that it was the table, but I'm not sure.

-- try out on sample of data
-- Table is called 'TestArea', column used is 'fid_1'
SELECT t.seq, t.edge, geom
FROM  TestArea.ST_GetFaceEdges('fid_1', 1) As t(seq,edge)
    INNER JOIN tt.edge AS e ON abs(t.edge) = e.edge_id;

I'm also not sure what the function of the inner join is – does this join the result to the original object?

Best Answer

You could use ST_Touches instead:

ST_Touches — Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect.

ST_Touches returns TRUE for eg

enter image description here

Getting the counts should work something like this:

SELECT a.id, count(*)
FROM polygon_table as a
JOIN polygon_table as b
  ON ST_Touches(a.the_geom,b.the_geom)
GROUP BY a.id
Related Question