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.

Answer 1

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()
    ...
}