英文:
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_SetSRID和ST_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 = "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.
答案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('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()
...
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论