Plotting a CSV file containing multiple co-ordinates of a route as points on QGIS

Question

I have a CSV file with 3700 routes, with each route represented by a row and the respective co-ordinates of that route in the following format. Since the distance of each route varies, the no of lat-lon pairs also vary.

Row 1 : "[[12.91859, 77.63022], [12.91886, 77.63042], [12.919260000000001, 77.62893], [12.916970000000001, 77.62861], [12.916900000000002, 77.63031], [12.917740000000002, 77.63055], [12.918050000000003, 77.62994], [12.918270000000003, 77.62909], [12.917780000000004, 77.62892000000001], [12.917440000000004, 77.62933000000001], [12.917460000000004, 77.62886], [12.917490000000004, 77.62952], [12.918080000000005, 77.62966], [12.917890000000005, 77.63045], [12.918420000000005, 77.63020999999999]]"

Row 2 :"[[12.89207, 77.6037], [12.89289, 77.59886], [12.90052, 77.60102], [12.90511, 77.60176], [12.9083, 77.6002], [12.911430000000001, 77.59993], [12.93045, 77.60059], [12.94439, 77.60292], [12.94408, 77.60538], [12.94295, 77.60504]]" .

.

. Row 3700 : ........

I am aware of plotting and the method for a single lat lon pair, but in this case there are multiple pairs within a cell of varying pair lengths. The no. of rows are way too high for me to do it manually.

Once I could get them to plot as points, I will use the Point to Path function to obtain the line of the route. (I presume I also will need a common id for each route for that to work)

Answer 1

• Add that CSV as a layer without geometry

• Open DB Manager, open the connection (double click) to the Virtual layers -> Project layers...

• Execute

  SELECT
    *,
    ST_GeomFromText(
      CONCAT(
        'LINESTRING',
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                <array_string_literal_column> ,
                ',', ''
              ),
              '] [', ', '
            ),
            '"[[', '('
          ),
          ']]"', ')'
        )
      )
    ) AS geometry
  FROM
    "<your_csv_layer>"
  ;
  

  Note that I assume here that the string literal is wrapped in " - take them out of the outer REPLACE parameter if that is not the case.

• Load as new layer, then work with it or make permanent through export

---

This below creates a valid geometry alright, but I can't make QGIS to do something with it, not even as an export...

• Open the field calculator, choose to add a new field of type geometry
• Use a sequence of replace statements to transform the array string literal into valid WKT:

  geom_from_wkt(
    concat(
      'LINESTRING',
        replace(
          replace(
            replace(
              replace(
                "<array_string_literal_column>" ,
                ',', ''
            ),
            '] [', ', '
          ),
          '"[[', '('
        ),
        ']]"', ')'
      )
    )
  )
  

  Note that I assume here that the string literal is wrapped in " - take them out of the outer replace parameter if that is not the case.
• Save/Export to spatially aware format (GeoPackage, GeoJSON)
• Load new file into project

Answer 2

Your file is a bit difficult to handle, but it is possible using Python:

import csv, re
csvfile = r"/home/bera/Desktop/gistest/coordinates.csv" #Change to your path

#Create a temporary line layer to add the lines to
templayer = QgsVectorLayer("Linestring?crs=epsg:4326&field=lineid:integer", "myLayer", "memory")
my_provider = templayer.dataProvider()

with open(csvfile) as f:
    reader = csv.reader(f)
    #For each row in the csv file
    for rownum, row in enumerate(reader):
        #print(row)
        #['[[12.89207, 77.6037], [12.89289, 77.59886], [12.90052, 77.60102], [12.90511, 77.60176], [12.9083, 77.6002], [12.911430000000001, 77.59993], [12.93045, 77.60059], [12.94439, 77.60292], [12.94408, 77.60538], [12.94295, 77.60504]]']
        
        #Use re to extract the numbers and convert them from text to float
        coordinates = [float(x) for x in re.findall("\d+\.\d+", row[0])]
        #[12.89207, 77.6037, 12.89289, 77.59886, 12.90052, 77.60102, 12.90511, 77.60176, 12.9083, 77.6002, 12.911430000000001, 77.59993, 12.93045, 77.60059, 12.94439, 77.60292, 12.94408, 77.60538, 12.94295, 77.60504]
        
        #Create a list of points
        points = [QgsPoint(lon, lat) for lat, lon in zip(coordinates[::2], coordinates[1::2])]
        #[<QgsPoint: Point (12.89207000000000036 77.60370000000000346)>, <QgsPoint: Point (77.60370000000000346 12.89288999999999952)>, <QgsPoint: Point (12.89288999999999952 77.59886000000000195)>, <QgsPoint: Point (77.59886000000000195 12.90052000000000021)>, <QgsPoint: Point (12.90052000000000021 77.60102000000000544)>, <QgsPoint: Point (77.60102000000000544 12.90511000000000053)>, <QgsPoint: Point (12.90511000000000053 77.60175999999999874)>, <QgsPoint: Point (77.60175999999999874 12.90830000000000055)>, <QgsPoint: Point (12.90830000000000055 77.60020000000000095)>, <QgsPoint: Point (77.60020000000000095 12.91143000000000107)>, <QgsPoint: Point (12.91143000000000107 77.59993000000000052)>, <QgsPoint: Point (77.59993000000000052 12.93045000000000044)>, <QgsPoint: Point (12.93045000000000044 77.60058999999999685)>, <QgsPoint: Point (77.60058999999999685 12.94439000000000028)>, <QgsPoint: Point (12.94439000000000028 77.60291999999999746)>, <QgsPoint: Point (77.60291999999999746 12.94407999999999959)>, <QgsPoint: Point (12.94407999999999959 77.6053799999999967)>, <QgsPoint: Point (77.6053799999999967 12.94294999999999973)>, <QgsPoint: Point (12.94294999999999973 77.60504000000000246)>]
        
        #And a line
        the_line = QgsLineString(points)
        
        #Create a new feature
        f = QgsFeature()
        f.setGeometry(QgsGeometry(the_line)) #Set its geometry to the line
        f.setFields(templayer.fields())
        f["lineid"] = rownum #And its id field to the number of the csvfile row
        my_provider.addFeature(f) #Add it to the temp layer
QgsProject.instance().addMapLayer(templayer) #Add the layer to the map

Answer 3

QGIS has multiple scripting choices that therefore offer multiple approaches. Other current answers give PyQGIS and SQL solutions, here is one in QGIS' native expression language, in a way which illustrates the considerably increased power it has developed in recent years with the with_variable function and arrays in particular.

To use the fragment below, import your CSV layer as LineString geometry type, but leaving the geometry null or invalid (or anything else). You will fill it in below. Import the route strings as a single text field, here I've called it AsReceived, change as needed. I'm not going into detail about the importing since I presume you have other fields of interest in the CSV and have that set up already in some way.

Now start the Field Calculator (through Layer Properties / Fields or the icon on the data table. Choose Update Existing Field and select <geometry>. Enter the expression below. Your geometries should now be updated. If you prefer, you could instead use this as an expression for a Geometry Generator symbology if there is some other meaningful geometry field you are displaying now anyway.

with_variable('flat',
  array_foreach(
    string_to_array("AsReceived", ','),
    to_real(ltrim(rtrim(@element,' ]'),' ['))
),
make_line(
    array_foreach(
      generate_series(0,array_length(@flat)-2,2),
        make_point(@flat[@element+1],@flat[@element])
    )
  )
)

It's worth explaining how this works.

• Line 1 defines a variable @flat...
• ...which busts apart the string at commas (leaving string fragments like [[12.89207 and 77.6037] (Line 3)
• ...removes the [ ] and extra spaces and turns into numbers (Line 4)
• ...and repacks into a flat array of those numbers a1,b1,a2,b2,... (Line 2)

We then transform this into points and those into a line.

• To do this, line 8 line iterates over an index, itself an array with @element=0,2,4... up to the number of points in @flat
• ...repacking @flat as an array of paired points (line 7,9)
• ...finally making those into a line (line 6)

I've assumed in the path strings, latitude comes first (i.e., your points are in India). If you're tracking whales off the coast of Svalbard with longitude first, or to adapt to different coordinate systems with x coordinate first, switch the order in make_point.