Hey there! Let's break down how to solve this PostGIS + GORM + orb dilemma without having to manually write SQL everywhere. I've dealt with similar spatial type headaches in Go, so here are a few solid, schema-agnostic approaches:

1. PostgreSQL Triggers & Rules (Database-Level Automation) #

Triggers are a clean way to handle geometry conversion at the database layer, keeping your Go code free of hardcoded SQL. Here's how to set them up:

For Inserts/Updates (WKB → PostGIS Geometry) #

If your table has a geometry column (e.g., geom), create a BEFORE INSERT/UPDATE trigger that automatically converts the raw WKB bytes (from orb's Value() method) into a valid PostGIS geometry using ST_GeomFromWKB:

CREATE OR REPLACE FUNCTION convert_wkb_to_geom()
RETURNS TRIGGER AS $$
BEGIN
  -- Replace 'geom' with your actual geometry column name
  NEW.geom := ST_GeomFromWKB(NEW.geom);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_convert_wkb_to_geom
BEFORE INSERT OR UPDATE ON your_table_name
FOR EACH ROW EXECUTE FUNCTION convert_wkb_to_geom();

Now when GORM inserts raw WKB bytes from your orb type, the trigger handles the conversion to a proper PostGIS geometry before storage.

For Queries (PostGIS Geometry → WKB) #

To avoid wrapping every query in ST_AsBinary(), you have two options:

• View with INSTEAD OF Trigger: Create a view that returns ST_AsBinary(geom) as geom, then add an INSTEAD OF INSERT trigger to route inserts back to the original table. This lets you query the view directly, and inserts work seamlessly:

  CREATE VIEW your_table_view AS
  SELECT id, ST_AsBinary(geom) AS geom, other_columns FROM your_table_name;
  
  CREATE OR REPLACE FUNCTION insert_into_view()
  RETURNS TRIGGER AS $$
  BEGIN
    INSERT INTO your_table_name (id, geom, other_columns)
    VALUES (NEW.id, ST_GeomFromWKB(NEW.geom), NEW.other_columns);
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trigger_insert_view
  INSTEAD OF INSERT ON your_table_view
  FOR EACH ROW EXECUTE FUNCTION insert_into_view();
  

• Implicit PostgreSQL Cast: Define an implicit cast from geometry to bytea using ST_AsBinary, so queries automatically return WKB bytes. Note: This can have side effects for other apps using the database, so test carefully:

  CREATE CAST (geometry AS bytea) WITH FUNCTION ST_AsBinary(geometry) AS IMPLICIT;
  

  With this, PostgreSQL returns WKB directly when you query the geometry column, which orb's Scan() method can parse without extra work.

2. GORM Callbacks (Application-Level Automation) #

If you prefer keeping logic in Go, use GORM's callback system to automatically wrap geometry fields with the required PostGIS functions. Since your library is schema-agnostic, you can detect fields that implement orb's Geometry interface and modify SQL on the fly:

For Inserts/Updates #

Register a before-create/before-update callback to wrap geometry values with ST_GeomFromWKB:

import (
  "fmt"
  "gorm.io/gorm"
  "github.com/paulmach/orb"
)

func init() {
  // Handle inserts
  gorm.DefaultCallback.Create().Before("gorm:create").Register("wrap_geom_insert", func(db *gorm.DB) {
    for _, field := range db.Statement.Schema.Fields {
      if _, ok := field.Field.Interface().(orb.Geometry); ok {
        value, _ := field.ValueOf(db.Statement.Context, db.Statement.ReflectValue)
        db.Statement.SetColumn(field.DBName, gorm.Expr("ST_GeomFromWKB(?)", value))
      }
    }
  })

  // Handle updates (similar logic)
  gorm.DefaultCallback.Update().Before("gorm:update").Register("wrap_geom_update", func(db *gorm.DB) {
    for _, field := range db.Statement.Schema.Fields {
      if _, ok := field.Field.Interface().(orb.Geometry); ok {
        if val, ok := db.Statement.ChangedFields[field.DBName]; ok {
          db.Statement.SetColumn(field.DBName, gorm.Expr("ST_GeomFromWKB(?)", val))
        }
      }
    }
  })
}

For Queries #

Register a before-query callback to modify select clauses to use ST_AsBinary() for geometry fields:

func init() {
  gorm.DefaultCallback.Query().Before("gorm:query").Register("wrap_geom_select", func(db *gorm.DB) {
    modifiedSelects := make([]string, 0)
    if len(db.Statement.Selects) == 0 {
      // Select all columns, wrap geometry ones
      for _, field := range db.Statement.Schema.Fields {
        if _, ok := field.Field.Interface().(orb.Geometry); ok {
          modifiedSelects = append(modifiedSelects, fmt.Sprintf("ST_AsBinary(%s) AS %s", field.DBName, field.DBName))
        } else {
          modifiedSelects = append(modifiedSelects, field.DBName)
        }
      }
    } else {
      // Wrap only explicitly selected geometry columns
      for _, col := range db.Statement.Selects {
        field := db.Statement.Schema.LookUpField(col)
        if field != nil {
          if _, ok := field.Field.Interface().(orb.Geometry); ok {
            modifiedSelects = append(modifiedSelects, fmt.Sprintf("ST_AsBinary(%s) AS %s", col, col))
          } else {
            modifiedSelects = append(modifiedSelects, col)
          }
        } else {
          modifiedSelects = append(modifiedSelects, col)
        }
      }
    }
    db.Statement.Select(modifiedSelects...)
  })
}

This way, GORM automatically handles the PostGIS function wrapping for all geometry fields, no hardcoding required.

3. Custom GORM Data Type (Encapsulated Logic) #

Create a generic wrapper type for orb geometries that handles PostGIS interactions internally. This keeps logic encapsulated and reusable across your library:

import (
  "database/sql/driver"
  "fmt"
  "gorm.io/gorm"
  "gorm.io/gorm/schema"
  "github.com/paulmach/orb"
  "github.com/paulmach/orb/wkb"
)

// Generic wrapper for any orb Geometry type
type GORMGeometry[T orb.Geometry] struct {
  T
}

func (g GORMGeometry[T]) Value() (driver.Value, error) {
  return wkb.Marshal(g.T), nil
}

func (g *GORMGeometry[T]) Scan(value interface{}) error {
  b, ok := value.([]byte)
  if !ok {
    return fmt.Errorf("expected []byte for geometry scan, got %T", value)
  }
  geom, err := wkb.Unmarshal(b)
  if err != nil {
    return err
  }
  typedGeom, ok := geom.(T)
  if !ok {
    return fmt.Errorf("failed to convert geometry to target type")
  }
  g.T = typedGeom
  return nil
}

func (GORMGeometry[T]) GormDataType() string {
  return "geometry"
}

func (GORMGeometry[T]) GormDBDataType(db *gorm.DB, field *schema.Field) string {
  if db.Dialector.Name() == "postgres" {
    // Adjust SRID and type as needed (e.g., "geometry(Point,4326)")
    return "geometry"
  }
  return ""
}

// Hook to wrap inserts with ST_GeomFromWKB
func (g *GORMGeometry[T]) BeforeCreate(tx *gorm.DB) error {
  for _, field := range tx.Statement.Schema.Fields {
    if field.Field.Interface() == g {
      tx.Statement.SetColumn(field.DBName, gorm.Expr("ST_GeomFromWKB(?)", wkb.Marshal(g.T)))
      break
    }
  }
  return nil
}

Use this wrapper like GORMGeometry[orb.Point] in your models, and it handles all PostGIS conversion logic automatically.

All these approaches let you avoid manual SQL writing while keeping your library schema-agnostic. Triggers/casts are great for database-side automation, while GORM callbacks/custom types keep logic in your Go code.

内容的提问来源于stack exchange，提问作者robbieperry22