Examples of postgis intergration

[morawskiOZ]

I am trying to add a Postigis extension on my Postgres DB which I use with gql API using gqlgen. I am struggling a bit.

Could someone share an example of how to add a custom type to ent schema and later translate it to GQL schema to use in qglgen. Examples of resolvers for queries & mutations of PostGIS entities (geography/geometry) would be most appreciated too.

[crossworth]

Hello @morawskiOZ

I will show the way I do this today, maybe is not the best example, but works for my needs.

I have a custom schema.MigrateOption to enable the extension:

// EnablePostgisOption returns a schema.MigrateOption
// that will enable the Postgres Postgis extension if needed.
func EnablePostgisOption(db *sql.DB) schema.MigrateOption {
	return schema.WithHooks(func(next schema.Creator) schema.Creator {
		return schema.CreateFunc(func(ctx context.Context, tables ...*schema.Table) error {
			_, err := db.ExecContext(ctx, `CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;`)
			if err != nil {
				return fmt.Errorf("could not enable postgis extension: %w", err)
			}

			return next.Create(ctx, tables...)
		})
	})
}

client.Schema.Create(ctx, schema.WithAtlas(true), EnablePostgisOption(db))

For working with Geometry I use WKT (since it's easy to handle and visualize, but could be changed to WKB), I have defined a custom Geometry type (also check that I use the generic geometry type, because I store different types of geometry in the same table, you could change to have Point, Linestring, Polygon as different types).

package types

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"io"
	"strings"

	"entgo.io/ent/dialect"
	"entgo.io/ent/dialect/sql"
	"github.com/twpayne/go-geom/encoding/ewkbhex"
	"github.com/twpayne/go-geom/encoding/wkt"
)

// Geometry represents a Postgis Geometry.
// It stores a WKT (Well-Known text) reference inside as a string.
type Geometry struct {
	wkt string
}

// NewGeometryFromWKT creates a new Geometry from the WKT provided.
func NewGeometryFromWKT(wkt string) Geometry {
	return Geometry{wkt: wkt}
}

// Scan implements the Scanner interface.
func (g *Geometry) Scan(value interface{}) error {
	if value == nil {
		return nil
	}

	geom, err := ewkbhex.Decode(value.(string))
	if err != nil {
		return err
	}

	wktOut, err := wkt.Marshal(geom)
	if err != nil {
		return err
	}

	g.wkt = wktOut
	return nil
}

// Value implements the driver Valuer interface.
func (g Geometry) Value() (driver.Value, error) {
	return g.wkt, nil
}

// WKT returns the Well-Known text from the geometry.
func (g Geometry) WKT() string {
	return g.wkt
}

// SetWKT sets the WKT for the geometry.
func (g *Geometry) SetWKT(wkt string) {
	g.wkt = wkt
}

// String returns the string representation of the Geometry.
func (g Geometry) String() string {
	return g.WKT()
}

// FormatParam implements the sql.ParamFormatter interface to tell the SQL
// builder that the placeholder for a Geometry parameter needs to be formatted.
func (g Geometry) FormatParam(placeholder string, info *sql.StmtInfo) string {
	return "ST_GeomFromText(" + placeholder + ", 4326)"
}

// GeometrySchemaType defines the schema-type of the Geometry type.
func GeometrySchemaType() map[string]string {
	return map[string]string{
		dialect.Postgres: "geometry",
	}
}

// MarshalJSON marshal the Geometry to JSON.
func (g Geometry) MarshalJSON() ([]byte, error) {
	if g.wkt == "" {
		return nil, nil
	}

	return []byte(`"` + g.wkt + `"`), nil
}

// UnmarshalJSON unmarshal the Geometry from JSON.
func (g *Geometry) UnmarshalJSON(data []byte) error {
	g.wkt = strings.ReplaceAll(string(data), `"`, "")
	return nil
}

// UnmarshalGQL implements the interface graphql.Unmarshaler.
func (g *Geometry) UnmarshalGQL(v interface{}) error {
	value, ok := v.(string)
	if !ok {
		return fmt.Errorf("type Geometry must be a string")
	}

	return json.Unmarshal([]byte(value), g)
}

// MarshalGQL implements the interface graphql.Marshaler.
func (g Geometry) MarshalGQL(w io.Writer) {
	_ = json.NewEncoder(w).Encode(g)
}

Take special note to FormatParam.

Schema definition:

field.Other("geometry", types.Geometry{}).Optional().Nillable().SchemaType(types.GeometrySchemaType()),

gqlgen.yml

models:
  Geometry:
    model: project/pkg/types.Geometry

For using functions/predicates, check:

• https://entgo.io/docs/predicates/#custom-predicates

Also check:

• https://github.com/a8m/entspatial

[morawskiOZ]

Many thanks for such a comprehensive answer! I will test it this week and see how it goes.

Do you have any examples of Custom Predicates with geometry? That would be very useful.

[crossworth]

Sorry for the late reply, I'm not sure if I didn't get the notification or just missed:

I have this simple example, there is others ways of doing it (like avoiding the ST_CollectionHomogenize or accepting a geometry struct that is "database" ready and can be used as an argument), in my case I always call ST_CollectionHomogenize to ensure all my geometries are the simplest (and standardized) as it can be, because of performance/index related problems.

func Intersects(column string, wkt string) func(selector *sql.Selector) {
	return func(selector *sql.Selector) {
		selector.Where(sql.P(func(b *sql.Builder) {
			b.WriteString("ST_Intersects(").Ident(column).Comma().
				WriteString("ST_CollectionHomogenize(").
				WriteString("ST_GeomFromText(").Arg(wkt).WriteString(", 4326)").
				WriteString(")").
				WriteString(")")
		}))
	}
}

But should warn you that this little function are not that great when doing complex queries, you may need to write the query (full or partially) manually, a lot of the times the left part of the expressing/predicate uses a lot of functions and things can get a little bit complicated trying to do everything on ent side of things (for now at least).

One pattern that I use a lot is doing a raw query using SQL Raw API or Custom SQL Modifiers that does the complex query but only returns the entity ID (and a few cases the distance, since is something a lot of geo related queries need), scan it on a struct and after do a query with geometry.IDIn(ids...), this seems like a good enough strategy for my needs.