interface {} is string, not []uint8 when scanning PostGIS Point from Postgres using pgx

Question

I have a database with a buildings table which contains a coordinate column of type GEOMETRY(POINT, 4326). With the hope to read the coordinates, I wrote the following code:

    rows, err := db.pool.Query(context.Background(), `select "uuid", "coordinate" from "building"`)
    defer rows.Close()

    for rows.Next() {
        var uuid pgtype.UUID
        var coordinate postgis.Point
        err := rows.Scan(&uuid, &coordinate)
        if err != nil {
            return err
        }
        log.Println("~~~", coordinate.X, coordinate.Y)
    }

Then, I am receiving the following error:

2020/08/22 18:32:32 [Recovery] 2020/08/22 - 18:32:32 panic recovered:
POST /get-state HTTP/1.1
Host: localhost:3000
Accept: */*
Accept-Encoding: gzip, deflate, br
Connection: keep-alive
Content-Length: 0
Postman-Token: 4e6f4dc7-9dc4-4cd4-a328-8da2bc86d43d
User-Agent: PostmanRuntime/7.26.3


interface conversion: interface {} is string, not []uint8
/usr/local/Cellar/go/1.14.6/libexec/src/runtime/iface.go:260 (0x100bd11)
    panicdottypeE: panic(&TypeAssertionError{iface, have, want, ""})
/Users/virtumonde/go/pkg/mod/github.com/cridenour/go-postgis@v1.0.0/decode.go:21 (0x10fda31)
    decode: ewkb, err := hex.DecodeString(string(value.([]byte)))
/Users/virtumonde/go/pkg/mod/github.com/cridenour/go-postgis@v1.0.0/point.go:48 (0x10fde58)
    (*Point).Scan: reader, err := decode(value)
/Users/virtumonde/go/pkg/mod/github.com/jackc/pgtype@v1.4.2/pgtype.go:590 (0x120ff39)
    scanPlanSQLScanner.Scan: return scanner.Scan(string(src))
/Users/virtumonde/go/pkg/mod/github.com/jackc/pgx/v4@v4.8.1/rows.go:220 (0x1627b2c)
    (*connRows).Scan: err := rows.scanPlans[i].Scan(ci, fieldDescriptions[i].DataTypeOID, fieldDescriptions[i].Format, values[i], dst)
/Users/virtumonde/go/pkg/mod/github.com/jackc/pgx/v4@v4.8.1/pgxpool/rows.go:70 (0x1634cd4)
    (*poolRows).Scan: err := rows.r.Scan(dest...)
/Users/virtumonde/Desktop/dev/terminus/terminus-server/db.go:49 (0x16388f3)
    Database.GetPlayers: err := rows.Scan(&uuid, &coordinate)
/Users/virtumonde/Desktop/dev/terminus/terminus-server/main.go:116 (0x163b26b)
    main.func4: players, err := db.GetPlayers()
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/context.go:161 (0x15a0eba)
    (*Context).Next: c.handlers[c.index](c)
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/recovery.go:83 (0x15b45bf)
    RecoveryWithWriter.func1: c.Next()
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/context.go:161 (0x15a0eba)
    (*Context).Next: c.handlers[c.index](c)
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/logger.go:241 (0x15b36f0)
    LoggerWithConfig.func1: c.Next()
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/context.go:161 (0x15a0eba)
    (*Context).Next: c.handlers[c.index](c)
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/gin.go:409 (0x15aac95)
    (*Engine).handleHTTPRequest: c.Next()
/Users/virtumonde/go/pkg/mod/github.com/gin-gonic/gin@v1.6.3/gin.go:367 (0x15aa3ac)
    (*Engine).ServeHTTP: engine.handleHTTPRequest(c)
/usr/local/Cellar/go/1.14.6/libexec/src/net/http/server.go:2836 (0x13a2692)
    serverHandler.ServeHTTP: handler.ServeHTTP(rw, req)
/usr/local/Cellar/go/1.14.6/libexec/src/net/http/server.go:1924 (0x139dffb)
    (*conn).serve: serverHandler{c.server}.ServeHTTP(w, w.req)
/usr/local/Cellar/go/1.14.6/libexec/src/runtime/asm_amd64.s:1373 (0x10640c0)
    goexit: BYTE    $0x90   // NOP

Thank you in advance. Any suggestions would be helpful.

From what I can tell it seems like `cridenour/go-postgis` can handle *only* `[]byte` (alias of `[]uint8`) while the `jackc/pgx` driver feeds it a `string`. What you can do is write a simple wrapper around `postgis.Point` that will ensure that the value passed to `(*postgis.Point).Scan` is always `[]byte`. Something like this: https://play.golang.org/p/iPD_z2vcCzp — mkopriva, Aug 22 '20 at 16:02
Worked like a charm! Can you describe a little bit of how the wrapper works, in global terms? My guess is that you're essentially replacing the Scan method and Go seems to understand this and use your method instead of the other one. — dclipca, Aug 22 '20 at 16:19
The wrapper implements the [`Scanner`](https://golang.org/pkg/database/sql/#Scanner) interface, just like `postgis.Point` and many other types that require custom scanning (decoding) of db values into Go values. The wrapper's implementation of the interface is using a [type switch](https://golang.org/ref/spec#Type_switches) to check the "intermediate" type of the value, if it's `[]byte` it delegates to postgis.Point's Scan method as is, if it's `string` it converts the value to `[]byte` and then delegates to postgis.Point's Scan. — mkopriva, Aug 22 '20 at 16:29
The standard `database/sql` package's `(*sql.Row).Scan` and `(*sql.Rows).Scan` check whether or not their arguments' types implement the `Scanner` interface, and if they do they invoke the `Scan` method on those arguments instead of trying to decode the db value themselves. The 3rd party package `jackc/pgx` that you're using provides the same feature. — mkopriva, Aug 22 '20 at 16:36
@mkopriva, it seems the values I get are skewed. For example instead of (-70.67637333333333 42.61538) I get (-3.235635480925084e-61, -1.8032341672154675e+27). What might be the interpretation? — dclipca, Aug 22 '20 at 16:50
I believe that's just default Println formatting, to check if the values are what you expect them to be first compare them to a constant with a plain if statement i.e. `if coordinate.X == -70.67637333333333`, then if it says `true` you can take a look at [`fmt`](https://golang.org/pkg/fmt/) package's documentation (which is used by `log` package) and see how to use the *"verbs"* to change the formatting of floats. — mkopriva, Aug 22 '20 at 16:54
... well maybe that's not actually the issue. You'll need to check how the data is stored and retrieved at each layer, from the client request through the Go variable, the db driver and into the db column, and then back... Somewhere some layer might be incorrectly encoding/decoding the data. — mkopriva, Aug 22 '20 at 17:01
It seems that PostGIS is sending the result in hexadecimal. I will investigate more and post the result. — dclipca, Aug 22 '20 at 17:41
Yes, indeed. If I am decoding it using a WKB converter it works perfectly. — dclipca, Aug 22 '20 at 21:32

score 0 · Accepted Answer · answered Aug 22 '20 at 21:33

0

import ("github.com/paulmach/orb/encoding/wkb")
err := rows.Scan(&uuid, wkb.Scanner(&coordinate))

answered Aug 22 '20 at 21:33

dclipca

1,739
1
16
51

interface {} is string, not []uint8 when scanning PostGIS Point from Postgres using pgx

1 Answers1