Npgsql: Enable direct marshalling between CrateDB GEO_SHAPE and .NET GeoJSON types

[amotl]

Problem

Currently, CrateDB's GEO_SHAPE types cannot be directly marshalled to/from .NET GeoJSON types when using Npgsql. While GEO_POINT works well with NpgsqlPoint, GeoJSON geometry types require manual string serialization.

Current Behavior

The InsertGeoJsonTyped() method in by-language/csharp-npgsql/DemoTypes.cs attempts to use NpgsqlDbType.Geometry but fails with:

System.NotSupportedException: The NpgsqlDbType 'Geometry' isn't present in your 
database. You may need to install an extension or upgrade to a newer version.

Workaround: The InsertGeoJsonString() method manually serializes GeoJSON objects (Point, Polygon, etc.) to JSON strings using JsonConvert.SerializeObject() and passes them as NpgsqlDbType.Json.

Desired Behavior

Enable fluent marshalling similar to PostgreSQL with PostGIS, where GeoJSON types can be directly used:

• Use Npgsql's PostGIS/GeoJSON Type Plugin
• Directly pass .NET GeoJSON.Net types (Point, Polygon, etc.) as parameters
• Directly read GeoJSON types from query results without manual deserialization

Technical Details

• CrateDB communicates GEO_SHAPE as string types (WKT or GeoJSON format)
• The PostGIS/GeoJSON plugin expects the database to identify as PostGIS
• CrateDB does not currently identify itself as PostGIS (see review discussion)

Related

• PR Npgsql: Use NpgsqlPoint .NET type for marshalling GEO_POINT types. Explore communicating and marshalling GeoJSON types. #782: Initial exploration of Npgsql geospatial type support
• Comment: Npgsql: Use NpgsqlPoint .NET type for marshalling GEO_POINT types. Explore communicating and marshalling GeoJSON types. #782 (comment)
• Npgsql issue: The NpgsqlDbType 'Geometry' isn't present in your database.  npgsql/npgsql#2411

Tasks

• Investigate why NpgsqlDbType.Geometry is not recognized by CrateDB
• Determine if CrateDB can/should identify as PostGIS for geometry type compatibility
• Explore alternative approaches to enable direct GeoJSON type marshalling
• Consider supporting direct conversion to Npgsql geometry types (NpgsqlLSeg, NpgsqlBox, NpgsqlPath, NpgsqlPolygon, NpgsqlCircle)

/cc @kneth, @seut

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

• Create Plan

Examples

• Example 1
• Example 2

🔗 Similar Issues

Related Issues

• crate/cratedb-fivetran-destination#119

🔗 Related PRs

#1257 - .NET/CI: Improve testing, also against pre-release versions of Npgsql [merged]
#1276 - .NET: Npgsql 10 has been released [merged]

👤 Suggested Assignees

• amotl
• coderabbitai

---

🧪 Issue enrichment is currently in early access.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false