3

I am trying to create a flow map and I have 150 million rows of data. I have tried to use QGIS's Virtual Layer (described in Creating virtual layers using Python and export them without using QGIS UI) to create the lines, but it is taking forever (I have an 8GB RAM). How can I create a GeoJSON of the lines as efficiently as possible?

My data looks like this:

TripID x1 y1 x2 y2
1 103.62183 1.27624 103.62181 1.29713
2 103.62542 1.39132 103.99923 1.37252
3 103.99156 1.27613 104.00284 1.37564

I need to create a line for every "tripID" using the two coordinates, then export as a GeoJSON.

Taras
  • 32,823
  • 4
  • 66
  • 137
sjp_1989
  • 569
  • 3
  • 11
  • 3
    I need to frame-challenge this, since GeoJSON is at least an order of magnitude larger than CSV, and doesn't provide for spatial indexing, so using it is going to be at least ten times slower. 150M rows is already a challenging task, and 8GB RAM is on the light side for GIS, so I wonder if this isn't an XY Problem. – Vince May 01 '21 at 13:39
  • The main advantage of creating a text from a text file is that it's IO bound and uses almost no memory. Perhaps OP only needs to create it, not use it. @Vince I agree. It would be better to generate a more compact format than the original. Shapefiles aren't it, obviously. Atlas BNA would be closer than JSON. At 8 bytes per float, with 4 floats per feature, you're already at 4.8 GB just for the geometry. If it's going to a dB somewhere after creation, maybe they will index it there. – wingnut May 01 '21 at 13:59
  • I required the output in GeoJSON format as it was the only accepted format in this case. But yes, file size definitely grew out of hand upon conversion to GeoJSON. In the end I had to aggregate my data at a higher level just to make the data smaller in the final output. – sjp_1989 May 04 '21 at 04:28

1 Answers1

12

I would just use a script to go from CSV to JSON. You don't need a GIS or any special libraries, just string handling. Here's a Python example.

First, set up some string placeholders:

header = '{ "type": "Feature", "properties": { "ID": '
seg1 = '{} '
seg2 = '}, "geometry": { "type": "LineString", "coordinates": [ [ '
seg3 = '{}, {} ], [ {}, {} ] ] '
seg4 = '} }\n'

Now read the CSV. For each line, create the GeoJSON for the simple segment format and save to a file:

with open('Segments.csv','r') as f:
    with open('OUTPUT.geojson','w') as g:
        # read the header line
        line = f.readline()
        for line in f:
            l = line.strip()
            _ = l.split(',')
            i,a,b,c,d = [float(x) for x in _]
            g.write(header+seg1.format(int(i))+seg2+seg3.format(a,b,c,d)+seg4)

Your CSV File

TripID,x1,y1,x2,y2
1,103.62183,1.27624,103.62181,1.29713
2,103.62542,1.39132,103.99923,1.37252
3,103.99156,1.27613,104.00284,1.37564

Output JSON

{ "type": "Feature", "properties": { "ID": 1 }, "geometry": { "type": "LineString", "coordinates": [ [ 103.62183, 1.27624 ], [ 103.62181, 1.29713 ] ] } }
{ "type": "Feature", "properties": { "ID": 2 }, "geometry": { "type": "LineString", "coordinates": [ [ 103.62542, 1.39132 ], [ 103.99923, 1.37252 ] ] } }
{ "type": "Feature", "properties": { "ID": 3 }, "geometry": { "type": "LineString", "coordinates": [ [ 103.99156, 1.27613 ], [ 104.00284, 1.37564 ] ] } }

Output Mapped from JSON in QGIS

enter image description here

This loads correctly in QGIS. I can't speak for other applications. I did it by:

  • creating a dummy segment in QGIS,
  • saving it to JSON and then
  • examining the format.

Only Python was required. No library imports or GIS was required.

  • ID was saved as an integer attribute type, as it looked like an integer
  • no formatting of floats was done for lat/long, as it was implicit in the CSV.
wingnut
  • 2,090
  • 5
  • 10