1

I'm using QGIS 3.2.3. I have:

  1. a shapefile layer of town outlines (about 600 features)
  2. a spatialite layer of points (about 20000 features)

I would like to filter the points layer to show only those points that are east of a town in the shapefile layer.

I'm stymied by the lack of an obvious join condition between the two sets of data and the fact that one data source is a spatialite database and one is not.

Conceptually, I want something like:

select *
from
  pointtable
  join polytable on (???)
where
  polytable.town = 'SomeTown'
  and st_x(polytable.geom) > st_x(pointtable.geom)

...but I'm not sure how to get there.

  • Is there a townID or similar on the point layer? else you will need to refine how you associate a point to a given town (ex: points just West of new York are East of Los Angeles; another point can be East of town X..., but 1 000 km up north) – JGH Sep 25 '18 at 11:55
  • The points are limited to Massachusetts, so I really only care about the easy/west relationship. Nothing is "too far" north or south. The points don't have a town id. I can arrange for them to have a town name which should match in most cases. I'll give that a shot. – larsks Sep 25 '18 at 12:20
  • Can I join layers on an expression, rather than a column name? The join condition would be something like on lower(towns.town) = lower(points.city), since there is inconsistent casing in the two data sets. I guess the alternative is to filter the data first, rather than using the source data verbatim. – larsks Sep 26 '18 at 17:06
1

Try creating a virtual layer (layer -> add layer -> virtual layer). Set both your layers as base for the new layer, and enter a valid SQL-code, this should yield the desired result.

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.