database/sql and custom column types

[Peter Nguyen]

Hi,

I'm using lib/pg in my project and has now encountered the Postgis geography(POINT,4326) column type that I need to both scan into a Point struct and also inserting Point structs back to that column. The way to insert data into that column type is to call the Postgis function ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326). How can this be solved using the database/sql package? Is there a way using the provided interfaces to map a Point struct into that SQL statement?

[Gyepi SAM]

Yes.

For inserts you can implement the db.driver.Valuer interface

import "database/sql/driver"

func (p Point) Value() (driver.Value, error) {
//Assuming that p.String() returns a correctly formatted string
return p.String(), nil
}

db.Exec("insert into x (point) values(ST_GeometryFromText(?, 4326))", point)

Of course, you can also skip the Valuer interface and just do:

db.Exec("insert into x (point) values(ST_GeometryFromText(?, 4326))", point.String())

but the former looks nicer. I would take this a step further and use a trigger
to centralize the conversion so the sql looks like:

db.Exec("insert into x (point) values(?)", point)

For scanning, you'd need to implement the db.Scanner interface.

func (p *Point) Scan(val interface{}) error {
// parse []byte into Point members
}

-Gyepi

[Matt Cottingham]

To add to Gyepi's post, a good example of Valuer usage is the NullTime type implemented in github.com/lib/pq:

https://github.com/lib/pq/blob/master/encode.go#L345

Check the hstore (key/value column type) directory for a more complicated example.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Andy Balholm]

On Tuesday, February 18, 2014 3:10:32 AM UTC-8, Peter Nguyen wrote:

I'm using lib/pg in my project and has now encountered the Postgis geography(POINT,4326) column type that I need to both scan into a Point struct and also inserting Point structs back to that column. The way to insert data into that column type is to call the Postgis function ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326). How can this be solved using the database/sql package? Is there a way using the provided interfaces to map a Point struct into that SQL statement?

If your column type is geography (rather than geometry), it's actually simpler than that. You can just do something like this:

create table test (p geography);

insert into test(p) values ('POINT(-118.4079 33.9434)');

The ST_GeometryFromText function is unnecessary in this situation. Virtually every type in Postgres has an input function that accepts a value formatted as text.

On the other hand, when you're scanning a point out of the DB, you do need to use the ST_AsText function in your query, since the default output format is a bunch of hexadecimal gibberish.

[Peter Nguyen]

Thanks for all your replies! I managed to create a type that maps to the geography column of Postgis, thanks to this package https://github.com/twpayne/gogeom. Here is the code in case anyone was looking for it:

http://play.golang.org/p/uKXaGvZ2C2

One strange thing is that according to the specification of WKB, the byte for geometry type should be from 1 to 7. But in the code, I had to use uint64 which read to a very large number (18580565393409). It's maybe because of different versions of Postgresq + Postgis, I'm not sure...

[sc...@doozee.org]

A quick aside regarding the code that you posted. Postgres (bizarrely, IMHO) stores longitude first, then latitude, so:

 return fmt.Sprintf("POINT(%v %v)", p.Lat, p.Lng)

should be:

 return fmt.Sprintf("POINT(%v %v)", p.Lng, p.Lat)

Thanks for sharing your code!

[Nigel Tao]

On Sat, Dec 3, 2016 at 4:26 AM, <sc...@doozee.org> wrote:
> A quick aside regarding the code that you posted. Postgres (bizarrely, IMHO)
> stores longitude first, then latitude, so:

Well, the usual order is "latitude, longitude", but on the standard
visualization (North is 'up' on typical maps), latitude
(North-South-ness) is 'y' and longitude (East-West-ness) is 'x', and
the usual order (for 2D points in general, not geographic locations)
is "x, y", not "y, x".

[Peter Nguyen]

Thanks for the explanation!