Geospatial Radial Search RESTful API with PostGIS and entgo Example

Let’s look at an example of building a location-based search RESTful API–something you might use to find restaurants or points of interest in a radius around you. Specifically, we’ll use a database that efficiently handles geospatial queries and a RESTful API that lets users store location data as well as radial search for nearby points of interest.

We will use a mix of common technologies for implementing this RESTful API and backend. For example, PostGIS extends the capabilities of the PostgreSQL relational database by adding support for storing, indexing, and querying geospatial data. Ent is a simple, yet powerful entity framework for Go, that makes it easy to build and maintain applications with large data-models. This post assumes you already have some knowledge and experience with PostgreSQL, PostGIS and entgo as they are already very well documented tools.

Going forward, you should have your PostgreSQL database setup and working with the PostGIS extension. Here is an example of ensuring the extension is available when bootstrapping our Ent client:

entClient, err := ent.Open(dialect.Postgres, ...)
...
_, err = entClient.ExecContext(context.Background(), "CREATE EXTENSION IF NOT EXISTS postgis;")
if err != nil {
    log.Fatal().Err(err).Msg("failed creating extension postgis")
}

Data Structure

Ent does not understand Geospatial types out of the box, so will create our own for use as an Other field inside our ent schema. The custom type must be either a type that is convertible to the Go basic type, a type that implements the ValueScanner interface, or has an External ValueScanner. We declare and implement a GeoJson struct as follows:

import (
	"database/sql/driver"
	"encoding/json"
	"entgo.io/ent/dialect/sql"
	"github.com/rs/zerolog/log"
	"github.com/twpayne/go-geom/encoding/ewkbhex"
	"github.com/twpayne/go-geom/encoding/geojson"
)

type GeoJson struct {
	*geojson.Geometry
}

func (t *GeoJson) Value() (driver.Value, error) {
	geometry, err := t.Decode()
	if err != nil {
		return nil, err
	}
	encodedGeometry, err := ewkbhex.Encode(geometry, ewkbhex.NDR)
	if err != nil {
		return nil, err
	}
	return encodedGeometry, nil
}

func (t *GeoJson) Scan(value interface{}) error {
	if value == nil {
		return nil
	}
	geometry, err := ewkbhex.Decode(string(value.([]uint8)))
	if err != nil {
		log.Error().Err(err).Msg("failed to decode geometry")
		return err
	}
	geometryAsBytes, err := geojson.Marshal(geometry)
	if err != nil {
		log.Error().Err(err).Msg("failed to marshal geometry")
		return err
	}

	var geoJson GeoJson
	if err = json.Unmarshal(geometryAsBytes, &geoJson); err != nil {
		log.Error().Err(err).Msg("failed to unmarshal GeoJson")
		return err
	}
	*t = geoJson
	return nil
}

Schema

Next, let’s set up an Ent schema and use our GeoJson type as a PostGIS geometry point. Doing so will allow us to leverage PostGIS for coordinate searches while converting back and forward to a common GeoJSON for our REST clients. We will also add some additional fields for each row to make this example more interesting.

We create a V1POI (storing points of interest) schema with some fields. A primary key id, string name, PostGIS geometry point location and some additional metadata we may want to attach in a JSON details field. It is helpful to have additional struct validation tags for when data is passed between HTTP clients using the StructTag method, but we will skip it in this example.

...
type V1POI struct {
	ent.Schema
}
...
func (V1POI) Fields() []ent.Field {
	return []ent.Field{
		field.UUID("id", uuid.UUID{}).
			Default(uuid.New).
			StorageKey("id"),
		field.String("name").
			NotEmpty().
			MinLen(1),
		field.Other("location", &GeoJson{}).
			SchemaType(map[string]string{
				dialect.Postgres: "geometry(point, 4326)",
			}),
		field.JSON("details", map[string]interface{}{}).
			Default(map[string]interface{}{}),
	}
}
...

Lastly, we want to create a spatial index on our location column so that coordinates are nicely indexed by PostGIS. Refer to the documentation linked, but it will look something like running this SQL in your database:

CREATE INDEX poi_location_geom_idx
  ON v1poi
  USING GIST (location);

Custom Radial Search Predicate

At this stage we have our schema set up and are ready to insert and query data. Because we have a custom field, we need to help entgo query using this column. We will write a custom predicate that uses the PostGIS syntax to properly query the database and use the benefits of PostGIS.

At the start of this example, we mentioned we want to query for points of interest around a given coordinate. That is, give me all the points around this lat, long coordinate within a x meters radius.

In SQL one way we could write this is:

...
WHERE ST_DistanceSphere(location, ST_MakePoint(lat, long, 4326)) <= radius_meters
...

where radius_meters is a number for the amount of meters.

Translating this into a custom entgo predicate we could have the following function:

func Radial(column, lat, long, radius string) func(selector *sql.Selector) {
	return func(selector *sql.Selector) {
		selector.Where(sql.P(func(b *sql.Builder) {
			b.WriteString("ST_DistanceSphere(").Ident(column).Comma().
				WriteString("ST_MakePoint(").
				Arg(lat).WriteString(", ").
				Arg(long).WriteString(", ").
				WriteString("4326)").
				WriteString(") ").
				WriteString("<= ").Arg(radius)
		}))
	}
}

Giving us the ability to reuse this custom predicate function across multiple handlers if desired. In addition to the standard lat, long and radius parameters we also pass the column name we would query–location in our example. Resulting in a query like so:

... EntClient.V1POI.Query().Where(Radial("location", "37.35411", "-121.95524", "1000")) ...

Handler

At this stage we have everything needed for constructing our RESTful API that can store and query geospatial data. Because we used GeoJSON in the GeoJson type we created we just need to conform to its formatting. So, calling an endpoint to create a point of interest could look something like this:

curl -X POST -d '{
    "name": "Test Restaurant",
    "location": {
        "type": "Point",
        "coordinates": [
            37.35411,
            -121.95524
        ]
    }
}' http://localhost:8080/v1/poi

Stefan Gloutnikov

Data Structure

Schema

Custom Radial Search Predicate

Handler