Hi Snowflake folks, Please let me know if you have other questions regarding the proposal. If any, Szehon and I can set up a zoom call with you guys to clarify some details. We are in the Pacific time zone. If you are in Europe, maybe early morning Pacific Time works best for you?
Thanks, Jia On Wed, Jun 5, 2024 at 6:28 PM Gang Wu <ust...@gmail.com> wrote: > > The min/max stats are discussed in the doc (Phase 2), depending on the > non-trivial encoding. > > Just want to add that min/max stats filtering could be supported by file > format natively. Adding geometry type to parquet spec > is under discussion: https://github.com/apache/parquet-format/pull/240 > > Best, > Gang > > On Thu, Jun 6, 2024 at 5:53 AM Szehon Ho <szehon.apa...@gmail.com> wrote: > >> Hi Peter >> >> Yes the document only concerns the predicate pushdown of geometric >> column. Predicate pushdown takes two forms, 1) partition filter and 2) >> min/max stats. The min/max stats are discussed in the doc (Phase 2), >> depending on the non-trivial encoding. >> >> The evaluators are always AND'ed together, so I dont see any issue of >> partitioning with another key not working on a table with a geo column. >> >> On another note, Jia and I thought that we may have a discussion about >> Snowflake geo types in a call to drill down on some details? What time >> zone are you folks in/ what time works better ? I think Jia and I are both >> in Pacific time zone. >> >> Thanks >> Szehon >> >> On Wed, Jun 5, 2024 at 1:02 AM Peter Popov <peter.po...@snowflake.com> >> wrote: >> >>> Hi Szehon, hi Jia, >>> >>> Thank you for your replies. We now better understand the connection >>> between the metadata and partitioning in this proposal. Supporting the >>> Mapping 1 is a great starting point, and we would like to work closer with >>> you on bringing the support for spherical edges and other coordinate >>> systems into Iceberg geometry. >>> >>> We have some follow-up questions regarding the partitioning (let us know >>> if it’s better to comment directly in the document): Does this proposal >>> imply that XZ2 partitioning is always required? In the current >>> proposal, do you see a possibility of predicate pushdown to rely on x/y >>> min/max column metadata instead of a partition key? We see use-cases where >>> a table with a geo column can be partitioned by a different key(e.g. date) >>> or combination of keys. It would be great to support such use cases from >>> the very beginning. >>> >>> Thanks, >>> >>> Peter >>> >>> On Thu, May 30, 2024 at 8:07 AM Jia Yu <ji...@apache.org> wrote: >>> >>>> Hi Dmtro, >>>> >>>> Thanks for your email. To add to Szehon's answer, >>>> >>>> 1. How to represent Snowflake Geometry and Geography type in Iceberg, >>>> given the Geo Iceberg Phase 1 design: >>>> >>>> Answer: >>>> Mapping 1 (possible): Snowflake Geometry + SRID: 4326 -> Iceberg >>>> Geometry + CRS84 + edges: Planar >>>> Mapping 2 (impossible): Snowflake Geography -> Iceberg Geometry + >>>> CRS84 + edges: Spherical >>>> Mapping 3 (impossible): Snowflake Geometry + SRID:ABCDE-> Iceberg >>>> Geometry + SRID:ABCDE + edges: Planar >>>> >>>> As Szehon mentioned, only Mapping 1 is possible because we need to >>>> support spatial query push down in Iceberg. This function relies on the >>>> Iceberg partition transform, which requires a 1:1 mapping between a value >>>> (point/polygon/linestring) and a partition key. That is: given any >>>> precision level, a polygon must produce a single ID; and the covering >>>> indicated by this single ID must fully cover the extent of the polygon. >>>> Currently, only xz2 can satisfy this requirement. If the theory from >>>> Michael Entin can be proven to be correct, then we can support Mapping 2 in >>>> Phase 2 of Geo Iceberg. >>>> >>>> Regarding Mapping 3, this requires Iceberg to be able to understand >>>> SRID / PROJJSON such that we will know min max X Y of the CRS (@Szehon, >>>> maybe Iceberg can ask the engine to provide this information?). See my >>>> answer 2. >>>> >>>> 2. Why choose projjson instead of SRID? >>>> >>>> The projjson idea was borrowed from GeoParquet because we'd like to >>>> enable possible conversion between Geo Iceberg and GeoParquet. However, I >>>> do understand that this is not a good idea for Iceberg since not many libs >>>> can parse projjson. >>>> >>>> @Szehon Is there a way that we can support both SRID and PROJJSON in >>>> Geo Iceberg? >>>> >>>> It is also worth noting that, although there are many libs that can >>>> parse SRID and perform look-up in the EPSG database, the license of the >>>> EPSG database is NOT compatible with the Apache Software Foundation. That >>>> means: Iceberg still cannot parse / understand SRID. >>>> >>>> Thanks, >>>> Jia >>>> >>>> On Wed, May 29, 2024 at 11:08 AM Szehon Ho <szehon.apa...@gmail.com> >>>> wrote: >>>> >>>>> Hi Dmytro >>>>> >>>>> Thank you for looking through the proposal and excited to hear >>>>> from you guys! I am not a 'geo expert' and I will definitely need to pull >>>>> in Jia Yu for some of these points. >>>>> >>>>> Although most calculations are done on the query engine, Iceberg >>>>> reference implementations (ie, Java, Python) does have to support a few >>>>> calculations to handle filter push down: >>>>> >>>>> 1. push down of the proposed Geospatial transforms ST_COVERS, >>>>> ST_COVERED_BY, and ST_INTERSECTS >>>>> 2. evaluation of proposed Geospatial partition transform XZ2. As >>>>> you may have seen, this was chosen as its the only standard one today >>>>> that >>>>> solves the 'boundary object' problem, still preserving 1-to-1 mapping >>>>> of >>>>> row => partition value. >>>>> >>>>> This is the primary rationale for choosing the values, as these were >>>>> implemented in the GeoLake and Havasu projects (Iceberg forks that sparked >>>>> the proposal) based on Geometry type (edge=planar, crs=OGC:CRS84/ >>>>> SRID=4326). >>>>> >>>>> 2. As you mentioned [2] in the proposal there are difficulties with >>>>>> supporting the full PROJSSON specification of the SRS. From our >>>>>> experience >>>>>> most of the use-cases do not require the full definition of the SRS, in >>>>>> fact that definition is only needed when converting between coordinate >>>>>> systems. On the other hand, it’s often needed to check whether two >>>>>> geometry >>>>>> columns have the same coordinate system, for example when joining two >>>>>> columns from different data providers. >>>>>> >>>>>> To address this we would like to propose including the option to >>>>>> specify the SRS with only a SRID in phase 1. The query engine may choose >>>>>> to >>>>>> treat it as opaque identified or make a look-up in the EPSG database of >>>>>> supported. >>>>>> >>>>> >>>>> The way to specify CRS definition is actually taken from GeoParquet >>>>> [1], I think we are not bound to follow it if there are better options. I >>>>> feel we might need to at least list out supported configurations in the >>>>> spec, though. There is some conversation on the doc here about this [2]. >>>>> Basically: >>>>> >>>>> 1. XZ2 assumes planar edges. This is a feature of the algorithm, >>>>> based on the original paper. A possible solution to spherical edge is >>>>> proposed by Michael Entin here: [3], please feel free to evaluate. >>>>> 2. XZ2 needs to know the coordinate range. According to Jia's >>>>> comments, this needs parsing of the CRS. Can it be done with SRID >>>>> alone? >>>>> >>>>> >>>>>> 1. In the first version of the specification Phase1 it is mentioned >>>>>> as the version focused on the planar geometry model with a CRS system >>>>>> fixed >>>>>> on 4326. In this model, Snowflake would not be able to map our Geography >>>>>> type since it is based on the spherical Geography model. Given that >>>>>> Snowflake supports both edge types, we would like to better understand >>>>>> how >>>>>> to map them to the proposed Geometry type and its metadata. >>>>>> >>>>>> - >>>>>> >>>>>> How is the edge type supposed to be interpreted by the query >>>>>> engine? Is it necessary for the system to adhere to the edge model for >>>>>> geospatial functions, or can it use the model that it supports or let >>>>>> the >>>>>> customer choose it? Will it affect the bounding box or other row group >>>>>> metadata >>>>>> - >>>>>> >>>>>> Is there any reason why the flexible model has to be postponed to >>>>>> further iterations? Would it be more extensible to support mutable >>>>>> edge >>>>>> type from the Phase 1, but allow systems to ignore it if they do not >>>>>> support the spherical computation model >>>>>> >>>>>> >>>>> It may be answered by the previous paragraph in regards to XZ2. >>>>> >>>>> 1. If we get XZ2 to work with a more variable CRS without >>>>> requiring full PROJJSON specification, it seems it is a path to support >>>>> Snowflake Geometry type? >>>>> 2. If we get another one-to-one partition function on spherical >>>>> edges, like the one proposed by Michael, it seems a path to support >>>>> Snowflake Geography type? >>>>> >>>>> Does that sound correct? As for why certain things are marked as >>>>> Phase 1, they are just chosen so we can all agree on an initial design and >>>>> iterate faster and not set in stone, maybe the path 1 is possible to do >>>>> quickly, for example. >>>>> >>>>> Also , I am not sure about handling evaluation of ST_COVERS, >>>>> ST_COVERED_BY, and ST_INTERSECTS (how easy to handle different CRS + >>>>> spherical edges). I will leave it to Jia. >>>>> >>>>> Thanks! >>>>> Szehon >>>>> >>>>> [1]: >>>>> https://github.com/opengeospatial/geoparquet/blob/main/format-specs/geoparquet.md#column-metadata >>>>> [2]: >>>>> https://docs.google.com/document/d/1iVFbrRNEzZl8tDcZC81GFt01QJkLJsI9E2NBOt21IRI/edit?disco=AAABL-z6xXk >>>>> <https://docs.google.com/document/d/1iVFbrRNEzZl8tDcZC81GFt01QJkLJsI9E2NBOt21IRI/edit?disco=AAABL-z6xXk> >>>>> [3]: >>>>> https://docs.google.com/document/d/1tG13UpdNH3i0bVkjFLsE2kXEXCuw1XRpAC2L2qCUox0/edit >>>>> <https://docs.google.com/document/d/1tG13UpdNH3i0bVkjFLsE2kXEXCuw1XRpAC2L2qCUox0/edit> >>>>> >>>>> >>>>> On Wed, May 29, 2024 at 8:30 AM Dmytro Koval >>>>> <dmytro.ko...@snowflake.com.invalid> wrote: >>>>> >>>>>> Dear Szehon and Iceberg Community, >>>>>> >>>>>> >>>>>> This is Dmytro, Peter, Aihua, and Tyler from Snowflake. As part of >>>>>> our desire to be more active in the Iceberg community, we’ve been looking >>>>>> over this geospatial proposal. We’re excited geospatial is getting >>>>>> traction, as we see a lot of geo usage within Snowflake, and expect that >>>>>> usage to carry over to our Iceberg offerings soon. After reviewing the >>>>>> proposal, we have some questions we’d like to pose given our experience >>>>>> with geospatial support in Snowflake. >>>>>> >>>>>> We would like to clarify two aspects of the proposal: handling of the >>>>>> spherical model and definition of the spatial reference system. Both of >>>>>> which have a big impact on the interoperability with Snowflake and other >>>>>> query engines and Geo processing systems. >>>>>> >>>>>> >>>>>> Let us first share some context about geospatial types at Snowflake; >>>>>> geo experts will certainly be familiar with this context already, but for >>>>>> the sake of others we want to err on the side of being explicit and >>>>>> clear. >>>>>> Snowflake supports two Geospatial types [1]: >>>>>> - Geography – uses a spherical approximation of the earth for all >>>>>> the computations. It does not perfectly represent the earth, but allows >>>>>> getting accurate results on WGS84 coordinates, used by GPS without any >>>>>> need >>>>>> to perform coordinate system reprojections. It is also quite fast for >>>>>> end-to-end computations. In general, it has less distortions compared to >>>>>> the 2d planar model . >>>>>> - Geometry – uses planar Euclidean geometry model. Geometric >>>>>> computations are simpler, but require transforming the data between >>>>>> coordinate systems to minimize the distortion. The Geometry data type >>>>>> allows setting a spatial reference system for each row using the SRID. >>>>>> The >>>>>> binary geospatial functions are only allowed on the geometries with the >>>>>> same SRID. The only function that interprets SRID is ST_TRANFORM that >>>>>> allows conversion between different SRSs. >>>>>> >>>>>> Geography >>>>>> >>>>>> Geometry >>>>>> >>>>>> >>>>>> >>>>>> Given the choice of two types and a set of operations on top of them, >>>>>> the majority of Snowflake users select the Geography type to represent >>>>>> their geospatial data. >>>>>> >>>>>> From our perspective, Iceberg users would benefit most from being >>>>>> given the flexibility to store and process data using the model that >>>>>> better >>>>>> fits their needs and specific use cases. >>>>>> >>>>>> Therefore, we would like to ask some design clarifying questions, >>>>>> important for interoperability: >>>>>> >>>>>> >>>>>> 1. In the first version of the specification Phase1 it is mentioned >>>>>> as the version focused on the planar geometry model with a CRS system >>>>>> fixed >>>>>> on 4326. In this model, Snowflake would not be able to map our Geography >>>>>> type since it is based on the spherical Geography model. Given that >>>>>> Snowflake supports both edge types, we would like to better understand >>>>>> how >>>>>> to map them to the proposed Geometry type and its metadata. >>>>>> >>>>>> - >>>>>> >>>>>> How is the edge type supposed to be interpreted by the query >>>>>> engine? Is it necessary for the system to adhere to the edge model for >>>>>> geospatial functions, or can it use the model that it supports or let >>>>>> the >>>>>> customer choose it? Will it affect the bounding box or other row group >>>>>> metadata >>>>>> - >>>>>> >>>>>> Is there any reason why the flexible model has to be postponed to >>>>>> further iterations? Would it be more extensible to support mutable >>>>>> edge >>>>>> type from the Phase 1, but allow systems to ignore it if they do not >>>>>> support the spherical computation model >>>>>> >>>>>> >>>>>> >>>>>> 2. As you mentioned [2] in the proposal there are difficulties with >>>>>> supporting the full PROJSSON specification of the SRS. From our >>>>>> experience >>>>>> most of the use-cases do not require the full definition of the SRS, in >>>>>> fact that definition is only needed when converting between coordinate >>>>>> systems. On the other hand, it’s often needed to check whether two >>>>>> geometry >>>>>> columns have the same coordinate system, for example when joining two >>>>>> columns from different data providers. >>>>>> >>>>>> To address this we would like to propose including the option to >>>>>> specify the SRS with only a SRID in phase 1. The query engine may choose >>>>>> to >>>>>> treat it as opaque identified or make a look-up in the EPSG database of >>>>>> supported. >>>>>> >>>>>> Thank you again for driving this effort forward. We look forward to >>>>>> hearing your thoughts. >>>>>> >>>>>> [1] >>>>>> https://docs.snowflake.com/en/sql-reference/data-types-geospatial#understanding-the-differences-between-geography-and-geometry >>>>>> >>>>>> [2] >>>>>> https://docs.google.com/document/d/1iVFbrRNEzZl8tDcZC81GFt01QJkLJsI9E2NBOt21IRI/edit#heading=h.oruaqt3nxcaf >>>>>> >>>>>> >>>>>> On 2024/05/02 00:41:52 Szehon Ho wrote: >>>>>> > Hi everyone, >>>>>> > >>>>>> > We have created a formal proposal for adding Geospatial support to >>>>>> Iceberg. >>>>>> > >>>>>> > Please read the following for details. >>>>>> > >>>>>> > - Github Proposal : >>>>>> https://github.com/apache/iceberg/issues/10260 >>>>>> > - Proposal Doc: >>>>>> > >>>>>> https://docs.google.com/document/d/1iVFbrRNEzZl8tDcZC81GFt01QJkLJsI9E2NBOt21IRI >>>>>> > >>>>>> > >>>>>> > Note that this proposal is built on existing extensive research and >>>>>> POC >>>>>> > implementations (Geolake, Havasu). Special thanks to Jia Yu and >>>>>> Kristin >>>>>> > Cowalcijk from Wherobots/Geolake for extensive consultation and >>>>>> help in >>>>>> > writing this proposal, as well as support from Yuanyuan Zhang from >>>>>> Geolake. >>>>>> > >>>>>> > We would love to get more feedback for this proposal from the wider >>>>>> > community and eventually discuss this in a community sync. >>>>>> > >>>>>> > Thanks >>>>>> > Szehon >>>>>> > >>>>>> >>>>>
