This episode explores the intriguing question of putting rasters in a database. An insightful conversation about why you should or shouldn’t put rasters in a database – and how to do it when you need to.
About The Guest
Paul Ramsey is a renowned advocate for open-source geospatial software and co-founder of PostGIS. With expertise in databases for maps and locations, he specializes in a niche yet vital area of geospatial technology.
The Challenges of Storing Rasters in Databases
The prospect of storing rasters in a database stems from its inherent promise of fast data handling. And given the bulky nature of rasters and the need for swift data retrieval, it seems logical to leverage a database’s capabilities. However, the voluminous nature of raster data poses unique challenges. Here are some of the cons of storing rasters in databases.
Difficult To Manage Database
As a database scales up, it becomes increasingly challenging to manage. Usually, non-spatial databases that are considered large often weigh in at around 20 gigabytes. But raster data easily tips the scales at terabytes. This not only affects operational aspects like backup and restoration but also introduces added complexity around data replication and storage.
Furthermore, rasters are already indexed data structures, making them accessible without the additional layer of database integration. Therefore, in terms of speed, integrating rasters into a database offers little to no gain.
Underutilized Database Capabilities
Databases are fundamentally built to handle dynamic data and ensure fault tolerance, promising never to lose your data regardless of the circumstances. However, this strength is underutilized with rasters, which are rarely modified. This mismatch of requirements and capabilities further accentuates the difficulties of raster-database integration.
Cloud Native Raster Formats
To optimize the storage and retrieval of rasters without the complexities of database integration, cloud-native raster formats emerge as a viable solution. These formats store data in convenient, easy-to-access structures, enabling efficient retrieval of relevant portions of the data. This eliminates the need to download massive datasets for small requirements and simplifies data management.
Benefits Of Storing Raster Data in Databases
Despite the challenges, there are legitimate reasons to consider raster-database integration. Rasters excel at representing surface information across large areas, such as rainfall or elevation. Geospatial professionals working with vectors can benefit from querying rasters and vectors simultaneously, as it can yield valuable insights. By storing rasters and vectors in the same database, complex analytics using SQL tools become possible, enhancing the capabilities of GIS operators. This fusion opens up new avenues for geospatial analysis, making it a compelling area of exploration despite its challenges.
Out-DB Rasters
An alternative approach to storing rasters in databases involves using out-of-database (out-DB) rasters. This method entails storing references or pointers to external pixels instead of the actual pixels. A metadata shell is created in the database, containing all necessary information about the underlying data. This flexibility makes it possible to leverage cloud-native raster files through your database, allowing for seamless access and analysis of large raster datasets without the need for extensive local storage.
Connecting To Out-DB Rasters With GDAL
For out-DB rasters, GDAL plays a crucial role in connecting the database with the actual raster data, which could be stored anywhere from a local file system to a cloud-based storage location such as an S3 bucket or Azure blob. In this regard, GDAL acts as a bridge, creating virtual files that point to the data stored in various locations.
However, while the concept of virtual files offers impressive flexibility, it comes with a significant caveat. Since the data isn’t local to your computing environment, accessing it may involve a certain degree of latency. This latency can be even more pronounced if the database and the geospatial data aren’t located within the same cloud.
In-DB Rasters
In-DB rasters refer to raster data that is stored directly within the database. In this process, each pixel of your raster data is stored in the database, making the data immediately available for analysis.
Rasters can be loaded into PostGIS using raster2pgsql, which can handle a multitude of raster formats. It allows you to set some flags determining how the data will be loaded into the database and then generates a SQL file that can be piped directly into your database.
PostGIS Raster Module
The PostGIS Raster Module is an extension of the PostGIS system that caters to the needs of handling raster data. It provides tools for reading raster information, populating the Z dimension of vectors with data from underlying rasters, and more. The module was designed optimistically, envisioning users leveraging more advanced raster functionalities such as:
Vector-Raster Conversion
With the PostGIS raster module, you can convert vector data—points, lines, or polygons—into raster geometry. Conversely, a categorical raster can be transformed into a polygon, facilitating conversion between these two formats. This conversion flexibility is particularly useful for creating masks, processing elevation data, and other applications where raster and vector data need to interact.
Raster Map Algebra
Raster Map Algebra functionality allows you to perform mathematical operations on raster pixels, such as adding two rasters together, subtracting one from another, or creating a new raster from calculated results. While the potential seems limitless, the practicality of this feature faces some execution thread constraints, since PostGIS, being an extension of Postgres, can only leverage the core capabilities provided by the Postgres database.
Parallelism In PostGIS Raster Operations
While raster data processing is inherently parallelizable, it’s not a good fit with traditional database models. SQL-based systems typically operate on a row-by-row basis and lack the detailed knowledge about the data structure that raster-native frameworks possess. And even though the architecture of PostGIS can facilitate some parallelism, it can’t spread the workload across an extensive number of cores. The level of parallelism is dependent on what the core database (Postgres) provides.
Tools like GeoTrellis or Google Earth Engine, designed explicitly for raster data, can efficiently distribute the processing load across multiple nodes. However, the database, unlike these specialized tools, is not aware of the intricate data structure of rasters. It merely views the data as a sequence of rows, making it difficult to exploit the inherent parallelism of raster operations.
Exporting Raster from SQL
When it comes to exporting raster data, SQL offers some flexibility. For instance, it supports visual formats like PNG and JPEG, along with the GeoTIFF format. Although it does not support all GDAL output formats, the flexible GeoTIFF and two visual formats usually suffice for most use-cases.
Should You Store Rasters in A Database?
Whether you should store your rasters in a database depends on your specific needs. If your rasters are non-visual data and your questions involve mapping your vectors to these rasters, it might be worthwhile. This is particularly beneficial if the questions you’re asking are ever changing, as SQL provides flexibility in handling different questions. However, if you’re only running the same query repeatedly, you might find it more efficient to create a script that accesses the raw files directly, rather than storing them in a database. Ultimately, the choice depends on your unique use-case and needs.
Related podcast episodes
In Conversation
Why You Shouldn’t Put Rasters in a Database
Daniel: Paul, welcome back. I have a database, I love having a database, databases are great for large-scale data — surely the correct thing to do is put my rasters in my database?
Paul: And then we say: oh, for the love of God, no — please don’t put your rasters in the database. Your rasters are very, very large, and the larger your database gets, the harder it is to manage. People with very large operational databases in the non-spatial world will say “my database is 20 gigabytes” — and at a database company we kind of laugh, because that whole database fits in memory. People with rasters walk in with a couple of terabytes just for a start, and that changes the operational characteristics a lot — backups, replication. Databases are also built for dynamic data: the contractual guarantee is “I will never lose it.” Raster data doesn’t change — it rarely gets edited. And it’s not any faster, because raster data is intrinsically already indexed — it’s a great big grid. So in terms of scale it makes your database harder, in terms of speed it’s not any faster, and you don’t get the most out of what a database is for.
Cloud-Native Raster Formats
Daniel: It sounds like you could be a great spokesman for cloud-native raster formats.
Paul: Cloud-native raster formats take the fact that rasters already put their data into very useful, easy-to-access structures and ramp that up to ten, being very careful that nothing is inefficient about how the data is stored. In the cloud, all data files are linear hunks of bytes; cloud-native formats deliberately put the bytes in places where it’s convenient to quickly access little pieces of the file, so they don’t have to download two terabytes of image to get the 500k you actually want to look at.
When It Does Make Sense
Daniel: There’s still a part of me that wants to try anyway. Are there legitimate reasons?
Paul: Rasters are great for representing continuous surface information across a large area — the classic non-visual raster is an elevation model. If you’re a geospatial person, you probably also have some vectors of interest, and maybe there’s an aspect of the raster you’d like to interrogate with respect to the vectors: for every address point, how high is it — which tells you whether it’s going to flood. There’s a whole category of raster-vector analytical questions, and if you don’t have the data inside the database you have to write a script to do that integration. If your database can hold rasters and vectors at the same time, you can answer those questions using the same SQL tools — and any third-party tool attached to the database can do raster-vector GIS analytics too.
In-DB vs Out-DB Rasters
Daniel: How would I go about getting my rasters into the database?
Paul: We’re talking about PostGIS, because that’s what I know. You have two options. You can literally put your data in the database — every pixel resides inside the database, and we call those in-DB rasters. Or you can load just little pointers that say “over here in this file there are some pixels you might want to look at” — out-DB rasters. Either way you use a loading tool — the one that ships with PostGIS is called raster2pgsql. It reads a whole pile of raster formats, lets you set flags, and generates a SQL file you pipe into your database. You still tile the space, because input rasters are always quite large; in the in-DB case you want tiles smaller than the page size, which is 8 kilobytes — so not much bigger than 32×32 or 64×64 pixels.
Out-DB Rasters and GDAL
Daniel: What does an out-DB raster actually mean?
Paul: Instead of loading a little chip with all the pixels, we load a little chip that still has bounds and information about bit depth and number of bands, but instead of the pixels it says “you can access them over here.” If you go into the source code, the “over here” part is literally just a GDAL connection string — which, when you think about what GDAL can do for file access, makes your head explode. It’s not just a file on a file system; it can be an S3 bucket location, an Azure blob URL, or a standard URL on a web server. It opens up a whole new way of thinking about where you store your data — being free of file systems gives a lot of system design flexibility. It comes with the same warning label as any virtual file: if you ask for too much of it, it’ll get slow, because it’s not local to your computing.
Daniel: So I could point this at something like the Microsoft Planetary Computer, interrogate it, update my vectors, and cut the connection?
Paul: That’s exactly right. You can take the data in place and start working with it “in your database,” even though the raster data is in fact living somewhere else.
Raster Functions in SQL
Daniel: Tell me about the raster functionality in SQL.
Paul: It starts with the raster-vector side. You can take any vector — point, line, or polygon — and rasterize it, and vice versa: take a categorical raster and vectorize a category into a polygon. That’s useful for building masks — rasterize housing footprints, drop the mask across the elevation model, summarize, and now you have the average elevation underneath the house. For any point, you can read the raster value off the underlying raster. The module was also built with a great deal of optimism that people would do more advanced raster GIS — there’s a raster map algebra facility, so you can do math on the pixels: add two rasters, subtract bands. It’s crazy powerful, but within the constraints of the tile storage model.
Parallelism and the Future
Daniel: Raster math feels embarrassingly parallel — will we get to the stage where the database spreads it across 64 cores?
Paul: I don’t think so — not because we don’t want to, but because the architecture of the underlying database isn’t well suited to it. PostGIS is a creature of Postgres, so we get precisely what the core database provides — up to 4-to-16-wide parallelism depending on how the query plan falls out. It’s embarrassingly parallel in raster space because a raster-native tool knows ahead of time it has a perfectly segmented input data set. The database just knows “I’ve got a row, I’ve got a row.” When I look at the future of rasters and the database, it’s: first of all, not in the database. The platonic correct way is to expose rasters to vector processing using a GDAL raster source as the core metaphor — that gets you away from thinking about tile sizes and where things are stored.
Daniel: So, should you store your rasters in a database?
Paul: If your rasters are non-visual data, and you have questions that involve moving your vectors to those rasters, then you should probably consider it — particularly if the questions are ever-shifting, so you get a benefit out of the flexibility of SQL. If it’s only one question over and over again, then running a script against the raw files will be faster, more efficient, and easier to deploy. So my definitive answer is: it depends. I am a computer programmer — that is pretty much my answer to any question.
