1

I have 3 tables/shapes as follows:

ADRR (adress points)

ADRR_ID | ST_ID (street surface id) | point geometry 

STREET

ST_ID | ST_NAME (street name) | polygon geometry  

Obstacles

ID_OBS | polygon geometry  

Each address point is associated with a portion of street stored in the STREET table.

For each address, I want to check whether there is an obstacle in that STREET portion.

I want a column with a 'Y' or 'N' value to check whether the obstacle is or not present.

In order to do this, I used the VIRTUAL LAYER function in QGIS.

select 
    adrr.*
    ,(case when st_intersects(adrr.geometry, obstacles.geometry) then 'Y' else 'N' END)  V
from 
    obstacles,
    adrr 
    join STREET 
        on street.st_id = adrr.st_id

I don't have any error message but QGIS crashes each time I want to visualise the attributes of the resulting layer.

I created spatial indexes for all my tables but it didn't seem to help. Does anyone have another suggestion?

2

The query is making a cross join between all obstacles and all addresses, which is likely too much to handle.

You would need to create a subquery to get the streets having an obstacle, and then to join this subquery to your streets and addresses.

it should be something like the following (untested). Note that the obstacles are checked against the street geometry (your query check it against the address geometry but your text mentions the street) and that we limit the obstacle check to the 1st match (we don't care if a street has 1 or 10 obstacles, as long as it is not 0)

select 
    adrr.*
    ,(case when street_with_obstacle.st_id IS NOT NULL then 'Y' else 'N' END)  V
from 
    adrr 
    join STREET 
        on street.st_id = adrr.st_id
    LEFT JOIN (
     SELECT st_id
     FROM obstacles
     JOIN STREET    
      ON  st_intersects(STREET.geometry, obstacles.geometry)
     LIMIT 1
    ) street_with_obstacle ON STREET.st_id = street_with_obstacle.st_id;

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.