What do you do when you’re cleaning up data. Especially address data and you get down to the sloppy part of the data cleanup? I did 80% of this in Postgresql by shaking off the cobwebs on my sql skills but this part requires visual. I have addresses assigned to roads with no street type (like roads, drives, lanes, etc).
If you’ve played around with QGIS enough you’ve probably discovered you can do some spatial sql here and there. I’ve used this trick a few times and with all the other fun I had back in January, I sort of forgot it was a thing.
QGIS will let you create layers which result in a file of some sort be it shapefile, geopackage, etc. You can also build a virtual layer which only exists within QGIS and is a subset of your current layers.
For a virtual layer you import the the data layer and then throw some SQL up against it. In this case I imported my addresses and wrote some SQL: select geometry from address_point where type IS NULL and street_nam <> ‘HWY 66’
What I get is a point layer that I expertly named “Type is NULL” so I can find all the mistakes and quickly fix them. So far I dropped it from 500 errors down to about 310 in an hour. Could there be a fancier way to do this with SQL? Probably. Visually it works for me so I can double check things as I’m doing them.
So what happens as I fill out the attribute table with the correct data? The red dot disappears. You can feel a bit of display “slowness” if you have a lot of data displayed in the Virtual Layer so beware. For some clients I’ve built these virtual layers to create different colored dots as their editing data to point out mistakes. Overall it’s a neat trick to help with data cleanup.
