I have a 7GB GeoJson file that I would like to load into a PostGIS database. I have tried using ogr2ogr but it fails because the file is too big for ogr2ogr to load into memory and then process.

Are there any other alternatives for loading this geojson file into PostGIS?

The ogr2ogr error I get is:

ERROR 2: CPLMalloc(): Out of memory allocating -611145182 bytes. This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information.

  • 1
    Have you tried the "-gt" option? By default it groups 200 features per transaction. – Pablo Oct 31 '11 at 0:39
  • I wasn't aware of the -gt option and had not tried it before. I just attempted a re-run using the -gt option, though, and unfortunately encountered the same error. I also attempted to use the -WHERE option to limit the number of searchable options, but that did not seem to help either. – RyanKDalton Oct 31 '11 at 1:14
  • GDAL/OGR has improved reading of large GeoJSON files in 2.3.0 which greatly reduces the memory overhead. – AndrewHarvey Sep 14 '18 at 11:28

The sample that you sent shows that it may be possible to manually split the file using an editor like notepad++

1)For each chunk create a header:


2)After the header place many features:

{"geometry": {"type": "Point", "coordinates": [-103.422819, 20.686477]}, "type": "Feature", "id": "SG_3TspYXmaZcMIB8GxzXcayF_20.686477_-103.422819@1308163237", "properties": {"website": "http://www.buongiorno.com", "city": "M\u00e9xico D.F. ", "name": "Buongiorno", "tags": ["mobile", "vas", "community", "social-networking", "connected-devices", "android", "tablets", "smartphones"], "country": "MX", "classifiers": [{"category": "Professional", "type": "Services", "subcategory": "Computer Services"}], "href": "http://api.simplegeo.com/1.0/features/SG_3TspYXmaZcMIB8GxzXcayF_20.686477_-103.422819@1308163237.json", "address": "Le\u00f3n Tolstoi #18 PH Col. Anzures", "owner": "simplegeo", "postcode": "11590"}},

3) Finish the chunk with:


EDIT - Here is python code that will split the file in pieces of defined size (in number of features):

import sys

class JsonFile(object):
    def __init__(self,file):
        self.file = open(file, 'r') 
    def split(self,csize):
        while True:
            output=open("chunk %s.geojson" %(number),'w')
            if feature==']}':
                for i in range(csize):
                    if feature==']}':

if __name__=="__main__":
    myfile = JsonFile('places_mx.geojson')
    myfile.split(2000) #size of the chunks.
| improve this answer | |

Unfortunately JSON is, much like XML, badly suited for stream processing so almost all implementations require that the whole dataset be loaded in memory. While this is ok for small sets in your case there is no other option than breaking the dataset into smaller, manageable chunks.

Improving on Pablo's solution, here's one that does not require you to actually open and load the file into an editor and split by hand but tries to automate as much as possible the whole process.

Copy the json file onto a Unix host (linux, osx) or install cygwin tools on Windows. Then open a shell and use vim to remove first and last row from the file:

$ vim places.json

type dd to remove the first line, then SHIFT-G to move the end of the file, type dd again to remove last line. Now type :wq to save the changes. This should take just a couple of minutes at most.

Now we will harness the sheer power of unix to split the file in more manageable chunks. In the shell type:

$ split -l 10000 places.json places-chunks-

Go grab a beer. This will split the file into many smaller files, each containing 10000 lines. You can increase the number of lines, as long as you keep it small enough so that ogr2gr can manage it.

Now we are going to stick head and tail to each of the files:

$ echo '{"type":"FeatureCollection","features":[' > head
$ echo ']}' > tail
$ for f in places-chunks-* ; do cat head $f tail > $f.json && rm -f $f ; done

Go grab a snak. The first two commands simply create a header and footer file with the correct contents (just for convenience really), while the last will add header and footer to each of the chunks that we split above and remove the headerless/footerless chunk (to save space).

At this point you can hopefullyprocess the many places-chunks-*.json files with ogr2ogr:

$ for f in places-chunks-*.json ; do ogr2ogr -your-options-here $f ; done
| improve this answer | |
  • 1
    With this method, wouldn't we have to make sure that file "chunks" were split at the end of a feature block? Since I already pre-processed the data in Python to add the header & footer info, I should be able to add a counter to chunk the data. I will give that a go next. Thanks for the suggestion. – RyanKDalton Oct 31 '11 at 14:14
  • The example data you provided had one feature per line, that's why I went with split -l. If that's not the case with actual data then I'm afraid it's not going to work. – unicoletti Oct 31 '11 at 14:27
  • Yes, of course you are correct, where each feature is on a separate line. I wasn't thinking that one through all the way. – RyanKDalton Oct 31 '11 at 14:34
  • To remove the lines without opening the file. Remove the first line: sed -i "1d" places.json Remove the first 4 lines: sed -i "1,4d" places.json Remove the last 4 lines: head -n -4 places.json > places2.json – egofer Aug 3 '16 at 11:49

It should be straight forward to write a lazy reader and writer in Python that would convert your geojson file to the much smaller shapefile format or directly to SQL without doing it all in memory. Once converted, the native PostGIS tools can import large data sets. The geojson support in OGR is relatively new and there aren't any flags for handling large files.

If you can somehow share a manageable chunk of your file I could help you out.

| improve this answer | |

This has improved in GDAL 2.3.0 https://trac.osgeo.org/gdal/wiki/Release/2.3.0-News it's now much more memory efficient at reading large GeoJSON files.

| improve this answer | |

It's possible to load your data with FME Desktop. It's very easy.

| improve this answer | |
  • Will it process an extremely large file like this? – RyanKDalton Oct 31 '11 at 0:36
  • For example, split the file to many files before the transformation. hjsplit.org And import the news files in FME Desktop for the importation to PostGIS. – user3120 Oct 31 '11 at 1:42
  • 1
    probably, and if it dosent you can yell to support :) – simplexio Sep 6 '13 at 5:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.