Iterate through table to do geometric calculation

Question

I've got two tables:

• tableA holds the objects with it's geometry
• tableB holds holds pairs of two objects (ids, each in a separate column) from table1

Now I want to calculate the concaveHull for the pairs in each row coming from tableB. The SQL (for one pair only) is quit simple:

SELECT ST_ConcaveHull (ST_Collect(tA.geom),1)
FROM tableA AS tA
WHERE id IN (250,214)

The "WHERE id IN (250,214)" is an example object pair stored in tableB.

How can I do this calculation for each pair from tableB iterative? With joins or loops? I cannot get it work.

Answer 1

If I get this right you should be using two JOINs on your <tableB>:

SELECT ST_ConcaveHull(ST_Collect(a.geom, b.geom), <value_lower_than_1.0>[, TRUE | FALSE]) AS geom
FROM   <tableA> AS ref
JOIN   <tableB> AS a
  ON   ref.<id1> = a.<id>
JOIN   <tableB> AS b
  ON   ref.<id2> = b.<id>
;

You may want an index ON <tableB> (<id>), if not the PRIMARY KEY already.

---

Note that the target_percent parameter should be smaller than 1.0 to actually create a concave hull; using 1.0 will be equal to ST_ConvexHull! That value has a rather logarithmic impact on computation time, and likely a strong impact on the resulting shape of the hull; decrease slowly by steps in the range of 0.01 to test for the desired result!
The second parameter will allow holes (inner rings) in the hull; default is FALSE.