interfacing with postgis

[porjo]

Postgis is the geospatial addon for Postgresql. I'm wondering if there is any way to use Gorp's methods for Update & Insert with it?

For example, updating a geography object (longitude,latitude) value can be done easily enough with raw SQL:

pointStr := fmt.Sprintf("POINT(%f %f)", lon, lat)
_, err := DbMap.Exec("UPDATE place SET location = ST_GeogFromText($1) WHERE id = $2", pointStr, id)

However I can see no way to achieve this the 'Gorp' way - mapping table to struct etc. I've played around with TypeConverter but the problem is the Postgis function ST_GeogFromText has to be part of the query string and not contained in the bind parameter value.

[regexb]

+1

[regexb]

@porjo I ended up just using postgres triggers to create the geometry column on insert/update. Did you find figure out a way to make this work for you?

[porjo]

@begizi no, I'm just using raw SQL queries. I've not used triggers before, so I'd be interested to know how that works.

[nelsam]

This might help you guys. However, just wanted to mention, the TypeDef method implements an interface that I haven't quite made a PR for yet, but it's only used during a CREATE TABLE statement (or during automated migration, which is yet another PR I haven't made yet). As long as your column is already defined properly, this should work for geography types (I didn't feel like fiddling with geometry types, although I'm sure the solution is similar).

https://github.com/nelsam/gorq/blob/master/extensions/postgis.go

Feel free to copy whatever code you can make use of, or just import the package and use it from there. If you want to import the package and use the query language I've implemented in my little extension, the syntax of querying using radius values is something like:

type someModel struct {
  SomeGeoField extensions.Geography
}

gorqMap := &gorq.DbMap{DbMap:*dbMap}
m := someModel{}
g := extensions.Geography{Lat: someLat, Lng: someLng}
results, err := gorqMap.Query(&someModel).
  Where().
  Filter(extensions.WithinMeters(&someModel.SomeGeoField, g, 50).
  OrderBy(extensions.Distance(&someModel.SomeGeoField, g), "ASC").
  Select()