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