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.

[felippeduran]

Just for the record, I had to make a small tweak to make @wavded's code work for me. According to this documentation, I've found wkbGeometryType to be actually 32 bits (4 bytes), so I changed its type to uint32.

Before this tweak, I was getting an unexpected EOF error when parsing the geometry column.

[LaPetiteSouris]

please raise a hand and show respect to @wavded . This dude saves so many lives here !

[boyfunky]

I am having an issue here with @wavded implementation. when it runs the Scan method, i get this error interface conversion: interface {} is string, not []uint8

To give more context, i have a model that looks like this

type Device struct {
    gorm.Model
    Id                int       `json:"id" gorm:"primaryKey"`
    UserId            int       `json:"user_id" gorm:"uniqueIndex"`
    LatestLocation    Point  `json:"latest_location" gorm:"type:geography(POINT, 4326)"`
    CreatedAt         time.Time
    UpdatedAt         time.Time
}

So when it saves to the table, it saves the latest_location field like this

and i am trying to read the latest_location value in my code by doing something like this device.LatestLocation in one of my functions but it throws this error.

[giautm]

You create the column in the DB with the wrong column type. The POINT data is not supposed to display like a string when you view it

[boyfunky]

what type should it be @giautm?

[adriantabirta]

2023 outside PG(11-16) have default geometric data types, still need these data types:

Point
Circle
Box

🙏 🙏 🙏