Skip to main content

Iceberg Geo Type: Transforming Geospatial Data Management at Scale

Summary

  • Native Geospatial Support: Apache Iceberg is introducing native geospatial type support to standardize storage and management of spatial datasets at scale.
  • Unified Data Warehouse Layer: The Iceberg geo type establishes a standard data warehouse storage layer for the geospatial community, eliminating fragmented file formats.
  • Optimized Analytics Workflows: Integration with Apache Sedona and Spark streamlines geospatial analytics queries and accelerates spatial data processing.

Iceberg Geo Type: Transforming Geospatial Data Management at Scale

Watch: Iceberg Geo Type: Transforming Geospatial Data Management at Scale
Talk by Jia Yu, Co-founder and chief architect, Wherobots Inc. ; Szehon Ho, Software Engineer, Databricks
The Apache Iceberg™ community is introducing native geospatial type support, addressing key challenges in managing geospatial data at scale, including fragmented formats and inefficiencies in storing large spatial datasets. This talk will delve into the origins of the Iceberg geo type, its specification design and future goals. We will examine the impact on both the geospatial and Iceberg communities, in introducing a standard data warehouse storage layer to the geospatial community, and enabling optimized geospatial analytics for Iceberg users. We will also present a live demonstration of the Iceberg geo data type with Apache Sedona™ and Apache Spark™, showcasing how it simplifies and accelerates geospatial analytics workflows and queries. Finally, we will also provide an in-depth look at its current capabilities and outline the roadmap for future developments, and offer a perspective on its role in advancing geospatial data management in the industry.
Here's more to explore:
Databricks named a leader in the 2024 Gartner® Magic Quadrant™for Cloud DBMS: https://www.databricks.com/resources/analyst-paper/databricks-named-leader-by-gartner
An open, unified approach to your data, BI and AI workloads: https://www.databricks.com/product/databricks-sql
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

FAQs

What is the Iceberg geo type and why was it created?

The Iceberg geo type is a native geospatial data support feature added to Apache Iceberg to address fragmentation in how spatial data is stored and managed. Previously, the geospatial community used many different file formats, creating silos and inefficiencies. This unified type enables standardized storage and efficient querying of geospatial data.

How does the Iceberg geo type improve geospatial analytics?

When combined with Apache Sedona and Apache Spark, the Iceberg geo type simplifies and accelerates geospatial analytics workflows by providing a standardized data warehouse layer. This eliminates the need for special tools to handle different geospatial formats, making queries more efficient and development faster.

geospatial analytics platform

Apache Iceberg's new Geo type in Apache Iceberg v3 enables native geospatial analytics at scale on Databricks Data and AI platform. Organizations can store and query geographic data efficiently within Delta Lake, supporting use cases from asset tracking to environmental monitoring without separate geospatial databases.

What is Iceberg v3, and what does it add?

Apache Iceberg v3 introduces the Geo type for native geospatial data management, enabling efficient storage and querying of geographic coordinates and shapes directly in Delta Lake tables. This innovation eliminates the need for separate geospatial databases and simplifies data architecture for location-based analytics and AI applications.

What industries benefit most from Iceberg's geospatial capabilities?

Industries using geospatial data—including logistics, urban planning, climate science, and environmental monitoring—benefit from streamlined data management. The unified approach reduces complexity and enables organizations to integrate spatial data with their broader analytics infrastructure.

Full transcript

Let's get started. So, welcome to our presentation today about Iceberg GeoData tab. And uh you guys hear me okay from the back? Awesome. Cool. Uh. I'm a co-founder of Uber house and also the PMC chair of Apache Sedona. Today I'm here together with my friend. Hi. So, yeah, Uh I'm working at Databricks. I'm uh in Spark team, but previously I worked on Iceberg at Apple. Uh Iceberg PMC member. Cool. Cool. Great. Uh I think many of you in this room probably have heard of geospatial data or probably have been using geospatial data, right?
So, geospatial data nowadays is become uh increasingly important, right? Uh from logistics to urban planning to climate, right? You have geospatial data everywhere. However, managing geospatial data at scale presents some very unique challenges, right? So, today we're going to walk you through that. How can we solve that problem? Manage geospatial data at scale using Iceberg, right? And how we started and how the Iceberg GeoData tab works in general.
Cool. Let's move on. Uh some forward-looking statement. Okay. Uh uh agenda today, right? So, how we started? So, why we start to talk about Iceberg GeoData tab in Iceberg? And also then uh going to talk about the journey and the design of this tab. And uh finally, if we still have enough time, I'm going to give a quick demo about how that looks like and how you can use uh uh Iceberg, right? Cool. Let's move on. All right.
So, yes, we all know we all know Iceberg is great, Parquet is great, right? However, so what do we use before that? So, what do we use in the geo community before that? So, there are many existing uh uh geo data file uh formats in the past, right? So, you guys probably know some of them like TX TXT file with WKT WKB. Shapefile, GeoJSON, so on and so forth, right? So, there's one thing I often talk about to our uh to my friends is that so our geospatial folks are really good at reinventing the wheel, right?
So, in the past decade we we we invented a problem like 80 different file formats for every for every single different scenario, right? So, you probably need a really deep you need a really specific tool to actually handle all these formats. So, all these formats create data silos, right? It is really hard for you to actually make sense of geospatial data together with your existing uh data infrastructure. So, actually so realized that problem.
So, a couple of years back so there is this GeoParquet project started. So, GeoParquet is a community effort started in 2022. So, what it what it does is so it defines a cloud-native data format that build on top of Apache Parquet format and it defines how you describe a geospatial data type and how do you how encoding for geometry data type and how do spatial statistics bounding box inside Parquet, right? So, GeoParquet is really successful and there are many projects started to uh adopt uh GeoParquet including GeoPandas, Google BigQuery, Apache Sedona, right?
And also there are many data spatial data vendors start to release geospatial data using GeoParquet format. So, such as Foursquare, such as Open Overture Maps Foundation, right? Great. So, now we have geospatial support in Parquet file, but what about Iceberg? What about all those cool features in Iceberg such as asset transaction, data governance, so on and so forth, right? So, what can we do about that? So, uh actually people have been thinking about that for a very long time.
People really want to support geospatial in Iceberg. So, actually uh go all the way back to 2021. So, actually the first geo issue already created uh on Iceberg GitHub repository. And actually in 2023, so there is a couple research project including GeoLake started to think about how can I add geospatial data type into Iceberg. So, in late 2023, so Ververica, so my company, so we created a fork of Iceberg called Havasu and added geospatial support into Iceberg and we made it to production and opened up to our customers.
And given all the uh activities going on in the community, so actually in around early 2024, the community started to think about so how can we actually move away from all these forks and really put geospatial data type as a native data type into Iceberg and enjoy all the benefits of Iceberg, right? So, in early 2022 2024, so we started conversations and many companies uh were involved in the in the whole process and uh so it's still going to talk about all details here.
It is like year-long discussion. There are many many topics to be covered. And then finally, earlier this year, February 2025, so Iceberg geo data type definition is officially merged into Iceberg V3 spec. You probably have heard of this many many times from, from uh Databricks folks as well, right? So, this going to be going to going to become a big thing in Iceberg V3 spec and they're going to have uh implementation for that as well.
Cool. Now, I'm going to hand it over to my friend. Yeah. Thank you, Jia. Um so, I'll talk about it from the other point of view. So, you know, before I met Jia, like this we were just in the Iceberg community and how we came to want to support geo. Um so, the situation I think what happened was just like the geo's folks and then the big data folks were in very got very specialized, right? Different communities and there was just kind of a lack of knowledge of the other side.
So, that's why it took really long time for things like Iceberg to support Geo. Um I think what happened was um the GeoParquet, you know, he he mentioned GeoParquet was already kind of adopted and later on I know, you know, and worked really hard to get that adopted. Uh but GeoParquet was actually an extension of Parquet. Uh it didn't actually work with the Parquet community that closely. So, still the two communities still don't really know each other that well.
And um it took I think Iceberg not having a extension mechanism to force us to talk to each other because uh otherwise you could have extended Iceberg. Um and uh I think from our side, yeah, we just felt like we didn't know enough to obviously in 2021, 2022 we start seeing all the mails that these guys are sending on Iceberg list, but we just didn't know enough about what they're talking about to really think we can do it justice.
So, you know, guys coming from like uh said, all these community, GeoParquet, Havasu, GeoLake. Uh it's really confusing for us like what these projects are, what they're doing. Uh they're talking about really deep stuff like WKB, WKT encoding, uh like partitioning from like Microsoft, Google, uh Ubers, and like Geo kind of predicates. It's all very hard to understand for Iceberg developer. But I think what really convinced me, I think one day flew down from Seattle and we had a coffee and I asked him like, "Oh, you're really passionate about Geo.
Why do you want to contribute Geo to Iceberg?" And he told me, "Uh our customer don't want to get locked in. They want their data to be kind of open format. So, any engine can support it." And I think, "Well, yeah, that's really like kind of the goal of Iceberg to be a common format for uh all kind of type of data for any engine." So, I think we have more common uh points in common than difference and maybe we should tackle all these technical challenge to make this happen.
So, that's how we started to work together. Um and so, we think over the first principle like, "Okay, forget all this hard stuff like partitioning. What's the minimum minimum minimum we can do to get started?" And we think, "Oh, let's just add a type in Geo." Uh uh Geo type to Iceberg. Um it took a it turns out this project itself took the year, but I think, you know, we learned a lot from this and we can continue after this, but this is the building block, I think, for everything to come.
Um, so first, why is important to define a type in Iceberg, uh, geo type? So, um, obviously, you know, you can store geo data today even without geo type, right? You serialize it into a bytes, a string, struct, whatever. The problem is it's not standard, right? So, only your engine is able to understand that. Another engine doesn't know it's geo data. Um, also, if Iceberg knows it's geo data, it can do optimization. Um, so Iceberg as a data source, one of the main optimization is the filter push down.
The query engine, like Spark or Trino, push down the filter and the data source, like Iceberg, filter out the data that doesn't belong in the query. Um, and so, if we understand the geo, we can actually do filter push down for geo data, otherwise we cannot. We don't know it's a black box to us. Um, so that's why to define a type. If you ever need to define a type in Iceberg, um, there uh, two requirements. One is Iceberg sits on top of data files, right?
Like Parquet, Avro, ORC. So, we need a way to how to encode the geo data into those data files. And the second one is the metadata. So, Iceberg for the filter push down maintains, uh, statistics. In Iceberg, we call them metrics, column metrics, uh, for all the columns. And for geo, we need to define what does it mean, uh, metric? Like, what does lower bound and upper bound mean for geo? So, these are the two kind of fundamental question we try to answer for, um, Iceberg geo.
Uh, for the first one, what is uh, how to encode the geo data, we need to see, you know, what kind of what is geo data. Um, unfortunately, for us, like this is already kind of a very understood problem, right? A lot of databases have supported geo for a long time. Um, so this is they have a body in the geo special domain called OGC. It's a standards body. Uh, it stands for Open Geospatial Consortium. And they have already defined like a class hierarchy of geo data.
So, all this is geo data. Um, if you ever use, uh, even if you don't know much about geo, it's not very hard to pick up. If you ever use a map in your life, uh, you will understand this quickly. Like for example, at Google Maps, you know, if you click on the Google Map, you get a point, which is a set of coordinates, XYZ potentially, if it's three-dimensional. Uh set of points make a line or an edge. A set of line or edge make a polygon, and then you can have collections of any of these things.
These are all geodata that you should be able to store in the Iceberg geotype. For the encoding, uh we're also very fortunate that OGC body also specified how to encode all these classes. And they have uh they really like the acronym, so they make one called WKB. Sounds really fancy, but actually it just stands for well-known binary. I think it's well-known in the geo domain. Um so, the OG uh it's pretty much a very simple kind of uh encoding specification where it's just a set of bits that represent a geodata.
Uh the first bit is byte order, then you have some bits representing what class in the geo hierarchy, and then a class-specific encoding. So, I think we talked a little bit, but we think why reinvent the wheel? And be an open format, you want more compatibility, so we just use what most of the ecosystem use and decide to use WKB to encode the geodata into the data file. So, that's pretty easy so far. Um a little bit harder is the metrics.
As I mentioned, Iceberg needs to keep statistics about the geodatas. And the one of the most important ones is the upper and lower bounds. This is what it used to prune out the files that don't match the query, right? Um so, we have to think, what does this mean for geo? Uh fortunately, geo also has this concept. It's called the bounding box. So, if you think about it, like you have a lot of geodata, and in the geo world, to make a bound, you draw a box that kind of covers all the geodata that you want to bound, like all the geodata in your file, your row group, whatever.
Uh like on the right side, I have like a illustration of bounding box for all 50 uh US states, right? Um and the property of a box, or even a cube, this works for 3D as well, is it can be defined by two points. There's like a lower point, which is Xmin, Ymin, uh is Zmin for 3D and then uh max point X max, Y max, Z max. And so both of the points are themselves geo data, which is great because in Iceberg as of now the bounds actually needs to be the instance of that column.
So this works perfectly so far for Iceberg. Um there was only a little bit of uh discussion in Iceberg community about how to encode the bounds. Initially we were thinking, "Oh, why why reinvent the wheel? We do use the WKB again." But I think Ryan Blue uh he made a nice observation that in Iceberg the metadata size is very very important because that's on the critical path of reading and writing the Iceberg table. So he said, "Well we always know it's a point, so we can make some optimization.
We don't need the first bit. We we can always hard code the bit order. We can always you know assume it's a point, so we can just get an even more compact representation of the bound by just concatenating the coordinate values of the point. So we have a nice compact bound representation." Cool. So that's um you know the data file metadata file. So sounds like we're done, right? You would think so. But actually we're only getting start It turns out this is only the first step.
We figured out later on that if we don't have the type in parquet, all this falls apart and I'll have to explain why. So parquet is another, you know, specification and Iceberg is built on top of parquet. And the same problem happens if you don't have the geo type in parquet. Like Iceberg can write the geo data as WKB binary to parquet, but another person reading that parquet file won't know what that is. It's not standard.
So that's why you need a geo type in parquet. What's worse is you know parquet also need to do filter push down. So there's you know filter pruning at the Iceberg level to prune out files, but what if you have a very big file? You want the file format like parquet to prune out row groups as well. So parquet need to understand the geo type to do pruning at the lower level. And actually one interesting thing is the implementation of Iceberg writers.
Iceberg writers actually end up writing the whole parquet file and then just taking the parquet stats, copying to the iceberg metadata. Uh obviously this is much simpler than re-reading the parquet file after writing, right? So then uh we run into a chicken egg problem. If you don't have the stats in the parquet, actually we will not even ever have stats in iceberg. So we have to actually go to parquet community to uh add the geo type there.
Uh first we were like trying to see how to do that because it's a different community, but we're very fortunate uh when we make the iceberg proposal, it's open, you know, on the dev list. And there's one uh parquet PMC member called Gang Wu who was very nice and uh was really active and said, "Oh, the he'd see what we need in uh iceberg." And he said, "I will drive the effort in the parquet side." So we work really closely.
So me, Jan, Gang like just to make sure the specs are defined the same way in parquet and in iceberg for encoding the geo data and also the bounds for the geo data. So uh we're happy to see um this kind of like cooperation among the community. And what was even more cool was like, he was like the founder of the geo parquet, which was the extension. He was also on board, too, and agreed to kind of make the new uh put the geo parquet stuff into native parquet.
So everything now is like all in in sync. So this is actually quite huge if you know the history of the division of the communities. Um so are we done yet? No, so actually again we just we just got started. And um and to our kind of like surprise it took many more months of discussion. And this is uh where we kind of have to get into the weeds. I didn't initially want to get into the weeds of the geo domain. It's quite complex.
But it looks like you cannot escape that. Um so the problem with of geo is like it's not like another type in iceberg like integer type. No no one ever debates like the two engines reading the same uh integer value. There's no debate what is zero, what is one. But in geo world it's not the same. The same geo uh data can be interpreted to mean different things on the earth. Um so you what you have to do is actually iceberg as a spec we have to be very clear when the writer writes to your data he has to annotate it with enough information so the reader knows exactly what that the writer meant otherwise you have very subtle errors or even disastrous errors right when you interpret this data.
And there's two main class of problems here one is remember the OGC class hierarchy the point. What does a point mean on the earth like how do you map a point bytes into somewhere on earth? And also edge edge is two points how what does the how do you interpolate the edge between the two points? Um So this is a like a tough questions. Fortunately again the geo domain they have some advancements here so they they you will probably have seen this in school like coordinate reference system this is the answer to the first question how do you map a point to somewhere on earth?
And the coordinate reference system is a system where you define it's pretty complex but I think you you define a coordinate plane in a 2D you estimate the shape of the earth is sphere or ellipse in 3D and then you project that 3D onto the 2D. So it's a whole but it's a well studied domain and the OGC fortunately the standards body also have a standard for that. They actually made a database of all known coordinate reference systems out there and they give you an SRID which is a like a primary key into one of those coordinate reference systems.
So for most databases you provide an SRID when you write the geo data to tell it like what I used to generate the geo data. So we think it's okay but the problem is like the OGC I think it didn't catch up with the modern times cuz modern times you actually have CRS that are beyond what the OGC know about right you want a custom CRS and I think there were no good standards for that. Um There is a lot of kind of almost religious debate there like between pro JSON and some other ones.
And yeah we were like kind of getting a little we didn't really know to do because people kept debating on on this one. Fortunately we take a step back we think iceberg, you know, we're just like a format. So, we don't want to dictate to people what they should and should not use. We just want to support what most people are using. So, we allow like uh SRID and Proj JSON, even if it's, you know, like maybe not uh like formally stamped, you know, by the OGC.
And we can uh support other ones as well. So, we now add a CRS as a type parameter for GeoType. Um, and you can specify the default or an SRID value or a Proj JSON value. And iceberg implementation um if we we if it's a CRS that we know, then only then we will do the filter. Otherwise, we will say even we don't understand the data and we will not do the filter push down um optimizations. But, that's okay, because these are optional things, right?
Um, different implementation can choose to do filter push down for CRS that they know, right? So, that's the first part. The second hard question to answer was edge interpolation. Um, so edge interpolation is again um edge is modeled in uh the GeoType by two points. And there's actually I don't know if you can see um this is like Demetrio from Snowflake. He provided this nice diagram to illustrate um like a edge uh Cartesian edge, the normal edge between San Francisco and Berlin is a straight line.
But, if you ever take a flight to Europe, you know, like you don't go in a straight line. You kind of like the because the world is sphere, you kind of go across Arctic Circle and go back down. So, actually um you know, people we want to model this realistic kind of like edge. So, uh in the database world, you know, PostGIS I think was the first to do this one or I don't know the first, but the early runner. Um, they make two types, geometry type and geography type.
Geometry is for the straight line edges, and then geography is the edge where it consider the curve of the earth. Um, so we think, okay, maybe that solves the issue. But, again, we got bogged down in a lot of really mathematical debate there. A lot of people coming in and even criticizing the geography type of PostGIS not being specific enough. They say it's not geodesics and like debate the name for a long time. Uh it got really kind of ugly.
And then I think later on um again, like we take step back. Like Iceberg, we don't want to get into these debates. We we want um people to just be able to use us and be able to store what they uh most users want. And so we say, "Okay, we can make the edge interpolation algorithm parameterizable, and we support these kind of values. These are the most common values." I think uh Menelaos from Databricks uh provided us.
Um and so, you know, and uh same thing, Iceberg implementation, we can store these value, but then like only if we understand uh like if it's the default, for example, then we can do the filter pushdown. Um so maybe you that part I can double click into. So um the bounding box, right? Like the bounding box for the edge between San Francisco and Berlin, um if it's a Cartesian bounding box, a Cartesian edge is easy, right?
You just take the latitude of Berlin, latitude of San Francisco, and the longitude as well, and that's the box. But if it's a spherical edge, the box has to go way up. It has to, you know, because the edge goes way way to the north. So you have the edge the bounding box needs to actually take that into account. So you don't want to do the filter pushdown for uh edge interpolation algorithm that you don't understand. So, um all this is to say like, "Okay, there's a lot of technical discussion there."
But after 1 year of discussion, uh we got the two types into Iceberg, the geometry and geography type. Uh geometry type is for most use cases, I think it's fine because um it's faster. The linear edge interpolation is always faster for a computer to calculate than like a spherical one. Um and it's usually fine if you're doing like local kind of like data in a city or a county because you don't have these edges that you need to really model uh based on the sphere of the earth.
But on the other hand, if you do, then you can use the geography type. There you can use any kind of like uh uh the supported edge interpolation algorithms, uh you're limited though, the CRS has to be a world-spanning CRS. And there's also one thing I don't think we have time to cover is like the bounding box has to be wrap around. Like there's some case where if the at the 180 longitude, it kind of like goes back to -180 and there's some objects in that line.
So geography has to handle that case, but that's another thing. And then yeah, so you you would use this if you have you're trying to map things like you know, shipping lanes or airplane routes or something that kind of goes very far. So pretty much that's the summary. V3 we're really happy like me and like to get this through. The parquet committee and iceberg committee work hand in hand and then like in February 2025, like a few months back, we passed a vote to adopt this formally in both the iceberg and parquet specs.
And iceberg V3, which is like this one and many other types, all was formally adopted only a few weeks back in May. And so this is now officially part of iceberg V3. It was really cool. I think this is the biggest project we've done in iceberg or I done. And just like people from so many communities, GeoLake, Havasu, GeoParquet, Iceberg, Parquet. And then so many companies like Worx Bot, Snowflake, Databricks, Apple, ESRI, Carto.
Like company like who engineers who usually compete with each other like in the same room trying to see how to advance the whole ecosystem. I think it was really really cool. And yeah, I think it like iceberg as a community really has this principle of doing the hard thing. Like it we take the time like beyond I think what one company can invest in, but just like keep going until we support the majority of use case cuz I think iceberg what's the point of this open table spec if the people don't want to use the spec, right?
So even though a lot of time we want to like we think we cannot progress like eventually we get to a consensus. And I think the patience has paid off. Like we have some ripple effects after iceberg kind of adopt this spec because now it's kind of the first door I think into the wider big data ecosystem. Like Delta Lake has a RFC like a few days back about adding geo type to that and then Spark had the S PIP to add a geo type to that working with the Sedona community.
And so I think a lot more open source projects will start to adopt it after Iceberg has adopted this one. So I'm super excited for that. And uh yeah, now Jiao will talk about like what you can do with this stuff. Sure. Great. Uh Great. So much meat, right? Now let's talk about some cool demos, right? So the demo I'm I'm going to show you is actually a combination of uh this ongoing PRs. So it is using the Iceberg and Apache Sedona and Apache Spark all together.
So after all these PRs are merged, so you can actually actually use them in production. So now let's see some example here. Cool. So this is a a a table. Let's create an Iceberg table here, right? So this table has two columns. So ID column and a geometry column. So the geometry column is uh is having the geometry data type. And uh in the when you create this table, so in the table properties you're going to specify the format version to be uh a V3 because this is a V3 uh feature.
Now let's put some data there. So the first data we're going to put there is uh two line string objects. So line string A and B. So we're going to append those two records into the Iceberg table. And now let's put some more data there. So line strings C and D. So then we append those to the table. And then another append operation. So line string uh uh E and F. So another operation. So now if you look at this table overall, so you have six records.
So A B C D E F and six line strings. So if you put those on a map, so you'll see like all those line strings. Now let's do some deletion operation. So I want to remove the line strings that intersect with this orange polygon. So how do we do that? So So the way we do that is we introduce a special predicate called ST_Crosses. So, this is a special predicate coming from a Parquet owner, but you also have the same predicate across the board like in PostGIS as well.
So, what ST_Crosses does is it it takes two input. So, the first input is the geometry column, the one that we already have in the iceberg table. The second one is actually the polygon, the orange polygon shape. It is encoded in this WKT text string. So, you put that into the as a second input and then we actually run the delete from statement. So, eventually what you have is the following, right? You have a table that has actually the two lines string B and C got removed from the table.
Now, if you look back, we have done actually four operations, four transactions on the table and the iceberg table now has four versions, right? So, actually if you look at look back through the iceberg snapshots, you'll be able to see all the four snapshots and also the you know the corresponding operations associated with each of the snapshot. So, that's the you know the power of iceberg. Now, you can also do time travel across different versions, right?
So, you can actually jump right back to the to the snapshot that is right before the deletion. So, that has actually six records. You can also jump to the to the snapshot that is after the initial append. So, that has only two records, right? So, which is great. Now, we have a little bit more advanced example here. So, we have two tables here. So, there is a target table. So, it has a few records, right? So, we have another source table.
So, this source table has three records. However, here's a catch, right? So, if you look at the three new records here, so we want to put the three new records into the target table. We want to store like more of them together. However, if you look at the line string D, so it is accurate duplicate of the same record in target in the target table. And the second line string E, it is actually a same record. It is the it there is a record E in the target table, but the geometry shape is different.
The third record line string Z, so it is a new record, right? Now, we want to merge these two table together and of course update the necessary records. So, that's that's how we do it. So, you can use this merge into statement in Iceberg. And so, it is probably a little bit it'll be hard to see the details here, but essentially you have this this It's even harder for me to see. Oh, nice. Cool. Yeah. So, you can see this is a a a condition here, right?
So, first you when the when the ID matched, so you're going to do do a check. So, if the two geometries same, if they are the same, you don't actually merge the new geometry into the old table, but but if they don't match, you can insert that record into that table. Now, after this query, that's what you'll get. So, you get a six you get actually a table that has one two three four five records, right? But pay attention to the record D here.
So, record D that was duplicate, so we don't actually insert a new duplicate, right? And the record line string E, so its shape got updated. And line string Z actually got inserted into this this new table. So, basically this is a upsert base merge. Cool. Another cool thing is the special schema enforcement, right? There's this native geo data type. So, suppose you already have a table like what we already showed you before.
Now, I have a new table. I want to append this new table into the existing Iceberg geo table. And this table uh in the schema, it has one more column. So, this one more column is the number column. So, now if you want to merge append this table to the other table to the existing table, it is going to error out. Tells you that the schema does not match. And uh you can actually also do uh uh schema evolution on the uh on the Iceberg geo data table geo geo data table.
And you can actually alter the uh the schema to make it uh have this new column uh number column. And then you can actually append this new table to the uh existing Iceberg table. And then it will work. You can see that we have a new table that has both new table and uh the old table together. And then in the old table, because we initially did not uh did not have the uh number column. So, the number column has the value uh null.
But basically, they work together. And uh pretty nice. Cool. Another cool thing about the Iceberg geo data tab I so also uh Zuhair mentioned is uh you can actually leverage the bounding box to perform really nice filter pushdown. So, this is a really simple example here. So, suppose we have Iceberg table with multiple points. And uh so, you see there are multiple points here. And uh the color of each point actually like points that have same color, meaning that they're in the same Parquet file.
They all coming from the same Iceberg table, but points that have same color, they're in the same Parquet file Parquet data file. Now, suppose we have a a special special query or special filter. So, that is this uh polygon shape in there. So, it is the filter area, right? Now, I want to find all the points that are falling inside this uh polygon. So, what do we do? So, in Iceberg and as we mentioned, because Parquet and Iceberg both have the native geo data type, you you can actually have bounding box bounding box statistics on top of each Iceberg file.
So, basically, you can have a bounding box inside each of parquet file that actually help you to perform data data pruning. Now, if you run this query, right? So, you can actually use the spatial predicate to perform this. So, this spatial predicate here is called ST_Intersects. So, this still this is a spatial predicate coming from Apache Sedona, but you can also find the same same thing in PostGIS and somewhere else.
And then you can run that put that in the where clause and run that directly on top of the Iceberg table, right? So, it will find the uh uh points that intersect or fall inside this this polygon, this filter area, right? So, the output is uh three records, right? So, only three points that are falling inside this uh uh uh filter area. So, you can actually verify that if the uh filter pushdown actually uh kicks in or not.
So, if you actually use Sedona, you can actually print out the query plan. So, if you look at the query plan here, right? You can see that the filter uh the filter ST_Intersects got pushed down to the bottom to the very to the bottom of the query plan, meaning that it got executed really fast at the beginning. So, it it it did perform the uh the pruning. Cool. So, another cool thing you can do with Sedona on top of Iceberg is the geospatial clustering or indexing, right?
So, as you probably already know that, so because you already have this kind of bounding box statistics in parquet and Iceberg, right? So, uh this actually have huge impact on the query performance and filter pushdown. So, if you want to have really good performance for uh data really good data pruning performance for Iceberg tables for spatial data, so you actually need to order data based on their special proximity.
So there are so many way to order them, but essentially the idea here is you sort of want to make sure that you preserve the special proximity within each parquet parquet file and expert table. And such that the bounding box is really really tight, right? You can when you have a special filter, you can prune as many bound bounding boxes as possible. So inside only we have this function called curve, right? You can actually order the special records you have in expert table by the curve ID or the geometry.
So you have say you have a polygon, you have a line string, you have a point, you can have actually the you can take the centroid of your geometry and get a curve ID of that geometry shape and then you can order the entire table based on the curve ID. So this doesn't have to be like because this you you see this is taking the centroid of the geometry, right? Because this doesn't have to be 100% accurate. As long as you can preserve special proximity, it is good enough.
It's going to have really good really tight bounding box already. Cool. I think Yeah, that's all for our presentation. And do we do you want to talk about the survey right now? Yeah, I'm going to just quickly there's a computer survey for us. Thank you. Yeah, so there is a I think on your on your app, so there is a survey for each session. So if you like our talk, you can give fill in the survey. Yeah, okay. Thank you.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.