PostGis query returns invalid geometry error when performed with Go

Question

I tried to select the id from the steps table having their location within 1km from (x,y) some PostGis with Go using the pgx library.

I've tested the following code which returns an error: parse error - invalid geometry (SQLSTATE XX000)

func search(lat float32, lng float32) return (string, error){
    searchQuery   = "SELECT DISTINCT(id) FROM steps WHERE ST_Distance('SRID=4326;POINT($1 $2)'::geography, location) < 1000"

    // GetSession returns *pgxpool.Pool
    rows, err := postgres.GetSession().Query(context.Background(), searchQuery,
    lat,
    lng)
    if err != nil {
        // ERROR: parse error - invalid geometry (SQLSTATE XX000)
        return nil, err
    }
    defer rows.Close()
    ...
}

Then, I've just changed the query using ST_SetSRID & ST_MakePoint

searchQuery   = "SELECT DISTINCT(id) FROM steps WHERE ST_Distance(ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, location) < 1000"

... but ...

1) I still don't know why my fist version of the query

"SELECT DISTINCT(id) FROM steps WHERE ST_Distance('SRID=4326;POINT($1 $2)'::geography, location) < 1000"

returns a geometry error whereas it's working when I test it straight into pgadmin replacing $1 and $2 with random coordinate values like

"SELECT DISTINCT(id) FROM steps WHERE ST_Distance('SRID=4326;POINT(0.44 3.40)'::geography, location) < 1000"

2) I'm not sure if using ST_Distance with ST_SetSRID and ST_MakePoint is the most efficient way to check that a point is close to another one.

Can you try `ST_GeomFromText` instead? In regards to your 2nd question, you can read this http://blog.cleverelephant.ca/2021/05/indexes-and-queries.html - you should most likely be using st_dwithin and utilise a spatial index which you can build with your location values with `geography`, he explains that in the tail auf the post. — Timothy Dalton, Jun 03 '22 at 05:47
It's also working in pgadmin but Go returns `ERROR: syntax error at or near \"$2\" (SQLSTATE 42601)` for the request `SELECT DISTINCT(id) FROM steps WHERE ST_Distance(ST_GeomFromText(POINT($1 $2), 4326)::geography, location) < 1000`. The common thing between the 2 rejected request id the presence of the surrounding quotes ... — Big_Boulard, Jun 03 '22 at 07:13
Yes, it is expecting a string. Add quotes before, so 'POINT(...)' — Timothy Dalton, Jun 03 '22 at 07:42
sorry I mistyped the request it already has quotes indeed: `SELECT DISTINCT(id) FROM steps WHERE ST_Distance(ST_GeomFromText('POINT($1 $2)', 4326)::geography, location) < 1000` and the presence of quotes is the common denominator in the 2 rejected requests but don't know why ... — Big_Boulard, Jun 03 '22 at 07:44
Can you print the statement after the arguments have been inserted? — Timothy Dalton, Jun 03 '22 at 09:08
There's no option to print the request generated by the SQL driver, I can only simulate it performing a string replacement. So it gives me this: `SELECT DISTINCT(id) FROM steps WHERE ST_Distance(ST_GeomFromText('POINT(0.440000 3.400000)', 4326), location) < 1000` and it works as expected in pgadmin but still not using the Go Query func :/ — Big_Boulard, Jun 03 '22 at 09:23
I am not familiar with Go but this may help https://stackoverflow.com/questions/20928212/postgres-list-parameter-in-go-using-database-sql-and-pq - I do not think this is a PostGIS specific issue, it is how you are formatting the strings with your params lat and lon — Timothy Dalton, Jun 03 '22 at 09:33
`'SRID=4326;POINT($1 $2)'` is a string literal, postgres' ordinal parameter placeholders should NOT be in string literals. This is not a problem of Go. This is how postgres treats string literals. i.e. `'$1'` != `$1`, one is a plain string, the other a parameter placeholder that *postgres* (not Go) will replace with data sent by the client to the server. — mkopriva, Jun 03 '22 at 11:19
... if you want to combine string literals with parameter placeholders, use string concatenation, e.g. something like this: `'SRID=4326;POINT(' || $1::text || ' ' || $2::text || ')'`. — mkopriva, Jun 03 '22 at 11:20
On a side note, you are passing attributes with `lat` first, then `long`, but points in Postgis must be expressed as longitude first, then latitude. — JGH, Jun 03 '22 at 13:04
JFI using postgres string concat gives me a weird error ... `"SELECT DISTINCT(id) FROM steps WHERE ST_Distance('SRID=4326;POINT(' || $1::text || ' ' || $2::text || ')', location) < 1000"` `function st_distance(text, geometry) is not unique (SQLSTATE 42725)` — Big_Boulard, Jun 03 '22 at 13:57
@Big_Boulard it means that there's no `st_distance` function with the signature `(text, geometry)`, that means that you probably need to add **explicit** cast to the `text` argument to a type that `st_distance` can accept. — mkopriva, Jun 03 '22 at 14:15
@Big_Boulard you can see here that there are two st_distances https://postgis.net/docs/ST_Distance.html... the one you want has the signature `(geometry, geometry)`, so just modify your query like so: `ST_Distance(('SRID=4326;POINT(' || $1::text || ' ' || $2::text || ')')::geometry, location)` -- note how the string literal with its concatenations is wrapped in extra parentheses, this is just to enforce the right order of evaluation, i.e. to enforce the cast `::geometry` is applied to the text *after* it is concatenated. — mkopriva, Jun 03 '22 at 14:19
Thx @mkopriva it was a bit though for me on this one. So just for the record, here is the version using the recommended ST_DWithin (because it leverage a spatial index while ST_Distance doesn't): `SELECT DISTINCT(id) FROM steps WHERE ST_DWithin(('SRID=4326;POINT(' || 0.440000::text || ' ' || 3.400000::text || ')')::geometry, location, 1000);` — Big_Boulard, Jun 03 '22 at 14:37
@Big_Boulard I hope you meant `$1` and `$2` instead of `0.440000` and `3.400000` because otherwise this whole comment section is pointless :). — mkopriva, Jun 03 '22 at 14:49
@Big_Boulard feel free to answer your own question, it will help others, I think it's a common pitfall for all different kind of languages — Timothy Dalton, Jun 03 '22 at 17:43
@mkopriva yes I meant $1 and $2 sorry for that I will write the answer tomorrow you can count on me. Thank you all guys — Big_Boulard, Jun 03 '22 at 21:19

score 1 · Accepted Answer · answered Jun 04 '22 at 07:56

Query Issue

SELECT DISTINCT(id) FROM steps WHERE ST_Distance('SRID=4326;POINT($1 $2)'::geography, location) < 1000

note that $1 is lng and $2 should be lat

Why is-it happening?

'SRID=4326;POINT($1 $2)' is a string literal, postgres' ordinal parameter placeholders should NOT be in string literals. This is not a problem of Go. This is how postgres treats string literals. i.e. '$1' != $1, one is a plain string, the other a parameter placeholder that postgres (not Go) will replace with data sent by the client to the server.

Solution

.. if you want to combine string literals with parameter placeholders, use string concatenation:

ST_Distance(('SRID=4326;POINT(' || $1::text || ' ' || $2::text || ')')::geometry, location)

Note how the string literal with its concatenations is wrapped in extra parentheses, this is just to enforce the right order of evaluation, i.e. to enforce the cast ::geometry applied to the text after it is concatenated.

Optimization

You should most likely be using st_dwithin and utilise a spatial index which you can build with your location values with geography, he explains that in the tail of the post. See blog.cleverelephant.ca/2021/05/indexes-and-queries.html

Fixed SQL Query

SELECT DISTINCT(id) FROM steps WHERE ST_DWithin(('SRID=4326;POINT(' || $1::text || ' ' || $2::text || ')')::geometry, location, 1000);

Fixed Go function

func search(lat float32, lng float32) return (string, error){
    searchQuery   = "SELECT DISTINCT(id) FROM steps WHERE ST_DWithin(('SRID=4326;POINT(' || $1::text || ' ' || $2::text || ')')::geometry, location, 1000);"

    // GetSession returns *pgxpool.Pool
    rows, err := postgres.GetSession().Query(context.Background(), searchQuery,
    lng,
    lat)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    ...
}