GORM A fantastic ORM for Golang.

PostGIS extends the capabilities of the PostgreSQL relational database by 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 offer a ready-made solution.

This article was originally published here.

Task

Implementation of a microservice responsible for working with geodata:

• Storage of landfills of delivery zones;

• Storage of delivery points (customer addresses);

• Search for entry points to delivery zones of institutions;

• Storage of delivery routes, calculated taking into account various parameters.

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

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

GORM and PostGIS integration problem

However, it was clear from the beginning 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 allow to reveal 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 managed to find was an example implementation of the user type Location on the GORM site and several libraries supporting only basic geometric types (Point and in some cases Polygon).

An example of using SQL queries to work with geodata

Before working with geometric data, you 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 a function ST_AsText converted to wkt text format.

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

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

This text must then be converted into a structure for working with the polygon inside the application.

To create tables with geometric data types (migration), it was also necessary 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)
);

The main problems

1. Compared to functions using gorm’s capabilities in full, functions with SQL queries were 2-3 times longer and, accordingly, less readable.

2. There is an opportunity to use gorm automatic migration.

3. The wrong data format was selected, since using WKT is many times less productive than WKB, a benchmark that clearly shows the difference in performance when working with WKT and WKB formats helped to make sure of this.

Benchmark results:

From the results, it can be seen that the conversion of the polygon into WKT text format for DB transfer takes 5 times more time than conversion into WKB binary format. And getting a value from the database in text format will require 9 times more time than data in binary format.

Decision

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

Support for the following types has been implemented:

• Point

• LineString

• Polygon

• MultiPoint

• MultiLineString

• MultiPolygon

• GeometryCollection

Implementation of interfaces:

• sql.Scanner and driver.Valuer facilitated easy data retrieval and writing.

• schema.GormDataTypeInterface ensured correct behavior of GORM when migrating tables with geometric types.

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

The basis of the solution is the go-geom library, which implements effective types of geometry for geospatial applications, in addition, go-geom supports an unlimited number of measurements, implements encoding and decoding into wkb format and other formats, functions for working with 2D and 3D topologies and others features

The solution is to some extent an adaptation of go-geom to work with GORM and got its name 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  
}

Easy, 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, transfer 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 georm repository on GitHub.