1

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).

Improve this question
1
  • I accidentally down voted this (pre-coffee), so I edited the question, as it was the only way I was allowed to reverse my downvote.
    – John Powell
    Jun 14 '16 at 7:24
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.

Improve this answer
2
  • Ooooh! This is an interesting solution, I'll certainly bookmark it for later reference. I found another way though. I'll post my solution in the morning. But there is one little detail you missed John: the geometries in table B are MultiLine strings, not points.
    – Kartik
    Jun 14 '16 at 7:44
  • That shouldn't matter. The <-> operator should work with any geometry type. I called it geom, as it wasn't clear from the question which type is was or what it was called. You can also use ST_Distance and ST_DWithin to find k nearest neighbours, but the lateral join and ORDER BY a.geom <-> b.geom is more efficient (usually). I'll be interested to see your solution.
    – John Powell
    Jun 14 '16 at 8:00
0

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
Improve this answer
2
  • 1
    Fair enough. Unnest(array is a good approach too. I still think my approach answers the whole question as asked and deserves to be accepted, as such.
    – John Powell
    Jun 29 '16 at 13:42
  • Yes, I grant you that your answer is correct, and complete. Mine provides an alternate way to execute the first part.
    – Kartik
    Jul 1 '16 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.