I am in a bit of a weird work environment. I'm a data-scientist type person who is now doing more and more GIS work on some very large datasets - spatial regression, some mapping, spatial correlation, etc.

My data-engineer team are quite old-school and so instead of querying our spatial data directly, they only give us access to pre-processed shapefiles (most of my organisation is on Esri) on a network drive, to avoid storing data locally. Because of that, I tend to work in Python and have little SQL experience.

Managing large datasets with these is a pain - I tend to iterate over them in GeoPandas, and do most of my data manipulation in Python, which seems slow. I suspect ideally I'd do my initial querying in a database. I'm trying to find ways of improving that, but have some limitations!

I suspect the ideal solution would be a PostGIS SQL server, but we've been told that's impossible for now. I've considered a few options below...

  • Spin up a local PostGIS server on my machine?
  • aggregate those files into a SQLite/Spatialite local database
  • aggregate them into geopackage db

Do these make sense? Should I be looking at something else? Do any of those sound mad?

  • Shapefiles are slow and inefficient. Shapefiles on a network share have half the performance. Unfortunately, advice-seeking Questions are likely to be closed as opinion-based, and the only appropriate GIS SE place, Geographic Information Systems Chat, doesn't get the traffic which would be necessary and has a minimum reputation requirement. Note that once the geometries are in memory, this becomes and algorithmic challenge, not a I/O one, so you should carefully measure what you are doing. – Vince May 11 at 11:48
  • 1
    Also note that PostgreSQL is possible on any device more powerful than the average toaster-oven, so "impossible" is just plain wrong. – Vince May 11 at 11:56
  • I believe it is impossible to get an administrated PostgreSQL database if the admins say so. But if you are allowed to install your own PostGIS then do it. SpatiaLite and GeoPackage are also good but you need to know their limitations. GeoPackage does not have spatial functions while SpatiaLite does. Both two support spatial indexes but only with the special r-tree tables and you must learn to make the subqueries right. GeoPackage is native for QGIS and they play fine together. – user30184 May 11 at 12:15