3

I've got a column with 300 of start points and 300 of end points (X,Y coordinates for each point).

I now want each row in the column to connect with each other (e.g. X,Y Coordinate for Start Point, ends where X,Y coordinate for end point). This for EACH row. So it will be a total of 300 routes. The data I want out from this is the length (km).

Excel File columns:
Mission Place Delivery Place X Mission Place Y Mission Place X Delivery Place Y Delivery Place

Example of row 1..3:

enter image description here

Each row is ONE (1) route.

5
  • x and y coordinates are in one column separated by a delimiter or one column for each value? If you want to connect each point wich each other possible point that would be hugh number (300*299*288...) n! not 300 in total. Or do you mean point_1 to point_2 ... Can you give an example of your data / csv.
    – Xeppit
    Feb 21 at 14:23
  • I've updated the org. post. Have a look.
    – A M
    Feb 21 at 14:31
  • X,Y Mission Place & X,Y Delivery Place = 1 Route (which is equal to 1 row)
    – A M
    Feb 21 at 14:31
  • Just as straight lines from start to end? Or do you want to create a route following a road network you have?
    – BERA
    Feb 21 at 14:40
  • I have a road network! :) Shape file.
    – A M
    Feb 21 at 14:41

1 Answer 1

2

It's a fast fix:

The basic idea is to convert your points to a WKT Well-known text representation of geometry in EXCEL and export it as CSV data into QGIS. Therefore we create a new field (column) in EXCEL and call it geometry and construct a WKT LineString as Textthat can be read in qgis csv import.

In WKT two point coordinates (x,y) a seperated by a whitespace and the next point is seperated by a comma ",".

We can use:

POINT (30 10)

LINESTRING (30 10, 10 30, 40 40)

POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))

and some other options.

So we can construct a WKT for each row with the function TEXTJOIN in EXCEL.

In EXCEL use:

="LINESTRING(" & TEXTJOIN(" ";TRUE;A2;B2) & "," & TEXTJOIN(" ";TRUE;C2;D2) & ")"

enter image description here

Now you have a column called geometry

Important hint As you live in a country with a comma as decimal sepertaor you must convert your coordinates to a format with "." instead of ",".

enter image description here

Save your file as CSV in EXCEL.

Now in qgis import your CSV from menu --> Layer --> Add delimated Text Layer. Your geometry defintion is WKT and your geometry field is the geometry field you created in EXCEL. Choose Line as geometry type and your CRS.

enter image description here

Now you have a regular line layer with your fields and attributes in qgis and you can calculate what ever you want. Perhaps you must reproject your layer to get the length in km.

4
  • Where do i see the length in km? Can't find where the result is stated? My attribute table only consist of x_start, y_start, x_end and y_end
    – A M
    Feb 22 at 10:18
  • Also, when viewing the layer it is lines; it is not in line with the .shape file with the roads.
    – A M
    Feb 22 at 10:27
  • Than you have to provide an example / screenshot of your shape data. In which coordinate system is your road network and in which are your points and in which CRS do want to work. You will have to use a transformation from one crs to another. For your calculation you should use a projected crs and calculate it in gis qgistutorials.com/en/docs/3/calculating_line_lengths.html
    – Xeppit
    Feb 22 at 14:58
  • Yes. I don't understand. The points after your previous "fix" just give me lines that are completly off the road network..
    – A M
    Mar 1 at 14:22

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.