当使用Go执行PostGIS查询时,返回无效几何错误。

huangapple go评论83阅读模式
英文:

PostGis query returns invalid geometry error when performed with Go

问题

我尝试使用Go语言和pgx库从steps表中选择具有距离(x,y)一公里以内的位置的id。我测试了以下代码,但返回了错误: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返回*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()
    ...
}

然后,我只是改变了查询,使用了ST_SetSRIDST_MakePoint

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

但是...

1) 我仍然不知道为什么我的第一个版本的查询

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

返回一个几何错误,而当我在pgadmin中直接测试它时,将$1和$2替换为随机坐标值时,它是有效的,例如:

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

2) 我不确定使用ST_Distance、ST_SetSRID和ST_MakePoint是否是检查一个点是否靠近另一个点的最有效方法。

英文:

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   = &quot;SELECT DISTINCT(id) FROM steps WHERE ST_Distance(&#39;SRID=4326;POINT($1 $2)&#39;::geography, location) &lt; 1000&quot;

	// 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   = &quot;SELECT DISTINCT(id) FROM steps WHERE ST_Distance(ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography, location) &lt; 1000&quot;

... but ...

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

&quot;SELECT DISTINCT(id) FROM steps WHERE ST_Distance(&#39;SRID=4326;POINT($1 $2)&#39;::geography, location) &lt; 1000&quot;

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

&quot;SELECT DISTINCT(id) FROM steps WHERE ST_Distance(&#39;SRID=4326;POINT(0.44 3.40)&#39;::geography, location) &lt; 1000&quot;

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.

答案1

得分: 1

查询问题

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

注意,$1 是经度,$2 是纬度。

为什么会发生这种情况?

'SRID=4326;POINT($1 $2)' 是一个字符串字面量postgres的序数参数占位符不应该出现在字符串字面量中。这不是Go的问题,这是postgres处理字符串字面量的方式。即 '$1' != $1,一个是普通字符串,另一个是参数占位符,由postgres(而不是Go)用客户端发送到服务器的数据替换

解决方案

... 如果你想将字符串字面量与参数占位符结合使用,请使用字符串连接:

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

注意,字符串字面量及其连接部分被额外的括号包裹起来,这只是为了强制执行正确的评估顺序,即在连接后应用 ::geometry 强制转换为文本。

优化

你应该使用 st_dwithin 并利用空间索引,你可以使用地理位置值构建空间索引,他在文章的末尾解释了这一点。参见 blog.cleverelephant.ca/2021/05/indexes-and-queries.html

修正后的 SQL 查询

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

修正后的 Go 函数

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 返回 *pgxpool.Pool
    rows, err := postgres.GetSession().Query(context.Background(), searchQuery,
    lng,
    lat)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    ...
}
英文:

Query Issue

SELECT DISTINCT(id) FROM steps WHERE ST_Distance(&#39;SRID=4326;POINT($1 $2)&#39;::geography, location) &lt; 1000

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

Why is-it happening?

&#39;SRID=4326;POINT($1 $2)&#39; 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. &#39;$1&#39; != $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((&#39;SRID=4326;POINT(&#39; || $1::text || &#39; &#39; || $2::text || &#39;)&#39;)::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((&#39;SRID=4326;POINT(&#39; || $1::text || &#39; &#39; || $2::text || &#39;)&#39;)::geometry, location, 1000);

Fixed Go function

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

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

huangapple
  • 本文由 发表于 2022年6月3日 05:11:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/72482399.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定