Combining grid of points to closest distance of several subqueries results using PostGIS

Question

Say I have a table "table1" with a bunch of points (grid), and another table, "table2", with a bunch of lines (say they are roads), with a column "col1" (say it's a type of roads). So:

table1:

id;geography
1 ;POINT(X1,Y1)
2 ;POINT(X2,Y2)
...

table2:

geography  ; col1
LINE(Xi,Yi); 1
LINE(Xi,Yi); 1
LINE(Xi,Yi); 2
LINE(Xi,Yi); 1

I want to create a table which gives me, for each point in my grid, the distance to the closest "road" of type 1 and the distance to the closest "road" of type 2.

table3 I want (col1 is "distance to closest type 1", col2 is "distance to closest type 2"):

id ; col1  ; col2
1  ; 23.45 ; 0.234

To create this table, I thought of using lateral joins. However, doing so returns, in my case, zero rows, which is not true. I suspect that it joins both subqueries and that the intersection of the two is null. I'm just stumped as to how to get to what I want (which is basically the "sum" of the two subqueries instead of the intersection). Here is the query I used:

CREATE TABLE table3 AS (

SELECT t1.id,
ST_DISTANCE(t1.geography, t21.geography) AS col1,
ST_DISTANCE(t1.geography, t22.geography) AS col2
FROM table1 t1
CROSS JOIN LATERAL
    (SELECT t2.geography AS geography
     FROM table2 AS t2
     WHERE ST_DWITHIN(t2.geography, t1.geography, 10000) AND t2.col1 = 1
     ORDER BY ST_Distance(t2.geography, t1.geography)
     LIMIT 1
     ) AS t21
CROSS JOIN LATERAL
    (SELECT t2.geography AS geography
     FROM table2 AS t2
     WHERE ST_DWITHIN(t2.geography, t1.geography, 10000) AND t2.col1 = 2
     ORDER BY ST_Distance(t2.geography, t1.geography)
     LIMIT 1
    ) AS t22
)

I can do what I want for one lateral join at a time (i.e, have a table3 that has only col1), but I want to do all the columns at the same time. In this example I have only two columns, but I plan on having a dozen or so.

Answer 1

You shouldn't use cross join if one of the 2, or the 2, can be null, prefer left join. Also, usually we write the <-> instead of ST_Distance for nearest neighboor problem, and I'm not sure about the ST_Dwithin (it maybe slow down the computation, check the explain) so I wrote it in another way if you really want to limit to 10km:

CREATE TABLE table3 AS (
    SELECT t1.id,
        CASE WHEN col1_dist < 10000 THEN col1_dist ELSE NULL END AS col1,
        CASE WHEN col2_dist < 10000 THEN col2_dist ELSE NULL END AS col2
    FROM table1 t1
    LEFT JOIN LATERAL
        (SELECT 
            ST_DISTANCE(t1.geography, t2.geography) as col1_dist
        FROM table2 AS t2
        WHERE t2.col1 = 1
        ORDER BY t2.geography <-> t1.geography
        LIMIT 1
        ) AS t21
    ON TRUE
    LEFT JOIN LATERAL
        (SELECT 
            ST_DISTANCE(t1.geography, t2.geography) as col2_dist
        FROM table2 AS t2
        WHERE t2.col1 = 2
        ORDER BY t2.geography <-> t1.geography
        LIMIT 1
        ) AS t22
    ON TRUE
)