3

I recently posted this question, where the answer involved SQL and a Virtual Layer. In that situation, the SQL acted on the entire input layer, with the output exactly as desired.

Now I would filter the input layer so that the SQL/Virtual Layer approach operates only on the selected set of records. My SQL skills are rudimentary, and here's what I tried. The first two lines are my attempt to filter, and the remaining lines are from the earlier post:

SELECT    *
FROM      mylayer  
WHERE     owner = 'Jones';

SELECT    row_number() over() as newid,  
          a.id as aid,
          b.id as bid,   
          a.geometry  
FROM      mylayer a  
LEFT JOIN mylayer b
      ON ST_Distance(a.geometry, b.geometry) < 30 
      AND a.id <> b.id  
ORDER BY  a.id, b.id  

Although no error was generated, the output indicates that SQL only acted on the first two lines of code, and all subsequent SQL statements were ignored.

How can I get the answer to my earlier post to act only on a selected set of records?

2
  • 3
    Some interfaces would report a syntax error, since you have multiple queries, not one. pqsl honors the semicolon as a seperator, pgAdmin would run both queries but only show the second, and it seems that QGIS just ignores after the semi. Note that joining on ST_Distance is not optimizable (and effectively an antipattern), and you ought to be using ST_DWithin.
    – Vince
    Commented May 19 at 3:20
  • The virtual layer interface accepts only one query, the first one. Everything that follows a semicolon is discarded. Spatialite (virtual layer engine) does not implement the ST_DWithin function. However, it accepts PtDistWithin, which works similarly, except that it allows the distance to be expressed in meters when the geometries are points and the CRS is not projected (the Geography data type is not implemented). Commented May 19 at 11:58

1 Answer 1

6

To filter a table in the Virtual Layer query, you can use a WITH-clause:

WITH filtered AS (
 SELECT * 
 FROM mylayer
 WHERE owner = 'Jones'
)

select row_number() over() as newid,
            a.id as aid, b.id as bid,
            a.geometry
from filtered a --filtered is the Common Table Expression (CTE) name
left join filtered b
on st_distance(a.geometry, b.geometry)<30 and a.id<>b.id
order by a.id, b.id
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.