Invalid Geometries in Overture Division Areas

[KevinThoms]

Hey Overture team!

We have a user accessing your Overture Maps Division Areas data from Snowflake Marketplace and they're running into issues due to a few invalid geometries.

We found running: SELECT * from OVERTURE_DATA_MARKETPLACE.CARTO.DIVISION_AREA WHERE ST_ISVALID(geometry) = false returned 7 invalid geometries with the id's below:

    ('0ca3bee0-1166-43e1-a82d-f49e41127170'),
    ('604e4d47-073c-46f8-86a5-2a8838509986'),
    ('7a7b4236-aa79-43a2-846d-0b8f1f4f6c28'),
    ('d8b0d60b-8f3d-4ee4-a8b2-b58f5358d3cd'),
    ('3368ac8b-45da-47da-ac8c-2cf11fd4cdca'),
    ('ffeb7ee4-0d64-4014-9338-0071aa5d9434'),
    ('b38c964e-69b8-42b3-aa56-384a09995720')

They were running into the error Error: Geography validation failed: Loop 0: Edge (138.691677,53.904861) -- (138.700644,53.832590) crosses edge (138.706912,53.845652) -- (138.676595,53.782420) with the query below:

with

division as (select * from OVERTURE_MAPS__DIVISIONS.CARTO.division),
division_area as (select * from OVERTURE_MAPS__DIVISIONS.CARTO.division_area),

country_names_english as (
    select
        division.id,
        flatten.value:value::string   as country_name_english,
    from division
    inner join lateral flatten(input => division.names:common.key_value) flatten
    where flatten.value:key::string = 'en'
),

final as (
    select
        division.country as country_code,
        country_names_english.country_name_english,
        division_area.geometry,
        ST_GEOGFROMTEXT(ST_ASTEXT(division_area.geometry)) as geography_geometry,
    from division
    left join country_names_english on division.id = country_names_english.id
    left join division_area on division.id = division_area.division_id
    where division.parent_division_id is null
    and division.country is not null
    and division.subtype = 'country'
)

select *
from final

The query runs fine when excluding the invalid geometries.

Let us know if you have any insight into this issues. Thanks!

[jenningsanderson]

Thanks for raising this issue, @KevinThoms

These geometries are showing as valid in Amazon Athena (Trino) in the latest Overture release:

SELECT id,
	ST_ISVALID(st_geomfrombinary(geometry))
FROM overture.release.v2025_09_24_0
WHERE id IN (
		'0ca3bee0-1166-43e1-a82d-f49e41127170',
		'604e4d47-073c-46f8-86a5-2a8838509986',
		'7a7b4236-aa79-43a2-846d-0b8f1f4f6c28',
		'd8b0d60b-8f3d-4ee4-a8b2-b58f5358d3cd',
		'3368ac8b-45da-47da-ac8c-2cf11fd4cdca',
		'ffeb7ee4-0d64-4014-9338-0071aa5d9434',
		'b38c964e-69b8-42b3-aa56-384a09995720'
	)

d8b0d60b-8f3d-4ee4-a8b2-b58f5358d3cd	true
0ca3bee0-1166-43e1-a82d-f49e41127170	true
b38c964e-69b8-42b3-aa56-384a09995720	true
ffeb7ee4-0d64-4014-9338-0071aa5d9434	true
7a7b4236-aa79-43a2-846d-0b8f1f4f6c28	true
604e4d47-073c-46f8-86a5-2a8838509986	true
3368ac8b-45da-47da-ac8c-2cf11fd4cdca	true

I'm not sure which version of Overture data is currently on the Snowflake Marketplace (this is maintained by CARTO), nor how geometries are handled in Snowflake. If these geometries are valid in Presto/Trino, but not in Snowflake, it presents an opportunity to dig into the differences.

[Rachmanin0xFF]

Chiming in here to say that all of the IDs in question are attached to POINT geometries in our data, which seems incongruous with the error message the user was getting (points do not have edges).