support postgis geometric datatypes

[ToddG]

I'd like to use several postgis geometric datatypes such as:

• st_point
• st_polygon

I don't know how to solve this issue, but I suspect it will involve pgo + pog + squirrel...

Notes

postgis geometryoid defined here

https://github.com/postgis/postgis/blob/2ecfaefce1e4730c1eab3733469750c9546f6e8a/libpgcommon/lwgeom_pg.h#L35C1-L44C15

pgo uses types defined in postgres

https://github.com/ToddG/pgo/blob/ed334496fb784d3b7f5780863a4ff353461d2ba3/src/pgo_internal.hrl#L20-L22

% from pg_type.h
-define(BOOLOID, 16).
-define(BYTEAOID, 17).

postgres

However, I don't see where BOOLOID (for example) is defined
https://github.com/postgres/postgres/blob/master/src/include/catalog/pg_type.h

PostGIS Notes

EPSG:4326

https://epsg.io/4326

WGS 84 -- WGS84 - World Geodetic System 1984, used in GPS

PostGIS Links

• https://hub.docker.com/r/postgis/postgis
• https://postgis.gishub.org/chapters/geometries.html
• https://bostongis.com/?content_name=postgis_tut01
• https://www.postgis.net/workshops/postgis-intro/

PostgreSQL Links

• https://hub.docker.com/_/postgres/

PostGIS examples

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

SELECT ST_AsGeoJSON(geom) FROM geometries;

{"type":"Point","coordinates":[0,0]}
{"type":"LineString","coordinates":[[0,0],[1,1],[2,1],[2,2]]}
{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}
{"type":"Polygon","coordinates":[[[0,0],[10,0],[10,10],[0,10],[0,0]],[[1,1],[1,2],[2,2],[2,1],[1,1]]]}
{"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[2,0]},{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}]}
{"type":"Point","coordinates":[0.1,0.2]}

[ToddG]

For now, my workaround is to use squirrel to generate stuff before I add the geometry fields. Then I manually edit the generated files. I also do this for nullable fields.

Below is an example showing how to use the postgis functions to convert to/from text to make the client code easier to generate. In my case, I'll then convert from text geometry to an actual Point or whatever. This is just a quick hack to unblock me. Ideally, the generated code would use Point/Polygon/etc. instead of using pog.text as the intermediary.

• ST_GeomFromText
• ST_AsTex

---

cigogne migration

20250913195640-things.sql

--- migration:up
CREATE TABLE IF NOT EXISTS things (
    id UUID PRIMARY KEY,
    coordinates GEOMETRY NOT NULL
);


--- migration:down
DROP TABLE things;

--- migration:end

insert

pub fn insert_thing(
  db: pog.Connection,
  arg_1: Uuid,
  arg_2: String,
  arg_3: Int,  // geom type, 4326 in many cases
) -> Result(pog.Returned(Nil), pog.QueryError) {
  let decoder = decode.map(decode.dynamic, fn(_) { Nil })

"insert into things(id, coordinates) values ($1, ST_GeomFromText($2, $3));"
  |> pog.query
  |> pog.parameter(pog.text(uuid.to_string(arg_1)))
  |> pog.parameter(pog.text(arg_2))
  |> pog.parameter(pog.int(arg_3))
  |> pog.returning(decoder)
  |> pog.execute(db)
}

list

pub type ListThingRow {
  ListThingRow(
    id: Uuid,
    coordinates: String,
  )
}

pub fn list_things(
  db: pog.Connection,
) -> Result(pog.Returned(ListThingRow), pog.QueryError) {
  let decoder = {
    use id <- decode.field(0, uuid_decoder())
    use coordinates <- decode.field(1, decode.string)
    decode.success(ListThingRow(
      id:,
      coordinates:,
    ))
  }
  "select id, ST_AsText(coordinates) from things;"
  |> pog.query
  |> pog.returning(decoder)
  |> pog.execute(db)
}