Extending a segment in QGIS to check intersection with a polygon

Question

I am trying to find the adress points (red dots) located in front of an obstacle (red polygon). The street is green and the pink line represents the street face (Si).

This is what I have so far, but another method would be acceptable as well.

Therefore I want to extend the blue line (which is the line from the adress point to the street face). And then check if the extension intersects with the polygon.

I am using the virtual layer function of QGIS but I can also simply do this with postgis. I tried to use this to write my query but I get mistakes (st_translate):

postgis, extrapolate a line

    SELECT 
    adpt.id
    ,ST_TRANSLATE
        (adpt.id, 
            sin(
                ST_AZIMUTH
                    (ST_ClosestPoint(si.geometry, adpt.geometry), adpt.geometry)
                )
            * (
                    ST_Distance(
                        ST_ClosestPoint(si.geometry, adpt.geometry)
                                )
                    * 10 
                )
        , 
            cos(
                ST_AZIMUTH
                    (ST_ClosestPoint(si.geometry, adpt.geometry), adpt.geometry)
                )
            * (
                    ST_Distance(
                        ST_ClosestPoint(si.geometry, adpt.geometry)
                                )
                    * 10 
                )
        )
FROM  
    xl_adpt_ss_si ADPT
    JOIN xl_streetside si 
        ON adpt.SI_ID = si.id
where
    ADPT.id='1136345'

;

Once I have my additional point, I want to use ST_MakeLine to draw the new line before computing the intersections.

Answer 1

Since you have access to the center-line I'd suggest to go a different way; find all address points whose projected point on the center-line falls between the (min/max) fraction of line-length of those of the obstacles vertices.

Note: I will only consider a single obstacle case and assume that I can find a single road center-line by simply intersecting both. I will also limit the address points to map with a simple proximity search (via ST_DWithin). You will need to define those relations to make it work on full tables.

WITH
    map AS (
        SELECT  MIN(frac) AS minf,
                MAX(frac) AS maxf,
                geom
        FROM    (
            SELECT  ST_LineLocatePoint(a.geom, (ST_DumpPoints(b.geom)).geom) AS frac,
                    a.geom
            FROM    <center_line_table> AS a
            JOIN    <obstacle_table> AS b
              ON    ST_Intersects(a.geom, b.geom)
             -- WHERE b.<id> = xxx
        ) q
        GROUP BY
                geom
    )

SELECT  a.*
FROM    <adress_layer> AS a
JOIN    map AS b
  ON    ST_DWithin(a.geom, b.geom, <some_threshold>)
WHERE   ST_LineLocatePoint(b.geom, a.geom) BETWEEN b.minf AND b.maxf;

This should return all addresses, on all sides, whose closest points on the center-line fall within the center-line-aligned extent of the obstacle.

It is considerably simpler to write and should be substantially faster, if spatial indexes are in place.

Try it.