Geospatial data support: spatial field types, spatial relationships, and geographic hierarchies

[xavipereztr]

Geospatial data is a significant and growing part of the modern analytics stack, yet the current OSI spec has no way to express spatial semantics. Fields that contain geometry columns, spatial indexes (H3, Quadbin, S2, Geohash), or geographic identifiers are indistinguishable from e.g. regular strings. Relationships between datasets that are inherently spatial — containment, proximity, intersection — can only be described as foreign key joins, losing the spatial semantics that downstream consumers (BI tools, AI agents, query engines) need to handle them correctly.

At CARTO, we'd like to open a conversation about adding first-class geospatial support to the OSI spec. We think there are three areas worth discussing.

1. Spatial data types at the field level

Today, a field containing an H3 spatial index looks like any other string field:

fields:
  - name: h3
    expression:
      dialects:
        - dialect: ANSI_SQL
          expression: "h3"
    dimension:
      is_time: false
    description: "H3 cell index at resolution 8"

Building on the ideas in #55 (Semantic Field Types), one approach could be adding spatial type metadata to the field definition:

fields:
  - name: h3
    expression:
    dialects:
        dialect: ANSI_SQL
         expression:
      dimension:
          is_time: false
          spatial_data:                        # proposed addition
              type: spatial_index
              system: h3                         # h3, quadbin, s2, geohash
              resolution: 8
      description: "H3 cell index at resolution 8"

  - name: geom
    expression:
      dialects:
          dialect: ANSI_SQL
          expression:
      dimension:
          is_time: false   
          spatial_data:                        # proposed addition
             type: point                        # point, polygon, line, geography
              srid: 4326
       description: "Store location as WGS84 point"

This lets consuming tools understand what spatial operations are valid — you can compute isochrones from points but not from polygons, you can roll up H3 cells to coarser resolutions, you should render a spatial index as a grid layer rather than as points.

2. Spatial relationships between datasets

OSI relationships currently support column-based joins:

relationships:
  - name: stores_in_territory
    from: stores
    to: territories
    from_columns: [territory_id]
    to_columns: [territory_id]

But many geospatial relationships are inherently spatial. Stores are contained within territories not because of a foreign key, but because the store's point falls inside the territory's polygon. Delivery zones intersect with flood plains. Cell towers cover areas within a radius. These are evaluated using spatial predicates (ST_CONTAINS, ST_INTERSECTS, ST_DWITHIN) — part of the OGC SQL standard — rather than equality joins.

A possible extension:

relationships:
  - name: stores_in_territory
    from: stores
    to: territories
    spatial_join:                         # proposed addition
      predicate: ST_CONTAINS
      from_column: geom                  # point column on stores
      to_column: boundary                # polygon column on territories
    description: "Stores are spatially contained within sales territories"

This could coexist with from_columns / to_columns — some datasets have both a spatial relationship and a denormalized foreign key.

3. Geographic hierarchies and aggregation semantics

Related to #21 (Dimension Hierarchies), geospatial data has a specific flavor of hierarchy worth calling out: geographic rollup levels. Data at H3 resolution 8 can be rolled up to resolution 7, 6, or 5. Store-level data can be aggregated to ZIP → city → state → country.

The spatial aspect adds a nuance compared to general dimension hierarchies: aggregation behavior varies by field. When rolling up census demographics from fine-grained cells to a broader region, population counts should be summed, but rates like median income or employment rate need population-weighted averaging. This aggregation guidance is tightly coupled with the geographic hierarchy — the hierarchy tells you which levels you can roll up to, and the aggregation semantics tell you how each field should behave during that rollup.

Why at the spec level?

These concepts aren't vendor-specific. H3, Quadbin, and Geohash are open standards. ST_CONTAINS and ST_INTERSECTS are part of the OGC SQL spec. Geographic hierarchies and aggregation semantics exist in every spatial dataset. We believe defining them at the OSI spec level (rather than as vendor custom_extensions) benefits the whole ecosystem.

That said, we're open to discussing where the boundary should be — perhaps a minimal core (spatial field types + spatial relationship predicates) at the spec level, with richer capabilities (aggregation guidance, rendering hints, analysis suggestions) left to custom_extensions.

About us

We're the team at CARTO, a cloud native geospatial analytics platform. We're building our Agentic GIS technology on top of OSI v1.0 and have started working with custom_extensions for geospatial metadata in mind. We'd like to contribute these learnings back to the community and work for a common spec for geospatial data support within OSI.

[pramsey]

spatial_join: # proposed addition
predicate: ST_CONTAINS

This feels overly restrictive to me. A pair of spatial columns can potentially be joined by any predicate, or even more complex relationships like DE9IM patterns. I think declaring a potential spatial relationship on its own gets you to the goal line.

[cayetanobv]

I agree, @pramsey, it can be restrictive. What if the predicate is optional? It can be included if you want to add this context.

[pramsey]

Yes, I guess, I just wouldn't want people to feel they have to fill in the slot for completeness sake. Then you get folks enumerating all the possible predicates they could use between two spatial columns, etc.