I have an idea for a project that would build a free worldwide 3D scan of the real world. You may think of it as a high-definition version of OpenStreetMap (OSM) project. The point cloud data would originate in LIDAR or stereo camera 3D scans. I am thinking of possible database solution for the project.

The main problem would be storing trillions of data primitives (point cloud points). Each point would have few properties (coords - lon, lat, elevation; intensity, RGB values, some semantic tags). I think the scenario is mostly similar to OSM but with way more data (easily 1000 times the OSM data but most likely much more).

Real spatial features are not neededn it the database and only a queries by location (with the help of geohash or b-tree indexes?) are required.

OSM uses plain Postgres (not PostGIS) because they do not need any geometry operations on top of their nodes. Today (October 2016) the OSM project contains 3.5 billion of nodes in a plain Postgres db table that runs on a single server. The whole database (including ways, relations, editing history etc.) has a size of about 6 TB (see server hardware).

Since I need 1000x the scale of OSM it is clear that many servers will be needed. Can someone help to at least roughly outline how the system may look like? I think data fragmentation may be done geographically by dividing Earth's surface into squares and have one table_points (=one database?) per such patch - lets call these "slave servers". There would be a central "master server" (not a db) that would manage queries for slaves (in case data from multiple slaves would be needed) and handle data returns to clients. Would such model be viable?

I can think of some very sophisticated fully distributed database system but I think at the beginning something very simple like simple Postgres slaves (maybe with few read-only replicas for faster reads) could be sufficient for the basic functionality.

Note: I found the pointcloud extension for Postgres (explanatory video here). It seems to group points into "patches" to minimize table row count. Would this make DB faster?

EDIT: I am also exploring distributed NoSQL databases like Cassandra or CouchDB. Also thinking about a DIY solution with people having nodes at home rather than using a datacentre (mostly due to financial reasons - datacentre needs to be paid). But with a "flat worldwide NoSQL" (read: data not fragmented by geo coords) I am not sure how queries would work (speed etc.).


I have dealt with something very similar to this. This is quite a broad problem but I'll try to offer the pointers I can after a year (and then the rest) of dealing with massive geographic data sets.

I'm not saying "do these", I'm saying these ideas will be resourceful depending on your hardware situation and software needs. You clearly already have good ideas about distributing out the data, that's good.

Firstly, I'd make sure not to have monolithic tables with too many sets of datatypes. Normalise out - I've found that splitting your geospatial columns into an entirely separate table.

Secondly, write down the sort of queries you'd expect a typical client to need. There's a 'chicken and egg' situation here, but I always like to write the SQL then design the table and indexes around that. Rick James' blog (former DBA at Yahoo!) always has nuggets of wisdom on this.

Thirdly, shard by location, which you have already touched upon. You can try doing this by 5-10 degree boxes around the world by latitude and longitude, depending on how dense your data is. Or if your data density is not uniform around the world, try a more sophisticated approach based on densities.

Our approach to sharding involved creating multiple Amazon RDSs, and then having a meta data table that mapped a range to the correct database and table. This is a simpler approach if you have the cash - personally I was against this due to the spiralling costs, but the uptime of AWS is top-notch. Something for you to think about.

Good luck, sounds like a cool project!

| improve this answer | |
  • Thanks for valuable answer! I added some NoSQL thoughts to the question - are you interested in replying to those too? – Kozuch Oct 30 '16 at 11:17
  • You're welcome. While I've dealt with Redis I personally haven't dealt with Cassanda or CouchDB. I know that Redis have added some really cool geospatial features in their latest version that will be useful for you. Just remember to back them up with memory and disk space to get the most out of them! – jonbaldie Oct 30 '16 at 11:23
  • Redis? Interesting. The more I read about NoSQL the less I think my "project" can be done with traditional relationship DBs... – Kozuch Oct 30 '16 at 23:47
  • The first time I used Redis in a data cache, my jaw dropped it was so fast. It's all completely in memory, backed up on disk. Other NoSQL tools are similarly fast. – jonbaldie Oct 31 '16 at 10:47

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