Postgis types (using paulmach's orb package)

[sid6mathur]

Any tips on how to scan Postgis types into structs with scany while still maintaining the high-level abstractions that it helps with ?

A sample table has a PostGIS geometry type amongst many vanilla types:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE IF NOT EXISTS cities (
    cityname TEXT,
    -- many other built-in types  --
    geom geometry(Point, 4326) -- PostGIS type for a lat/long plus projection 4326
);

The Go struct type to scan into:

type City struct {
    Cityname string
    // many other built-in types that scany handles well
    Geom orb.Point // This is a simple lat/long coordinate type from https://github.com/paulmach/orb
}

sqlQuery := "SELECT cityname, geom from locations"

var cities []*City
err := pgxscan.Select(ctx, db, & cities, sqlQuery) 

Now, the above code is missing the logic for parsing the PostGIS geometry type into an orb.Point (For orb.Point, see https://github.com/paulmach/orb)

Now, the wonderful author of orb.Point does provide a utility function that implements the Scanner interface from database/sql of stlib. https://pkg.go.dev/github.com/paulmach/orb/encoding/ewkb

Their example for using the ewkb.Scanner wrapper to slurp PostGIS data into an orb.Point is in the call to Scan() below:

// both of these queries return the same data
row := db.QueryRow("SELECT ST_AsEWKB(geom) FROM geodata")
row := db.QueryRow("SELECT geom FROM geodata")

// if you don't need the SRID
p := orb.Point{}
err := row.Scan(ewkb.Scanner(&p))
log.Printf("geom: %v", p)

// if you need the SRID
p := orb.Point{}
gs := ewkb.Scanner(&p)
err := row.Scan(gs)

log.Printf("srid: %v", gs.SRID)
log.Printf("geom: %v", gs.Geometry)
log.Printf("also geom: %v", p)

Given that scany doesn't have a Scan function that can take Scanner implementing types, I am confused on how to use ewkb.Scanner to swizzle a PostGIS point into an orb.Point type struct member , while still using scany for all my other struct members.

Any guidance appreciated :)

[georgysavva]

That's an interesting use case. Thanks for opening the issue.

For Scany to know how to handle a custom type, the type needs to implement the sql.Scanner interface. The problem with orb.Point{} is that it doesn't implement it on its own, only after being manually wrapped via the ewkb.Scanner() function call. To solve this, you should implement a custom wrapper type around orb.Point{} using ewkb.Scanner() internally, something like ScannablePoint{}, which has the Scan() method and conforms to the sql.Scanner interface.

I hope this helps. Let me know if you more help with this.

[sid6mathur]

Thanks for your tips, @georgysavva . I realized while attempting a composition of the nature you suggested that the GeometryScanner type in orb's encoding/ewkb already supports the sql.Scanner interface, and hence works out of the box with scany using pgx as follows.

Postgresql+PostGIS table configured as follows:

CREATE EXTENSION IF NOT EXISTS postgis;
DROP TABLE IF EXISTS test_regions;
CREATE TABLE test_regions (
    regionid TEXT, -- 'us-east2' OR 'usgovcentral' OR 'china-east2'
    geom geometry(Point, 4326) -- a PostGIS geolocation with SRID configured as 4326 aka GPS
);
INSERT INTO test_regions (regionid, geom) VALUES
('us-west-2', ST_SetSRID(ST_GeomFromText('POINT(-122.68 45.52)'), 4326)),
('ap-southeast-2', ST_SetSRID(ST_GeomFromText('POINT(151.21 -33.87)'), 4326));

The above table with two rows can be read successfully using scany as follows:

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/georgysavva/scany/v2/pgxscan"

    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/paulmach/orb/encoding/ewkb"
)

// Represents (say) a datacenter region in a cloud company's infrastructure and its geolocation
type Region struct {
    RegionId string               `db:"regionid"`
    Loc      ewkb.GeometryScanner `db:"geom"` // The scanner type wraps an orb.Geometry
}

func main() {
    var err error
    db, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        panic(err)
    }
    var regions []*Region
    // Note of the use of PostGIS ST_AsEWKB() in the query below
    rows, err := db.Query(context.Background(), `SELECT regionid, ST_AsEWKB(geom) as geom FROM test_regions;`)
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    if err := pgxscan.ScanAll(&regions, rows); err != nil {
        panic(err)
    }
    for _, r := range regions {
        if !r.Loc.Valid {
            fmt.Printf("Warning: Invalid geometry for %s\n", r.RegionId)
            continue
        }
        fmt.Printf("Region = %s at PostGIS geometry [type = %s, projection/SRID = %d, lng/lat = %v]\n",
            r.RegionId,
            r.Loc.Geometry.GeoJSONType(), r.Loc.SRID, r.Loc.Geometry)
    }
}

Note that the above code uses orb's ewkb.GeometryScanner which in turn uses the generic type orb.Geometry.

The two data center locations are printed correctly:

 Region = us-west-2 at PostGIS geometry [type = Point, projection/SRID = 4326, lng/lat = [-122.68 45.52]]
 Region = ap-southeast-2 at PostGIS geometry [type = Point, projection/SRID = 4326, lng/lat = [151.21 -33.87]]

At your discretion, you may add the above PostGIS interop via https://github.com/paulmach/orb and its ewkb.GeometryScanner to your README, and then close this issue.

Thanks very much!

[georgysavva]

I am glad you were able to solve your issue. I don't think adding this information to Scany's Readme is necessary because your use case is pretty niche, and other users don't seem to have similar problems. But thank you for your investigation. If more use cases come up in the future, I will document this somewhere.