Pg: Support for geometric data types

[ghost]

Has go-pg support for PostgreSQL geometric data types?

[vmihailenco]

No, there is no any support for geometric data types.

[wavded]

Sorry I missed this case when looking at the previous issues about geometry. It would be super neat to have native support for PostGIS, at least the Geometry type.

I will close my case as its a duplicate:

https://github.com/go-pg/pg/issues/1034

[wavded]

Also, I am willing to work on a PR if desired to include as we are in the middle of a project that would benefit from this functionality.

[wavded]

Did some trial and error on making a custom type locally, but I'm stuck currently, it seems a ValueAppender adds stuff to the query (or visaversa), for a basic geometry type based off geojson we would need to use the ST_GeomFromGeoJSON function for inserts/updates but the ST_AsGeoJSON for selects. Not sure how to make that work for a custom type.

Was utilizing this library for GeoJSON (which does implement sql.Scanner)

https://github.com/paulmach/go.geojson

Note this does not compile, but what I had so far:

package types

import (
    "fmt"

    "github.com/go-pg/pg/types"
    "github.com/paulmach/go.geojson"
)

type geom struct {
    v *geojson.Geometry
}

var _ types.ValueAppender = (*geom)(nil)

func Geometry(gj string) types.ValueAppender {
    g, err := geojson.UnmarshalGeometry([]byte(gj))
    if err != nil {
        panic(fmt.Errorf("types.Geometry(unmarshal failed: %s)", err.Error()))
    }
    return &geom{g}
}

func (g *geom) Value() interface{} {
    return g.v
}

func (g *geom) AppendValue(b []byte, quote int) []byte {
    byt, err := g.v.MarshalJSON()
    if err != nil {
        panic(fmt.Errorf("types.Geometry(marshal failed: %s)", err.Error()))
    }
    b = append(b, "ST_GeomFromGeoJSON('"...)
    b = append(b, byt...)
    b = append(b, "')"...)
    return b
}

[andystroz]

I am looking for PostGIS support as well!

[wavded]

I was able to get this to work for my needs (I only needed the point type)... here is an example:

package types

import (
    "bytes"
    "database/sql/driver"
    "encoding/binary"
    "encoding/hex"
    "fmt"
)

// Point represents an x,y coordinate in EPSG:4326 for PostGIS.
type Point [2]float64

func (p *Point) String() string {
    return fmt.Sprintf("SRID=4326;POINT(%v %v)", p[0], p[1])
}

// Scan implements the sql.Scanner interface.
func (p *Point) Scan(val interface{}) error {
    b, err := hex.DecodeString(string(val.([]uint8)))
    if err != nil {
        return err
    }
    r := bytes.NewReader(b)
    var wkbByteOrder uint8
    if err := binary.Read(r, binary.LittleEndian, &wkbByteOrder); err != nil {
        return err
    }

    var byteOrder binary.ByteOrder
    switch wkbByteOrder {
    case 0:
        byteOrder = binary.BigEndian
    case 1:
        byteOrder = binary.LittleEndian
    default:
        return fmt.Errorf("Invalid byte order %d", wkbByteOrder)
    }

    var wkbGeometryType uint64
    if err := binary.Read(r, byteOrder, &wkbGeometryType); err != nil {
        return err
    }

    if err := binary.Read(r, byteOrder, p); err != nil {
        return err
    }

    return nil
}

// Value impl.
func (p Point) Value() (driver.Value, error) {
    return p.String(), nil
}

Definition:

type Record struct {
    Location   types.Point `sql:"type:geometry"`
}

Usage:

rec := &Record{
    Location: types.Point{-88, 43}
}

[giautm]

Tks @wavded , You saved me a day.

[irees]

@wavded The value can also be hex-encoded EWKB, if you don't want to use the EWKT representation above.

[vctqs1]

I got error when tried this.

package types

import (
    "bytes"
    "database/sql/driver"
    "encoding/binary"
    "encoding/hex"
    "fmt"
)

// Point represents an x,y coordinate in EPSG:4326 for PostGIS.
type Point [2]float64

func (p *Point) String() string {
    return fmt.Sprintf("SRID=4326;POINT(%v %v)", p[0], p[1])
}

// Scan implements the sql.Scanner interface.
func (p *Point) Scan(val interface{}) error {
    b, err := hex.DecodeString(string(val.([]uint8)))
    if err != nil {
        return err
    }
    r := bytes.NewReader(b)
    var wkbByteOrder uint8
    if err := binary.Read(r, binary.LittleEndian, &wkbByteOrder); err != nil {
        return err
    }

    var byteOrder binary.ByteOrder
    switch wkbByteOrder {
    case 0:
        byteOrder = binary.BigEndian
    case 1:
        byteOrder = binary.LittleEndian
    default:
        return fmt.Errorf("Invalid byte order %d", wkbByteOrder)
    }

    var wkbGeometryType uint64
    if err := binary.Read(r, byteOrder, &wkbGeometryType); err != nil {
        return err
    }

    if err := binary.Read(r, byteOrder, p); err != nil {
        return err
    }

    return nil
}

// Value impl.
func (p Point) Value() (driver.Value, error) {
    return p.String(), nil
}
Definition:

type Record struct {
    Location   types.Point `sql:"type:geometry"`
}
Usage:

rec := &Record{
    Location: types.Point{-88, 43}
}

[giautm]

@vctqs1 : I believe the error related to PostgreSQL rather this module. Did you try to install geometric plugin for postgresql?

Plugin PostGis: https://postgis.net

[iorlas]

Putting my 2 cents here, I have met too many clients writing their own wrappers for the POINT structure (geography mostly). It would be awesome to have at least this one type, somewhere in the extras.