4

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!

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
9
  • Thanks for the tip - although i am afraid in this case join doesnt work so easily as i dont 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 dont want all the sections of every road, only specific sections - but if i JOIN by road nro, then it joins ALL sections of that particular road. Joining by section nro is ofcourse no better as there are plenty duplicate sections nro (for different roads naturally).
    – iippo
    Commented Oct 18, 2012 at 12:49
  • perhaps you can be more explicit about the data you want to extract out of that huge shapefile. Are the roads all in one district? Perhaps you can make your own unique ID by combining roadnr and sectionnr in a new field...
    – PieterB
    Commented Oct 18, 2012 at 12:53
  • But, can you combine the roadnr and sectionnr to a new unique nr (both, in the shape and excell)? So you can join them on that new created ID.
    – PieterB
    Commented Oct 18, 2012 at 13:26
  • The roads are all over the country and there's maybe 400k km of them, so i would like to avoid editing anything by hand :) ...Also what i noticed, is that as i JOIN by road nro and there are several of the same nro's in the .xls, it actually only picks the biggest road section nro (the next column after road nro) and attaches (+ the the length in the third, last, column) that to all the lines of that particular road in the shape file.
    – iippo
    Commented Oct 18, 2012 at 13:32
  • 2
    Is it possible to share your shapefile and exel so we can look after the real data? your example isn't clear to me
    – PieterB
    Commented Oct 18, 2012 at 13:46
3

If you have a road no., section no. and length field in both your spreadsheet and your shapefile, and they correspond (match), then you could try to join the spreadsheet to your shapefile by doing the following:

Make a 'combined' field that is the combination of road no., section no. and length in both your Excel spreadsheet and your shapefile. This 'combined' field should be unique in both.

Perform the attribute join on the combined field.

Then you can select the road segments in the shapefile that have a corresponding record in the spreadsheet.

Unfortunately, if you can't establish a 1:1 match between records in the shapefile and in the spreadsheet, you won't be able to select the features you want in the shapefile.

Improve this answer
1

I believe this will answer you question

Joining non-spatial CSV file to spatial data (shapefile) using QGIS?

There are various options.

Improve this answer
1
  • I am not yet really familiar with mmqgis, but it looks interesting - thanks for letting know about it, unofortunately i believe the end result is the same as already discussed above?
    – iippo
    Commented Oct 18, 2012 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.