Select transposed values

Question

I am trying to do a conflation. This is my method, and my problem:

METHOD

Step 1: I have two tables with road geometries. I am using ST_Dwithin to subselect geometries from table 'A' that lie within 500 meters of geometries from table 'B'. Let's call this selected part of table 'A', 'sel_a'. (This just to whittle the massive table 'A' into just useful geometries). I think I got this query right (waiting for results at the moment).

Step 2: Create points from geometries in table 'sel_a'; I am querying start, end and mid points for each geometry.

Step 3: Compute the closest line from table 'B' for each point obtained above. Then I can, for each geometry in table 'A' attribute a geometry from table 'B', such that the geometry from table 'B' was selected as being closest to at least two points created in step two. (Hit me in the comments if this is not clear.)

PROBLEM

The only way I can think of doing step 2 is using this query (which is pretty straightforward), but I'm not sure how to combine the results into two columns:

SELECT DISTINCT ON (sel_a.id)
     sel_a.id AS A_ID,
     ST_Line_Interpolate_Point(sel_a.geom, 0.0) AS sp,
     ST_Line_Interpolate_Point(sel_a.geom, 0.5) as mp,
     ST_Line_Interpolate_Point(sel_a.geom, 1.0) as ep,
   FROM sel_a

Which would result in a table that looks like this:

A_ID  sp          mp        ep
1     <start pt>  <mid pt>  <end pt>
2     <start pt>  <mid pt>  <end pt>
3     <start pt>  <mid pt>  <end pt>
...   ...         ...       ...

Now how do I then execute step 3? I will need to somehow transform the table that the above query returns into something that looks like this:

A_ID  Point_frm_A closest_B_ID
1     <start pt>  <to be computed>
1     <mid pt>    <to be computed>
1     <end pt>    <to be computed>
2     <start pt>  <to be computed>
2     <mid pt>    <to be computed>
2     <end pt>    <to be computed>
...   ...         ...

How do I do that? Or (preferably), can I modify the query above to directly result in the table structure I am looking for? (The latter will avoid an extra transposing step).

Answer 1

You can use UNION SELECT to combine various columns into rows, so from the output of step 2 to stack sp, mp, ep into the same column:

SELECT a_id, sp FROM points 
  UNION 
SELECT a_id, mp FROM points 
  UNION 
SELECT a_id, ep FROM points

You can then use the <-> operator, for finding the closest point in one table to another, used in the ORDER BY clause, in conjunction with a LATERAL JOIN to return the closest point from table B to each sp, mp and ep from step 2. An example of this approach can be seen in Paul Ramsey's cartoDB blog.

Combining the two, gives something like (untested)

WITH 
  points (a_id, sp, mp, ep) AS 
    (SELECT 
       DISTINCT ON (sel_a.id)
       sel_a.id AS A_ID,
       ST_Line_Interpolate_Point(sel_a.geom, 0.0) AS sp,
       ST_Line_Interpolate_Point(sel_a.geom, 0.5) as mp,
       ST_Line_Interpolate_Point(sel_a.geom, 1.0) as ep,
    FROM sel_a),
  stacked_points(a_id, pt) AS
    (SELECT a_id, sp FROM points 
       UNION 
     SELECT a_id, mp FROM points 
       UNION 
     SELECT a_id, ep FROM points)
 SELECT 
     sp.*, b.b_id 
 FROM stacked_points sp
    CROSS JOIN LATERAL
     (SELECT b_id 
      FROM b 
      ORDER BY sp.pt <-> b.geom
      LIMIT 1) as b;

I don't think there is a more elegant way of getting the closest point in table b to each of the sp, mp and ep, and then combining the result into two columns in one step, but I may very well be wrong -- it would not be the first time.

Answer 2

What I ended up doing to flip the columns to rows is the following:

SELECT
    p.a_id AS a_id,
    unnest(array[p.sp, p.mp, p.ep]) AS a_pts,
    0 AS b_id
  INTO TEMPORARY TABLE a_points
  FROM
    (SELECT DISTINCT ON (sel_a.a_id)
         sel_a.a_id AS a_id,
         ST_Line_Interpolate_Point(sel_a.geom, 0.0) AS sp,
         ST_Line_Interpolate_Point(sel_a.geom, 0.5) AS mp,
         ST_Line_Interpolate_Point(sel_a.geom, 1.0) AS ep
       FROM sel_a) AS p;

As you can see, I nested my code that generates points into another that uses unnest to unstack an array of points. This results in the table I want.

(Ps. the 0 AS b_id initializes the column 'b_id' as numeric. This column is subsequently updated to store the ID of the nearest line from table 'b' for each row in table 'a_points', created above. John Barca's answer provides a perfect method to do this in the last few lines of his code block.)

Further reading on unnest:

1. Example from SO
2. Creating unnest