I'm performing some basic spatial analyses on a large database of traffic injuries in California between 2003 - 2011. Because the dataset is large (nearly 1 GB of points), I'd like to first cut it down to a specific geographic region using a spatial query, but I find that QGIS consistently freezes and hangs if I try to use a spatial query or filter the layer.

What are some ways I can work with this dataset? Are there more efficient data formats I can use?

  • 1Gb isn't all that descriptive, since the width of the dBase file record width is going to be the major determinate in how many features this represents (assuming the 4000 byte record limit is honored, the limit ranges from 25+ million features to ~250k features). With a three order of magnitude range, knowing the rough order of magnitude on the feature count would be useful. – Vince Jul 28 '14 at 20:29
  • Be careful when working with shapefiles that have components greater than 1GB. The absolute maximum any component can be is 2GB... merging, adding or editing datasets this large I would strongly advise against as they are prone to corruption and when that happens you will loose all your work. You could do OGR2OGR using a clpsrc to extract the area. – Michael Stimson Jul 28 '14 at 21:58

Load the data into SpatiaLite in QGIS... best way is to create a new SpatiaLite database via the right-click GUI in the browser window, then simply drag and drop your shapefile onto the SpatiaLite database you just created.

From there you have all the power of SpatiaLite at your disposal, including SQL and SQL Spatial functions, which are a way more intuitive way to work with this kind of data.

Some more documentation can be found here, and there's the SpatiaLite manager plugin.

  • 1
    This worked quite well. Specifically in my case, I had to import to Spatalite, and then use an SQL query to limit the dataset as much as possible before moving on to other types of queries. Thank you! – jkho_ Jul 29 '14 at 0:27
  • @user35138 that's great! my pleasure and glad I could help! – DPSSpatial_BoycottingGISSE Jul 29 '14 at 15:18

I would suggest to export the shapefile(s) to SQlite or FileGDB (which should perform better in QGIS). You can either do this in QGIS GUI (right click on layer a select Save As), or use OSGeo4WShell to covert using ogr2ogr, e.g. command:

ogr2ogr -f "FileGDB" C:/Temp/myshp.shp C:/Temp/test.gdb


ogr2ogr -f "SQLite" C:/Temp/myshp.shp C:/Temp/test.sqlite

*Note, if you are editing the data I would suggest SQLite, since you cannot edit FileGDB in QGIS, however either option should suffice for viewing.

  • file geodatabase??? – DPSSpatial_BoycottingGISSE Jul 28 '14 at 20:26
  • Yes, file geodatabase. – artwork21 Jul 28 '14 at 20:27
  • that's the first time I've seen a suggestion that someone move INTO a proprietary system as a solution versus using an open source solution to move OUT of one... interesting! – DPSSpatial_BoycottingGISSE Jul 28 '14 at 20:34
  • In many cases walking the high wire between proprietary and non-proprietary may be needed. QGIS is flexible in that manner at least for viewing data. – artwork21 Jul 28 '14 at 20:42
  • Esri published the fGDB library so it is no longer truly proprietary. Despite any misgivings on Esri that some people may have the fGDB format is awesome: 3 levels of spatial index, maximum size (depending on config keyword) 2TB - that's a LOT of vector data!! IMO the best vector data format that is file based. – Michael Stimson Jul 28 '14 at 22:01

Perhaps use GRASS to Clip the area? You could run this in command prompt, and it wouldn't require loading the dataset with QGIS first.

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.