Feat: support a stable representation for geometry datatypes

[NickCrews]

Is your feature request related to a problem? Please describe.

sqlglot currently parses the type parameters of GEOMETRY datatypes opaquely, leaving them just as found, not converting to a uniform format. This means that when we work with the datatype expression, we aren't able to know which DataTypeParam represents what in the expression:

import sqlglot
import sqlglot.expressions as sge

duckdb = "GEOMETRY('EPSG:4269')"
e = sqlglot.parse_one(duckdb, read="duckdb", into=sge.DataType)
print(repr(e))
print("duckdb")
print(e.sql(dialect="duckdb"))
print("postgres")
print(e.sql(dialect="postgres"))

print()
print("---")
print()

postgres = "geography(POINT,4269)"
e = sqlglot.parse_one(postgres, read="postgres", into=sge.DataType)
print(repr(e))
print("duckdb")
print(e.sql(dialect="duckdb"))
print("postgres")
print(e.sql(dialect="postgres"))

Gives:

DataType(
  this=DType.GEOMETRY,
  expressions=[
    DataTypeParam(
      this=Literal(this='EPSG:4269', is_string=True))],
  nested=False)
duckdb
GEOMETRY('EPSG:4269')
postgres
GEOMETRY('EPSG:4269')

---

DataType(
  this=DType.GEOGRAPHY,
  expressions=[
    DataTypeParam(
      this=Var(this=POINT)),
    DataTypeParam(
      this=Literal(this=4269, is_string=False))],
  nested=False)
duckdb
GEOGRAPHY(POINT, 4269)
postgres
GEOGRAPHY(POINT, 4269)

This appeared in ibis-project/ibis#11978. In particular, when ibis tries to convert a sqlglot datatype to an ibis datatype, we would need to know the dialect it is coming from. This is POSSIBLE to do on the ibis side, but if it was solved here in sqlglot, then the above transpilation would work in vanilla sqlglot (which would be good for your users) and it would be much simpler to implement in ibis since we wouldn't need to branch based on the dialect.

Describe the solution you'd like
Some stable contract where, when handed a sge.DataType with a typecode of GEOMETRY, I know how to extract out the shapecode (eg POINT, LINE, ...) and the SRID (eg "EPSG:4269") WITHOUT needing to know which dialect it came from.

Similarly, when CONSTRUCTING a sge.DataType, there is a stable contract of where I should put the shapecode and SRID such that sqlglot will compile it to the right thing for each dialect.

Describe alternatives you've considered
Do nothing. We solve it on the ibis side by delegating based on the dialect. This means that your pure-sqlglot users wouldn't be able to get the above transpilation though.

Additional context
I have NOT done a survey of other SQL dialects to see what sort of parameterization they support in their geography types. But that probably would be a pre-requisite if we wanted to design one IR here that worked for all dialects.

dialect	params	example	link
bigquery		GEOGRAPHY	https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/data-types#geography_type
postgres	shapecode, numeric srid	GEOGRAPHY(POINT, 4269)
postgres	namespaced srid	GEOMETRY('EPSG:4269')

[georgesittas]

Hey @NickCrews, thank you for the ticket.

This is low priority for the core SQLGlot team and hence we don't plan to tackle it ourselves right now. However, we'd happily welcome a PR for it if you want to try it yourself.

I'm not sure if we need to implement a solution that covers all dialects in one go. Perhaps we can introduce a new node that would be in the place of the DataTypeParams we generate today, to represent the Coordinate Reference System identifier in a "canonical" fashion, enabling transpilation, as you're suggesting.

Then you can handle roundtrip & transpilation for the current set of target dialects (duckdb, postgres). That means you can only get this new IR node if you're reading with either of the two and can hence making an assumption that DuckDB <-> Postgres will be handled.

If you end up doing this, I'd urge to also take a look at least at Snowflake, BigQuery, Databricks, since they're very popular. But if you don't have bandwidth, the team can assist with this once a PR is up.

Cheers.

[NickCrews]

Thanks, that makes sense this is low priority, thanks for the honesty.

Are you imagining an IR that looks like this? Eg the expressions of a DataType are ALWAYS DataTypeParam, but we just change the things they wrap from plain Literals to these semantic objects?

DataType(
  this=DType.GEOMETRY,
  expressions=[
    DataTypeParam(
      this=CoordinateReferenceSystem(
        this='EPSG:4269'
    )),
   DataTypeParam(
     this=GeometryShapeType.POINT)
  ],
  nested=False)

[georgesittas]

Exactly, that's how imagined it. And then you can override the generator methods for these new expressions to produce what you need.

[NickCrews]

Here is a summary of the different dialects behaviors. generated with claude code, I spot checked, but might contain small errors or omissions.

Dialect	Geometry SRID	Geography SRID	Geometry Shapecode	Geography Shapecode	Type Syntax Example	Docs
Postgres	type-level SRID supported; common typmod form numeric SRID; sqlglot issue example also uses namespaced CRS text in geometry modifier ('EPSG:4269')	type-level SRID supported; numeric SRID in typmod (example 4269)	type-level shapecode supported in typmod (e.g. POINT, LINESTRING, POLYGON)	type-level shapecode supported in typmod (e.g. POINT)	GEOGRAPHY(POINT, 4269) and GEOMETRY('EPSG:4269')	https://postgis.net/docs/using_postgis_dbmanagement.html
DuckDB	type parameter can carry CRS/SRID-like text, example EPSG:4269; effectively metadata in type arg	n/a, duckdb only has geometry, no geography type	no type-level shapecode parameter; shape kind value-level	n/a, duckdb only has geometry, no geography type	GEOMETRY('EPSG:4269')	https://duckdb.org/docs/stable/core_extensions/spatial/overview
BigQuery	n/a, bigquery only has geography, no geometry	no SRID type parameter; WGS84 geodesic semantics implicit	n/a, bigquery only has geography, no geometry	no type-level shapecode; shape kind value-level only	GEOGRAPHY	https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#geography_type
Databricks	type-level SRID parameter present; GEOMETRY(srid); supports many numeric SRIDs; also ANY (runtime/value-dependent SRID, not fixed in persisted type)	type-level SRID parameter present; GEOGRAPHY(srid) but constrained to 4326 or ANY	no type-level shapecode parameter; shape kind value-level	no type-level shapecode parameter; shape kind value-level	GEOMETRY(3857), GEOMETRY(ANY), GEOGRAPHY(4326), GEOGRAPHY(ANY)	https://docs.databricks.com/aws/en/sql/language-manual/data-types/geometry-type and https://docs.databricks.com/aws/en/sql/language-manual/data-types/geography-type
Snowflake	no SRID type parameter on declaration; SRID carried on GEOMETRY value/object; default 0 when not set	no SRID type parameter on declaration; GEOGRAPHY semantics fixed to WGS84 / SRID 4326	no type-level shapecode parameter; shape kind value-level	no type-level shapecode parameter; shape kind value-level	GEOMETRY and GEOGRAPHY	https://docs.snowflake.com/en/sql-reference/data-types-geospatial

• Cross-dialect pattern: shapecode is type-level mainly in Postgres typmods; other dialects keep shape kind in values.
• SRID representation split:
  • Type-level explicit: Postgres, Databricks.
  • Type-level text/CRS flavor: DuckDB geometry parameter.
  • Type-level absent, semantics implicit/fixed: BigQuery GEOGRAPHY, Snowflake GEOGRAPHY.
  • Value-level SRID model: Snowflake GEOMETRY.
• ANY behavior (Databricks): type does not lock SRID at schema level; SRID can vary per value at runtime.

[georgesittas]

Lots of terminology that I'm not familiar with here, @NickCrews. Can you help with a TL;DR? Do you see a reasonable path forward?

[NickCrews]

Sorry, that was more of notes for me/posterity, instead of actually looking for something from you. I need to think more about this to propose actual behavior, it might be untenable. But posting this here for now as more ongoing notes, hopefully this is helpful.

Shapecode (eg POINT vs LINE vs MULTILINE)

is stored/enforced at the column/datatype level only for postgres. For the other dialects, a column can hold a mix of any of the shapes, and that info is stored per-value.

So we could add a GeometryShapeType, implement it for reading/writing to postgres, and the other backends they don't have to worry about it for reading. Not sure what they would do about when they write a dtype with a GeometryShapeType: should they just ignore it, or warn?

SRID (eg 'EPSG:4269')

Defines how the raw (x, y, z) numbers should be interpreted. for example:

• SRID 4326: Represents WGS 84, the standard used by GPS. The raw numbers should be interpreted as degrees for longitude and latitude. Assumes a curved surface
• SRID 6393 (Alaska Albers): an SRID optimized for mapping in alaska. x,y numbers are interpreted as meters. Assumes a flat surface.
• SRID 3857: The Web Mercator projection used by Google Maps, OpenStreetMap, and most web mapping libraries.
• etc

For our problem here, this much trickier than typecodes since dialects do different things. Some dialects store this per-value, thus a column can hold a mix of SRIDs. Others store the SRID per column and thus all values in that col are that SRID. Some dialects support multiple SRIDs, others (eg bigquery) only support WGS 84 (eg lat/lon).

[knaaptime]

if it's worth anything, the geoparquet spec defines the SRID/CRS at the column level (not per-geometry). I think from what I can tell they also had a similar discussion over in duckdb-spatial recently