16

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?

underdark
  • 84,148
  • 21
  • 231
  • 413
djq
  • 16,297
  • 31
  • 110
  • 182
  • 5
    I know underdark has answered this with her characteristically clear diagrams, but something I'm not sure on is whether your polygons are topologically related in the first place. Topology, in this case, implies a logical relationship between entities as separate from their visual representation, and (in PostGIS) requires setting up a schema with CreateTopology and so on (http://bit.ly/oLk8QY) But the way your buildings are digitised looks to me like they are topologically distinct despite the visual closeness of their edges. Just something for future questioners to be aware of. – MerseyViking Sep 23 '11 at 11:01
  • Would this mean that I need to add a AddTopoGeometryColumn to the table before running the query? Based on the results from ST_Touches the values I checked all made sense but perhaps that was by chance. – djq Sep 23 '11 at 15:27
  • 1
    Not only would you need to call AddTopoGeometryColumn, but your data would need to be digitized in a topologically consistent way. For instance, traditionally you would digitise two semi-detached houses as two polygons and use "snap to vertex" in your GIS to ensure the shared wall touches, but it will be stored as two coincident lines with coincident points at each end. But topologically digitizing them means that the shared wall really is just one line and two nodes shared by each polygon. ST_Touches just does a spatial check for closeness, not a topological one. – MerseyViking Sep 26 '11 at 13:50

1 Answers1

19

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
underdark
  • 84,148
  • 21
  • 231
  • 413
  • I am interested in knowing how many polygons each polygon touches though. Otherwise it is perfect, and I got it to run! – djq Sep 22 '11 at 20:47
  • 1
    I just realized it returns a true/false value for every case so all I need to do is count how many true values are there. Thanks! – djq Sep 22 '11 at 20:52
  • 5
    Added a possible counting query. – underdark Sep 22 '11 at 20:55