Join attributes by location doesn’t appear to be including data that it should

Question

I am trying to calculate the number of bus stops which are within 400m of c. 100 properties that are in relatively close proximity to one another.

Using MMQIS I created the 400m buffer for the properties, which was fine, but when they were displayed I noticed that quite a few overlapped one another. I then performed a ‘join attributes by location’, selecting my bus stop layer as the target vector layer and the 400m buffer layer as the ‘join vector layer’, and it quickly combined the two datasets. I initially thought this sequence of events was correct, but when I filter out some of the individual buffers which overlap it is clear that the combined dataset does not comprise all the bus stop data that features in the bus stop layer. And all the layers have the same CRS.

I’m a missing a trick? I need to find the answer as quickly as possible. Is there a simple alternative way for this to be achieved if this beyond the capabilities of join attributes by location?

Answer 1

If I understand your problem description correctly, the Points in polygon tool should directly compute you the number of bus stops (points) within 400 meters of each input feature (buffer).

Answer 2

OK, I’ve made progress with this – it’s been a bit of painful experience, but I’ve now got the answers I want. I’m sure there’s a more refined approached to sorting this out, so please step forward if you’ve got a suggestion, but to share it with you this is what I did:

1. Performed a ‘split vector layer’ on the my 400m buffer layer,
   which created over 100 shapefiles and I dumped these into their own folder
2. Located the ‘join attributes by location’ in the Processing toolbox and right clicked it then selected ‘execute as batch process’. I then populated the table that popped up with the individual buffer shapefiles and bus stop layer file – it was a slow process given the amount of files I had, but when hit run button the thing did what I wanted.
3. I then had over a 100 output files that I needed to merge. In the end I decided the best way to go was to combine the separate dbf files into a single dbf file in Excel. I did this using the instructions as below – all credit for this goes to somebody called Brian Johnson who posted this on the ESRI website, to which I extend my thanks

---

This is an Excel VBA solution. Hopefully the code is commented enough to follow.

1) Open a new Excel workbook. 2) Go to the VBA Editor and insert a 'Module'. 3) Paste the code shown below into the Module. 4) Save this Excel workbook (as an XLSM if you are using Excel 2007 or above). 5) Change the FolderPath assignment to the folder with your DBF files. 6) Run the Merge_DBF_Files sub. 7) You can use the Column A list of source file names to come up with an Excel formula to extract out the data you need from the file name using FIND, MID, etc. Delete other DBF field columns you don't need.

I tested this on a small set of DBFs and it works fine. Not sure how it will work with 1,000 files...

Brian Johnson

Sub Merge_DBF_Files()  

' VBA routine to merge all DBF files in one folder into one Excel worksheet  
'  Brian Johnson  
'  spiff88@gmail.com  
'  02/12/14  

    Dim SummarySheet As Worksheet, WorkBk As Workbook  
    Dim NRow As Long, FileName As String  
    Dim SourceRange As Range, DestRange As Range  
    Dim FolderPath As String  

' Create a new workbook and set a variable to the first sheet.  
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)  

' Modify this folder path to point to the files you want to use.  
    FolderPath = "C:\temp\DBF_Files\"  

' NRow keeps track of where to insert new rows in the destination workbook.  
    NRow = 1  
    i = 0  

' Call Dir the first time, pointing it to all DBF files in the folder path.  
    FileName = Dir(FolderPath & "*.dbf")  

' Loop until Dir returns an empty string.  
    Do While FileName <> ""  

' Open a workbook in the folder  
        Set WorkBk = Workbooks.Open(FolderPath & FileName)  
        Debug.Print Format(i, "00") & ":" & FolderPath & FileName  

' Set the cell in column A to be the file name.  
        SummarySheet.Range("A" & NRow).Value = "FileName"  

' Set the source range  
        WorkBk.Worksheets(1).Activate  
        RowCount = Application.CountA(Range("A:A"))  
        Set SourceRange = WorkBk.Worksheets(1).Range("A1:BB" & RowCount)  
        SummarySheet.Range("A" & NRow + 1 & ":A" & (RowCount + NRow - 1)).Value = FileName  

' Set the destination range to start at column B and be the same size as the source range.  
        Set DestRange = SummarySheet.Range("B" & NRow)  
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)  

' Copy over the values from the source to the destination.  
        DestRange.Value = SourceRange.Value  

' Increase NRow so that we know where to copy data next.  
        NRow = NRow + DestRange.Rows.Count  

' Close the source workbook without saving changes.  
        WorkBk.Close savechanges:=False  

' Use Dir to get the next file name.  
        FileName = Dir()  
        i = i + 1  
    Loop  

' Call AutoFit on the destination sheet so that all data is readable.  
    SummarySheet.Columns.AutoFit  

End Sub 

Answer 3

If you would like to do step 3 in QGIS you can use the "Merge Shapefiles to One" tool in the Vector>Data Management Tools menu. It allows you to select a folder and a geometry type and merges all the files in the specified folder.