How to make friends Gorm and PostGIS, an industrial-grade solution

GORM Fantastic ORM for Golang.

PostGIS expands the capabilities of a relational database PostgreSQL adding support for storing, indexing, and querying geospatial data.

In this article, we will share our experience of integrating GORM and PostGIS, the difficulties when trying to use gorm to work with geometric data, and of course we will offer a ready-made solution.

This article was originally published Here.

Task

Implementation of a microservice responsible for working with geodata:

• Storage of delivery zone landfills;

• Storage of delivery points (customer addresses);

• Search for occurrences of a point in the delivery zones of establishments;

• Storing delivery routes calculated taking into account various parameters.

Since most of the microservices in the project (part of the project is described in the case Telegram App Shawarma bar & KINTO'S) written in Go with the main relational DBMS PostgreSQL. It was decided to store microservice data also in PostgreSQL, given previous positive experience with its PostGIS extension.

The following was determined technology stack: Go, GORM, PostgreSQL, PostGIS.

GORM and PostGIS integration problem

However, from the very beginning it was clear that GORM does not support geometric data types out of the box, so the decision was made to use raw SQL queries. This solution did not unlock the capabilities of GORM and significantly increased the complexity of developing and maintaining a microservice.

Searching for a solution on the Internet did not lead to success. The only thing I could find was an example of implementing a custom type Location on GORM website and several libraries that support only basic geometric types (Point and in some cases Polygon).

An example of using SQL queries to work with geodata

To work with geometric data we had to use SQL queries. For example, to get a polygon:

SELECT 
	p.id, 
	p.address_id, 
	ST_AsText(p.geo_polygon) as geo_polygon,
FROM public.polygons p 
WHERE p.id = $1

The geo_polygon field contains a polygon, using the function ST_AsText converted to wkt text format.

An example of a WKT string that can be contained in the geo_polygon field:

POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))

This text then needs to be converted into a structure for working with the polygon within the application.

To create tables with geometric data types (migration), we also had to write SQL queries:

CREATE TABLE IF NOT EXISTS public.addresses (
	id bigserial,
	address text NULL,
	geo_point geometry NOT NULL,

	CONSTRAINT pk_address_id PRIMARY KEY(id)
);

Main problems

1. Compared to functions that use gorm's full capabilities, functions with SQL queries were 2-3 times longer and, accordingly, less readable.

2. The ability to use gorm automatic migration is no longer available.

3. An inappropriate data format was chosen, since using WKT is much less productive than WKB, I helped verify this benchmarkwhich clearly shows the difference in performance when working with WKT and WKB formats.

Benchmark results:

The results show that converting a polygon into the WKT text format for transmission to the database takes 5 times longer than converting it into the WKB binary format. And retrieving a value from a database in text format will require 9 times more time than data in binary format.

Solution

To simplify and optimize the work with geodata in GORM, it was decided to write our own types for geometries that will expand the functionality of gorm.

The following types are supported:

• Point

• LineString

• Polygon

• MultiPoint

• MultiLineString

• MultiPolygon

• GeometryCollection

Implementation of interfaces:

• sql.Scanner And driver.Value facilitated easy acquisition and recording of data.

• schema.GormDataTypeInterface ensured that GORM behaved correctly when migrating tables with geometry types.

• fmt.Stringer added the ability to display data in the human-readable WKT format.

The solution is based on a library go-geom implementing effective geometry types for geospatial applications, in addition, go-geom has support for an unlimited number of dimensions, implements encoding and decoding in the wkb format and other formats, functions for working with 2D and 3D topologies and other features.

The solution is in some way an adaptation of go-geom to work with GORM and is called georm (a combination of the words “geometry” and “ORM”). You can check out the solution on GitHub georm.

Examples of use

Description of structures with geometric types:

type Address struct {  
    ID       uint `gorm:"primaryKey"`  
    Address  string  
    GeoPoint georm.Point  
}  
  
type Zone struct {  
    ID         uint `gorm:"primaryKey"`  
    Title      string  
    GeoPolygon georm.Polygon  
}

Simple, automatic gorm migration.

db.AutoMigrate(  
    // CREATE TABLE "addresses" ("id" bigserial,"address" text,"geo_point" Geometry(Point, 4326),PRIMARY KEY ("id"))  
    Address{},  
    // CREATE TABLE "zones" ("id" bigserial,"title" text,"geo_polygon" Geometry(Polygon, 4326),PRIMARY KEY ("id"))  
    Zone{},  
)

Full use of ORM capabilities for queries, transmission of geometric data in wkb format:

// INSERT INTO "addresses" ("address","geo_point") VALUES ('some address','010100000000000000000045400000000000003840') RETURNING "id"  
tx.Create(&Address{  
    Address: "some address",  
    GeoPoint: georm.Point{  
       Geom: geom.NewPoint(geom.XY).MustSetCoords(geom.Coord{42, 24}),  
    },  
})
// ...

// INSERT INTO "zones" ("title","geo_polygon") VALUES ('some zone','010300000001000000050000000000000000003e4000000000000024400000000000004440000000000000444000000000000034400000000000004440000000000000244000000000000034400000000000003e400000000000002440') RETURNING "id"
tx.Create(&Zone{  
    Title: "some zone",  
    GeoPolygon: georm.Polygon{  
       Geom: geom.NewPolygon(geom.XY).MustSetCoords([][]geom.Coord{  
          {{30, 10}, {40, 40}, {20, 40}, {10, 20}, {30, 10}},  
       }),  
    },  
})
// ...

// SELECT * FROM "zones" WHERE ST_Contains(geo_polygon, '0101000020e610000000000000000039400000000000003a40') ORDER BY "zones"."id" LIMIT 1  
db.Model(&Zone{}).  
    Where("ST_Contains(geo_polygon, ?)", point).  
    First(&result)
// ...

Not a big bonus – implementation of the fmt.Stringer interface, output in human-readable wkt format.

// POINT (25 26)  
fmt.Println(georm.Point{  
    Geom: geom.NewPoint(geom.XY).MustSetCoords(geom.Coord{25, 26}).SetSRID(georm.SRID),  
})  
  
// POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))  
fmt.Println(georm.Polygon{  
    Geom: geom.NewPolygon(geom.XY).MustSetCoords([][]geom.Coord{  
       {{30, 10}, {40, 40}, {20, 40}, {10, 20}, {30, 10}},  
    }),  
})

For more information and usage examples, visit the repository georm on GitHub.