Virtual layers in QGIS and the task of identifying spatially coincident point features


A typical housekeeping task in my workplace is finding historic drillholes that have been entered into a database in an approximate manner, all with the same coordinates (and not their correct position)!. This may have happened for several valid reasons including when the location of the holes in an old investigation was not provided, other than on a basic plan, and those entering the data did not have time to determine accurate coordinates. The other situation is when a drill hole has been entered multiple times into a database by mistake and possibly given a different name that makes it difficult to easily identify by a standard attribute search.

So is it possible to find point features (such as drill holes or sample locations) that have been given coincident coordinates in order to fix them? While there may be several ways of doing this, the answer is surprisingly simple through the use of virtual layers in QGIS!

Virtual layers allows the power of SQL database functionality to be applied to a layer or layers without need to change the data. The layer may be a local feature class or shapefile, or even an external database(s) such as a Web Feature Service, and if this is the case, there is no need to make a local copy.

Some of the capabilities SQL can do include:

  • 1.       Attribute tables can be changed (alias field names assigned, filters applied, etc)
  • 2.       Virtual joins to other layers are possible
  • 3.       Aggregate functionalities can be applied: max, min, count, etc.  
       
      Normally one would use an aggregate function on an attribute table field to do this, but QGIS allows the geometry field to be considered as well. Given this surprising capability, the following syntax can be used on a virtual  layer (in this case the file name is drilling and a temporary field name, DuplicateRecords, has been created to store the count of duplicate records).


select COUNT(geometry) as "DuplicateRecords", geometry from drilling

group by geometry

The virtual layer will appear as a point file that can be labelled, symbolised and filtered using the DuplicateRecords field. The virtual layer does not have an identifier field but if you load the database as a regular layer in QGIS as well you can identify individual records. By assigning the corrected coordinates to each feature the virtual layer will reflect the updates.

Try this on a real drill hole database sometime such as the boreholes layer (a Web Feature Service) on the Mineral Resources Tasmania website where there are some problematic drillholes still to sort out.

 I hope I have explained this properly. Let me know if it is confusing and I can provide some screen grabs! There may be a more elegant use of SQL syntax than what I provided but start simple!

Regards
Colin
(colinintas@gmail.com)

Comments

Post a Comment

Popular posts from this blog

How to assign RGB colours to QGIS vector file

Image
A frequent request of MRT is whether we have a symbology file for the geology polygon layers that can be imported into QGIS to replicate the map images. It so happens that an RGB text file has been made available that has the correct colours. Unfortunately patterns are not available but in a subsequent blog I will outline how to create them in QGIS There are two ways to do this: A) The simple way (but does not create a useful legend) 1.        Join a RGB table of values to the matching geology layer based on a common field. The table can be something like this (space delimited example):   Code R G B 9502 24 38 240 9500 32 62 10 etc 2.        In the Symbology window for the geology layer choose single symbol option (at the top of the screen). Not exactly  intuitive! 3.        Press the data defined override expression button to the right of the Fill Co...
Read more

Orthorectification of historic aerial photos

Updated Dec 14 2020 One of the occasional tasks I undertake in my geological mapping work is to orthorectify historic vertical aerial photos in order to examine them in a GIS. The old photos have much value as they capture the landscape at particular point in time where it may have been subsequently modified. I have recently been working in the Launceston area and have created mosaics of the oldest and most detailed photos available in order to show extent of older quarries, geomorphic features, geology, etc. As an example, I was recently asked to comment on a development application on a former quarry site and the old photos were very good at showing the extent of ground disturbance. I created a digital stereopair with StereoPhotomaker (see earlier blog) from the scans and made a rectified image for the GIS from which I was able to gain a good understanding of the site.   This blog provides a documented methodology for my own benefit and anyone else. There are two main consid...
Read more

Geological Dip and Strike Label Placement in QGIS

If you are interested in automatically placing the dip value in the same position relative to the dip direction for cartographic reasons (when plotting a map) then you have come to the right place. (I must confess I have borrowed and modified this tip from the Irish QGIS site (with thanks and unfortunately cannot find the link despite some effort) but am repeating it here so that I have my tips in one location.) To state the problem in another way, without this script the dip value will stay in the same geographic position with respect to the point symbol regardless of which way the symbol is oriented. This doesnt look good and therefore finding an automated method of displaying such information on large datasets is highly desirable. The solution (assuming you are familiar with labelling point features in QGIS): In the symbol placement tab of the Layer Properties, choose the Offset from Point option and place the following script in the data derived override option for the Offset X,Y...
Read more