Example using PostGis geometry

[tamis-laan]

I just started using sqlc and I need to combine it using PostGis, specifically to encode GPS coordinates.

I have the following database schema:

CREATE TABLE IF NOT EXISTS reports (
	description TEXT NOT NULL,
	location geometry(Point, 4326) NOT NULL
);

I think I should do the following:

-- name: CreateReport :one
INSERT INTO reports 
	( description, location)
VALUES
	($1, $2)
RETURNING description, location;

Here is my sqlc.yaml config:

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "./migrations"
    gen:
      go:
        package: "database"
        sql_package: "pgx/v5"
        out: "./src/database"
        emit_json_tags: true
        overrides:
          - db_type: "geometry"
            go_type: "github.com/cridenour/go-postgis.Point"

If I execute the following:

report, err := db.CreateReport(context.Background(), database.CreateReportParams{
	Description: "description",
	Location: postgis.Point{
		X: 2.0,
		Y: 3.0,
	},
})

I get back the error: parse error - invalid geometry (SQLSTATE XX000)

How would I get this to work properly?

[andrewmbenton]

I created a sqlc playground to try this and as far as I can tell the generated code looks fine: https://play.sqlc.dev/p/2942befa870c33a0f1efa332a5f3e9578e657b28948e3bbab98362b9711f1a19

I don't have experience with the geometry data type but this looks like an error you'd get when trying to scan some "invalid" (or maybe just unexpected) data from the database into the postgis.Point struct.

Does the insert work if you change the sqlc annotation to -- name: CreateReport :exec and remove the RETURNING ... clause?

[kittyofheaven]

do you know how to fix the same problem but using "mysql" engine

version: "2"
sql:
  - engine: "mysql"
    queries: "databases/query.sql"
    schema: "databases/schema.sql"
    gen:
      go:
        package: "db"
        out: "src/db"
        overrides:
          - db_type: "geometry"
            go_type: "github.com/cridenour/go-postgis.Point"

[tamis-laan]

@andrewmbenton hey yes, it turned out I needed to use a postgis function to construct the point. However this creates a different problem with sqlc:

#2603

[vladimir-budjen]

@tamis-laan have you managed to fix this? If I use fmt.Sprintf("SRID=4326;POINT(%.8f %.8f)", X , Y) I still get interface {} is string, not []uint8