4

I have a dataset (buildings, polygons) that has some overlapping features. Some buildings are old and already demolished but are still in the dataset. I want to hide all these. These have properties of ‘demolished’. So that’s easy one might say. However, for the same (old building) another feature exist and has properties ‘in use’. Luckily these two features have the same reference numbers. I like to filter the identification numbers (so multiple features) when at least one of the features has properties ‘demolished’.

I’m not experienced with good syntaxes for this and was wondering If anyone could help me out with this. I’m using QGIS 2.14.

The easy one, only filtering the demolished building would be:

NOT “buildingstatus” = ‘demolished’

But I need the other features with a corresponding identification number. What would be this syntax? Either to hide them, or select them (and delete, or move to other layer)?

CASE
WHEN buildingstatus = demolished 
THEN SELECT Identification 
END

UPDATE:I'm trying this query with a virtual layer. First I get geometry issues. If I define the geometry as a polygon, than it adds a layer but QGIS crashes.

enter image description here

enter image description here

NOTE: If I embed the layer it doesn't make a difference

enter image description here

Improve this question

3 Answers 3

Reset to default
4

If you only want to hide the buildings which have the “buildingstatus” = ‘demolished' while keeping them in your dataset, you could use a rule-based style.

The rule would be: NOT “buildingstatus” = ‘demolished'. Here you would apply your style. All other buildings won't be shown. You can refine this rule by "Refine selected rules" and add a classification for example as shown in my Screenshot. You don't need to use the "hide" rule is just for demonstration.

enter image description here

Improve this answer
4

How about a Virtual layer?

SELECT * FROM yourlayer 
  WHERE id IN (SELECT id FROM yourlayer
                 WHERE buildingstatus = 'demolished')

Note: here nested SELECT enables you to select 'in use' buildings along with the 'demolished' one, if they share the same id.

Improve this answer
8
  • This would be the ideal solution. Not working exactly yet though. My input for the virtual layer would be: SELECT * FROM GV_pand WHERE identifica IN ( SELECT identifica FROM GV_pand WHERE pandstatus = "Pand gesloopt" ) However, with auto detect geometry option enabled. I get an error. With defined geometry (polygon type and the CRS) then Qgis does add a new layer but It just crashes.
    – EduardoHansson
    Jul 11, 2017 at 12:57
  • @EduardoHansson, just a quick question... is it a double-quotation? (I mean, "Pand gesloopt" part)
    – Kazuhito
    Jul 11, 2017 at 12:58
  • I see now. I changed it to single-quotation. Yet still geometry issues.
    – EduardoHansson
    Jul 11, 2017 at 13:03
  • @EduardoHansson hmm... could you add that error message to your original post? thanks!
    – Kazuhito
    Jul 11, 2017 at 13:06
  • 1
    Tried, didn't impact. I found a workaround (I need the results quick) but I definitely like to work on your query method further since it is so much quicker. I extracted all features by defenition demolished. Then with excel i make a query of all those ID's (dumping doubles, and add id="value"OR ... etc. I'm loading this into my layer query selection. 8x1000 times, cuz with 8000 items in once it's crashing :)
    – EduardoHansson
    Jul 11, 2017 at 14:34
2

I'm not sure you can do this with a query (but be glad to be proved wrong). Assuming the geometry of the 'in use' and the 'demolished' polygons are the same,you could try the following:

Open the processing toolbox (view > panels > toolbox) and use the extract by attribute tool to create a new layer with “buildingstatus” = ‘demolished’ as the query.

You could then use the Difference tool with the original layer and the newly created one to generate a final layer with just the 'in use' polygons.

Improve this answer
1
  • The extract by attribute tool is very useful! However, the difference tool also cuts away the newer buildings (different sizes, polygons) that replaced the demolished ones. Kazuhito's query might do the trick, if I get it working. Else I maybe have another idea: Extract by attribute. Export this layer to csv. Load in all the identification numbers in excel. Erase the doubles and transform these numbers into a search query to find all polygons with those id's.
    – EduardoHansson
    Jul 11, 2017 at 13:47

Your Answer

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

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