Drawing routes (on road shapefile) between X,Y coordinates in QGIS

Question

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:

Each row is ONE (1) route.

Answer 1

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) & ")"

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 ",".

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.

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.

Answer 2

I can suggest a PyQGIS solution. It utilizes the openpyxl Python package.

Let's assume there is an Excel file called 'testbook' with the following data provided on the 'Sheet1':

Proceed with Plugins > Python Console > Show Editor (see documentation) and paste the script below:

# imports
from os.path import realpath, isfile
from openpyxl import load_workbook
from qgis.core import QgsVectorLayer, QgsField, QgsFeature, QgsPointXY
from qgis.PyQt.QtCore import QVariant
from qgis.utils import iface

# providing a path to the Excel file
path_to_excel = realpath("D:/qgis_test/testbook.xlsx")
# loading Excel file with a coresponding sheet
if isfile(path_to_excel):
    book = load_workbook(path_to_excel)
    sheet = book.get_sheet_by_name("Sheet1")

# getting field names and data from the Excel file 
data = [list(value) for value in sheet.values]
attrs, records = data[0], data[1:]

# creating a vector layer
layer = QgsVectorLayer("LineString?crs=epsg:4326", "flows", "memory")
# accessing the vector layer provider
provider = layer.dataProvider()

layer.startEditing()

# adding attributes to the vector layer
for attr in attrs:
    if attr == 'id':
        provider.addAttributes([QgsField(attr, QVariant.Int)])
    elif any(i in attr for i in 'XY'):
        provider.addAttributes([QgsField(attr, QVariant.Double)])
    else:
        provider.addAttributes([QgsField(attr, QVariant.String)])

layer.updateFields()

# adding data to the vector layer attributes
for record in records:
    feat = QgsFeature() # creating a feature
    feat.setAttributes(record) # feeding feature with attribute values
    pt_from = QgsPointXY(record[4], record[3]) # starting point geometry
    pt_to = QgsPointXY(record[6], record[5]) # ending point geometry
    feat.setGeometry(QgsGeometry.fromPolylineXY([pt_from, pt_to])) # creating line geometry
    if feat.isValid():
        # adding feature to the vector layer
        provider.addFeature(feat)
    layer.updateExtents()

layer.commitChanges()

if layer.isValid():
    # adding layer to the Project
    QgsProject.instance().addMapLayer(layer)
    iface.zoomToActiveLayer()

In the code above, please do not forget to change the "D:/qgis_test/testbook.xlsx" and "Sheet1" variables before running it.

Press Run script and get the following output:

---

References:

• Reading Excel file using PyQGIS
• Adding LineString from points using PyQGIS
• PyQGIS 101: Creating & editing a new vector layer
• Joining vector layer with CSV-file that contain duplicates in QGIS
• Creating new empty vector layer with PyQGIS
• Adding LineString from points using PyQGIS