Open ghost opened 6 years ago
No, there is no any support for geometric data types.
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:
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.
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
}
I am looking for PostGIS support as well!
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}
}
Tks @wavded , You saved me a day.
@wavded The value can also be hex-encoded EWKB, if you don't want to use the EWKT representation above.
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}
}
@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
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.
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.
please raise a hand and show respect to @wavded . This dude saves so many lives here !
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.
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
what type should it be @giautm?
2023 outside PG(11-16) have default geometric data types, still need these data types:
Point
Circle
Box
🙏 🙏 🙏
I fixed error interface conversion: interface {} is string, not []uint8
with
// 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(val.(string))
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
}
Has go-pg support for PostgreSQL geometric data types?