QGIS – Converting String of Coordinates to WKT

Question

I have a CSV file with a field that is in the following format

Latitude Longitude Altitude Accuracy, Latitude Longitude Altitude Accuracy [etc]

e.g. 51.1 -4.2 100 5, 51.2 -4.1 110 5, 51.1 -4.1 200 10 [not 3 real points!]

These represent nodes / vertices on a line, stored in a single "cell"

I would like to import this CSV into QGIS and there is an obvious problem for me if I try to formulate a LINESTRING ZM() – the x and y values are transposed so QGIS imports a line Y,X rather than X,Y.

To explain what I thought of doing (so you can see that I'm trying!):

using Excel I can create a new column and a formula

="Linestring ZM("& G2 &")" [where G2 is the cell with string of coordinates]

Then import the csv as Delimited Text – QGIS detects this field as WKT, but can't differentiate the Y,X

The other option would be to import the csv "raw" as a table and then process it as a virtual layer, but I can't get LINESTRING ZM geometry to work in my SQL – I thought it might be

ST_GeomFromEWKT('SRID=4326;LINESTRING ZM((' || "[field]" || '))') as geom

but that doesn't work (in QGIS 3.20)

I know KML lists its coordinates as Latitude Longitude so I'm wondering if there is another format that can accept the coordinates in this order?

Or is there a simple script to swap coordinates from Y,X to X,Y?

There are many rows so manual editing is not an option…

Answer 1

You can read the csv file using python:

import csv

#Create a line vector layer
vl = QgsVectorLayer("LineString?crs=EPSG:4326&index=yes", "myLayer", "memory")
provider = vl.dataProvider()

with open(r'/home/bera/Desktop/lines.txt', newline='') as csvfile:
    spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
    for e, row in enumerate(spamreader):
        if e>0: #Skip header
            f = QgsFeature() #Create a new feature
            linevertices = []
            row = [float(i.replace(',','')) for i in row]
            for i in range(0,len(row)+1,4): #For each group of four values
                vals = row[i:i+4]
                if len(vals)==4:
                    Latitude, Longitude, Altitude, Accuracy = vals
                    linevertices.append(QgsPoint(Latitude, Longitude, Altitude)) #Create a point and append to list
            gLine = QgsGeometry.fromPolyline(linevertices) #Create a line from the point list
            f.setGeometry(gLine)
            provider.addFeature(f)

QgsProject.instance().addMapLayer(vl)