[GIS] How to join Shapefile and Excel data without unique ID column

qgisquery

I have one huge shapefile with lots of roads (polylines) along with various attributes and I have an Excel file containing some of these roads by three same attributes (road no., section no., length) – but no geometry.

I want to select this smaller group of roads from the big shapefile and save it as a new Shapefile. Note: I don't have any unique index number or such for any particular piece of road: so for example road3 might consist of 100 pieces with road3 as road3 attribute and 1 to 100 as the section number. So the problem is that I don't want all the sections of every road, only specific sections – but if Ii JOIN by road no., then it joins ALL sections of that particular road. Joining by section no. is of course no better as there are plenty duplicate sections no. (for different roads naturally).

What do you think is the best way to accomplish this?

Is this finally the time I should start looking into "SQL/Spatialite for dummies"?

If possible, I'd like to survive with QGIS and M$ Office, but if necessary i can get my hands on arcgis and mapinfo at work.

Thanks!

Best Answer

I checked your data. As I already mentioned in my comments and like Rayner said in his answer, you have to make your own unique ID in both, the shapefile and the excel.

For the shapefile:

Open attributetable, activate toggle editting and open the fieldcalculator.

enter image description here

Set all parameters like showed in the picture above and you wil get a new column filled with a unique ID

enter image description here

In your excel:

Populate a new column with the following expression: ="R"&A2&"_"&"S"&B2.

--> notice the difference between qgis and excel if you want to add a charactre. QGIS uses ' and excel uses ".

Now you can join the excelfile to your shapefile. http://www.qgis.nl/2012/07/17/direct-gebruiken-van-data-uit-excel-bestand/?lang=en and http://www.qgis.nl/2012/07/13/koppelen-van-data-uit-csv-bestand/?lang=en

Save it as a new shapefile.

Then query the new shapefile on one of the new added attributes. So you will get the selection you want.

Related Solutions

[GIS] Display colour coded road traffic data in QGIS

This would be easily done by simple Join, since an unique field CP was already given to both Point and Polyline layers.

A Virtual Layer can be helpful:

SELECT Polyline_data.*, Point_data.AllMotorVehicles
FROM Point_data JOIN Polyline_data
ON Point_data.CP = Polyline_data.CP

This query will return a new polyline layer with AllMotorVehicles field (copied from point layer), which can be used to color code these line segments.

[GIS] Count number of points per line in QGIS

You can use Join by location to join polyline with point shapefiles. However, the point should be exactly snapped to the line in order to be joined, otherwise, it is better to create a buffer of polygon shapefile around the road lines of distance that you can decide after several tests, then use the buffered polygon to join the points by location. To find the tool, you can go to Vector -> Data management tools -> Join by location, as you can see below:

The output polygon will create a count field in the attribute table of the number of accident points:

I used QGIS 2.14.8 for the above process.

Related Question