3

I have two datasets that I need to prepare for merging: both are of a set of POI in the UK (places of worship) which have been generated by two different sources. There are around 50,000 points total that I am working with. I am aware that ArcGIS has "find identical" which can generate a combined table of geospatially coincident features to prep for merging, so I am wondering if there is a good way to do this in QGIS. I note that there is a solution here: Identifying duplicate features by comparing two layers in QGIS?, but this seems pretty dated already (QGIS 2.8.2 "Select by Location" dialog lacks "Precision" field). Additionally, I would like to add a secondary (and thus probably best to be very fuzzy) level of comparison on the name fields.

Sample csv snippet 1:

X,Y,UNIQUE_REFERENCE_NUMBER,NAME
64603,796126,17128583,Our Lady of the Waves and St John R C Church
65742,801811,17125921,St Brendan R C Church
66698,798282,68805407,Lady of Star of Sea
67070,803420,17125920,Barra Parish Church
70334,807612,17125924,St Vincent's R C Church
70696,803097,17125925,St Barr R C Church
72626,946839,20133561,Chapel
72687,870275,17125963,Kilmuir Parish Church
73286,829700,17176145,St Mary's Church
74062,868490,41252211,United Free Church
74496.6,821084.7,85602455,St Peter's Church
74911,868433,17125957,Free Presbyterian Church
75392,821388,41251293,Daliburgh Church of Scotland
75414,821411,68803787,Church of Scotland
75666,836413,17125941,Howmore Church
75816,816488,17125931,Our Lady of Sorrows Roman Catholic Church
75857.8,845895.8,41251641,St Michael's Church
76159,835684,17125940,St Joseph's R C Church
77416,841842,18117082,St Bride's R C Chapel
77881,852044,17125951,St Mary's R C Church
78641.3,812064.8,41249385,St Michael's Church

Sample csv snippet 2:

X,Y,NAME
67076.79711,803410.267,BARRA
72692.42456,870265.891,KILMUIR AND PAIBLE
75395.55481,821381.923,SOUTH UIST
75669.4918,836404.6101,SOUTH UIST

A test of this should match "Barra Parish Church" (group 1) with "Barra" (group 2) and should be fuzzy enough to match, "Howmore Church" to "South Uist" in the second instance in group 2.

  • An ideal solution would be to get your points into postgis layers then execute a full join on st_dwithin() which joins points based on their distance within a threshold. If you don't have the means to put together something in postgis, a qgis virtual layer would work, but since it uses Spatialite it lacks st_dwithin so you'll have to buffer one of the layers then check st_within. I'll post an answer later with more details. – Jesse McMillan Jan 27 '17 at 1:21
  • This sounds like an excellent idea - hadn't thought of postgis at all, and already running a local instance of the opengeo server, so this would be pretty feasible. Will look forward to hearing more. – Jeremy Kidwell Jan 27 '17 at 15:17
1

I would solve this problem using PostGIS.

First, upload both layers to your PostGIS database. An easy method would be to use QGIS' Database Manager Plugin

Make sure to check 'geometry column' and 'create spatial index'.

Once they're both there, you can open the SQL window (F2) within database manager.

First we need to find a good tolerance (fuzzyness?). I uploaded your sample data as file1 and file2. You can use an inner join to only find matching records:

SELECT file1.*, file2.* 
FROM file1 INNER JOIN file2 
ON st_dwithin(file1.geom, file2.geom, 15)

INNER JOIN in database manager

The third argument in st_dwithin (15 in the screenshot) is the maximum matching distance (in whatever measure of distance your CRS uses). I'm sure you could use a much larger number than 15, but you may need to use trial and error to find an optimal amount.

Now the inner join is useful in itself, but I'm assuming you probably want a table that has all the records from both tables including unmatched ones. We can do a double-join to create that. (postgresql is a little weird about full outer joins...)

SELECT file1.*, file2.* 
FROM file1 LEFT JOIN file2 
ON st_dwithin(file1.geom, file2.geom, 15)
UNION all
SELECT file1.*, file2.*
FROM file1 RIGHT JOIN file2
ON st_dwithin(file1.geom, file2.geom, 15)
WHERE file1.id IS NULL

FULL JOIN using two joins

Now you can either create a view or load as a new layer (see checkbox in lower left).

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.