# Elevation data in a database (tens of billions of information)

I have a need of a service which gives me the elevation for a coordinate (latitude, longitude). There is a dataset called SRTM3 where you have this information in a list of files. Each file contains a representation of the elevation value in a 1201*1201 square and each elevation value is stored as a big endian integer. There are around 20 000 files in this dataset.

For example, for the latitude 50.4161 and longitude 14.12345, you would find the data inside one of these 1201*1201 values of the file N50E014.hgt.

My question is this : if I were to import all these data in a table, I would have around 20 000 * 1201 * 1201 = 28 848 020 000 records.

• Is it the right way to go in storing this in a database? If yes, what system would you recommend? The only request I would have to make is something like:

SELECT elevation
FROM elevation e
WHERE e.min_latitude < latitude
AND e.max_latitude > latitude
AND e.min_longitude < longitude
AND e.max_longitude > longitude

• Or should I just load the data from the raw file directly on request? But it means loading a file on each request (I could keep some of the most used in memory but it does not seem efficient).

EDIT: I was thinking about something more around the line of a task queue than opening the file on each request but let's forget this and keep our focus on database system. @Paparrazi said that a conventional database can handle this much data and advised with a cluster index. I thought of that previously but in this case, once the data are loaded, the only operation will be a read (never or almost never a write). It seems kind of useless to use these conventional databases with lot of feature like transactions, ... when you don't need it. Is there another alternative ? In bigdata oriented database (cassandra, mongo, ...) ?

EDIT2: I am looking into @MickyT answer with postgis. It seems to fit my needs. I will try to import the data into the index clustered table or the postgis raster one, benchmark the 2 solutions and post my result here.

• You may want to have a look at postgres with the postgis extension. It will store each of your raster tiles (20,000) and can be queried resonably simply with indexes. – MickyT Oct 1 '16 at 21:13
• @MickyT : but it won't change the fact that I will have 28 billions records and not 20 thousands ? – jobou Oct 2 '16 at 8:36
• No, you should only have to load up 20,000 raster images, assuming that the files you have can be treated that way. I'll try and write up a better answer tomorrow – MickyT Oct 2 '16 at 10:23
• @MickyT : I look into this and this is a nice solution. It seems that postgis is really optimized for this kind of processing. Moreover with gdal installed, I can directly use raster2postgres command line tool to import the HGT data (elevation values). Each file will be a raster row in the table with one band storing the elevation data. I am going to do some benchmark with both solution before I select the final one. – jobou Oct 2 '16 at 12:52

PostgreSQL with the PostGIS extension would appear to have everything you need to manage this data.

PostGIS has a fairly rich set of functions that deal with rasters like the files that you have described. Further documentation on enabling and using rasters is here.

Once you have the rasters loaded a query like the following should perform reasonably well

SELECT p.Identifier, ST_Value(r.rast, p.geog) AS elevation
FROM query_point p
INNER JOIN elevation_raster r ON ST_Intersects(r.rast, p.geog);
`
• Yes I did a small POC and it is perfect for my need. In order to improve the performance, I wanted to split each HGT file in sub tiles of 50x50. But SRTM3 is 1201x1201 which is a prime number. So I would have to mix 50x50 with 51x50 and 50x51. I don't know if I can mix different size of rasters in the same column. Is it possible ? – jobou Oct 2 '16 at 19:26
• I answered my question here : gis.stackexchange.com/questions/212771/… – jobou Oct 2 '16 at 20:12
• @jobou when reducing the file size I would take little steps to try and find the optimum. Personally I would look at ~500 x 500 and ~250 x 250 before jumping to ~50 x 50. You will probably find a tipping point. – MickyT Oct 3 '16 at 2:09
• you are right. My worry was about the different number of tiles in the same raster column but I will of course benchmark a few sample choices before selecting the final one. – jobou Oct 3 '16 at 8:02

That is around a TB of data. Many conventional data bases (e.g. MSSQL will handle that).

Clustered index on min_latitude, max_latitude, min_longitude, max_longitude may be the way to go.

Since you don't have any relationships a document db may be the way to go.

Consider a Cloud solution that can quickly scale.

In don't know much about big data. Hopefully you will get more answers.

• Okay, I imagine that I will need a pretty big server to handle an index on 4 columns containing float with around 1TB of data no ? Based on the fact that once loaded, I will only have to do read (so no transaction needed support), do you have an idea with another alternative like cassandra ... ? – jobou Sep 30 '16 at 18:03
• Cassandra would be an option. Not really my expertise. A clustered index does not require any space. – paparazzo Sep 30 '16 at 18:15
• Yes you are right, as the clustered index is sorting the database in the filesystem (if I understand correctly). I will need to do some benchmark I think before choosing a solution. A few other questions : with this much data and with the index on these 4 columns, how much RAM would I need ? And would you advise to create a primary key on the 4 coordinates column or to add a bigint integer autoincremented ? – jobou Sep 30 '16 at 18:24
• You have natural key - use it. Don't an identity. It is going to do some pretty efficient index scans so it will not need a mass of memory. – paparazzo Sep 30 '16 at 18:33
• Yes. I am hoping you have no duplicate coordinates. – paparazzo Sep 30 '16 at 18:38