title: Inserting and selecting PostGIS Geometry with Gorm
body: <p>I've been trying to find a way to insert and retrieve geometric types using Golang, and specifically the library <a href="http://gorm.io/docs/" rel="noreferrer">gorm</a>. I'm also attempting to use the library <a href="https://github.com/paulmach/orb" rel="noreferrer">orb</a> that defines different types for geometries, and provides encoding/decoding between different formats.</p>
  
  <p>Orb has <code>Scan()</code> and <code>Value()</code> methods already implemented for each type. This allows go's <code>Insert()</code> and <code>Scan()</code> functions to work with types other than primitives. Orb expects however to be using geometry represented in the well-known binary (WKB) format.</p>
  
  <p>The orb documentation shows that to accomplish this, you should simply wrap the field in the PostGIS functions <code>ST_AsBinary()</code> and <code>ST_GeomFromWKB()</code> for querying and inserting respectively. For example, with a table defined as:</p>
  
  <pre><code>_, err = db.Exec(`
          CREATE TABLE IF NOT EXISTS orbtest (
              id SERIAL PRIMARY KEY,
              name TEXT NOT NULL,
              geom geometry(POLYGON, 4326) NOT NULL
          );
      `)
  </code></pre>
  
  <p>You can just do:</p>
  
  <pre><code>rows, err := db.Query("SELECT id, name, ST_AsBinary(geom) FROM orbtest LIMIT 1")
  </code></pre>
  
  <p>And for insert (where p is an orb.Point):</p>
  
  <pre><code>db.Exec("INSERT INTO orbtest (id, name, geom) VALUES ($1, $2, ST_GeomFromWKB($3))", 1, "Test", wkb.Value(p))
  </code></pre>
  
  <p>Here's my issue: By using GORM, I don't have the luxury of being able to build those queries with those functions. GORM will automatically insert values into the database given a struct, and will scan in data into the whole hierarchy of the struct. Those <code>Scan()</code> and <code>Value()</code> methods are called behind the scenes, without my control.</p>
  
  <p>Trying to directly insert binary data into a geometry column won't work, and directly querying a geometry column will give the result in hex.</p>
  
  <p>I've tried multiple database approaches to solve this. I've attempted creating views that automatically call the needed functions on the geometry columns. This worked for querying, but not inserting.</p>
  
  <p>Is it possible to make some sort of trigger or rule that would automatically call the needed functions on the data coming in/out?</p>
  
  <p>I should also note that the library I'm working on works completely independent of the data and schemas, so I don't have the luxury of hard coding any sort of query. I could of course write a function that scans the entire data model, and generates queries from scratch, but I'd prefer if there was a better option.</p>
  
  <p>Does anyone know of a way of making this work in SQL? Being able to call functions on a column automatically by just querying the column itself?</p>
  
  <p>Any advice would be greatly appreciated.</p>