3

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

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

  • Why is using the "dumb" distances between the obstacle and the points not sufficient? Or is your question about creating the line geometries? – bugmenot123 Dec 6 '18 at 12:17
  • are those street faces one consecutive line per road (e.g. boundary of green polygon) or can you identify sides? do you have a centerline per road? – ThingumaBob Dec 6 '18 at 12:26
  • @bugmenot123 what do you mean by dumb distances ? – Lay Dec 6 '18 at 13:15
  • @ThingumaBob yes I can get a hold of the centerline per road, but I do want to identify the adress points at both sides of the road. The street faces are consecutive lines, but they don't exactly overlap with the street surface segments (street faces end at the small lines) ibb.co/xFvmYdy – Lay Dec 6 '18 at 13:15
3

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.

  • 1
    Was just about to post the same (using ST_LineLocatePoint) answer as you, except that I wasn't coding yet. Therefore I'm seconding this. :) – GHRF Dec 6 '18 at 14:01
  • Thank you, this seems indeed a more efficient solution. I am trying to make it work but I get an error on the last line (WHERE ST_LineLocatePoint(ST_LineMerge(b.geom), ST_DumpPoints(a.geom)) BETWEEN b.minf AND b.maxf) > set-returning functions are not allowed in WHERE – Lay Dec 10 '18 at 13:23
  • @Lay not sure how to help here, and especially not within the scope of comments; that's a more general query design problem, i.e. you can't use ST_DumpPoints in the WHERE filter. you might want/need to ask another question where you inquire to get help on solving your exact query; refer to this answer if you need to. I'd be happy to help, but this is just not the place ,) oh and read the PostgreSQL docs on how to access the columns of set returning functions, btw. – ThingumaBob Dec 10 '18 at 14:48
  • @Lay ...are you trying to make that work on tables? – ThingumaBob Dec 10 '18 at 14:55
  • @ThingumaBob, it works. I added a buffer to avoid problems when the obstacle doesn't intersect the axis and a filter based on the street side to avoir selection of adresses on other roads. – Lay Dec 10 '18 at 16:52

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.