PostGIS查询由于转义字符串而失败? [英] PostGIS query failing due to escape string?

问题描述

我有这个Postgres/PostGIS查询:

I have this Postgres/PostGIS query:

UPDATE raw.geocoding
SET the_geom = ST_Transform(ST_GeomFromText('POINT(((E'-96.6864379495382')::float8) ((E'32.792527154088')::float8))', 4326),3081)
WHERE id=((10793455)::int4)

运行它时，出现此错误:

When I run it, I get this error:

ERROR:  syntax error at or near "')::float8) ((E'"
LINE 2: ...sform(ST_GeomFromText('POINT(((E'-96.6864379495382')::float8...
                                                             ^

********** Error **********

ERROR: syntax error at or near "')::float8) ((E'"
SQL state: 42601
Character: 94

我挠头，因为PostGIS的转义数据没有问题(例如， )，并且查询是根据以下参数化查询从npgsql生成的:

I'm scratching my head because PostGIS doesn't have a problem with escaped data (for example), and the query was generated from npgsql based on this parameterized query:

UPDATE raw.geocoding
SET the_geom = ST_Transform(ST_GeomFromText('POINT(:longitude :latitude)', 4326),3081)
WHERE id=:id

我正在运行Postgres 9.1.5和PostGIS 2.0.1.

I am running Postgres 9.1.5 and PostGIS 2.0.1.

推荐答案

该错误是由字符串中的未转义的单引号引起的.标准方法是将它们加倍:

The error results from unescaped single quotes in the string. The standard way is to double them:

UPDATE raw.geocoding
SET    the_geom = ST_Transform(ST_GeomFromText(
               'POINT(((E''-96.6864379495382'')::float8)
                     ((E''32.792527154088'')::float8))', 4326),3081)
WHERE  id=((10793455)::int4)

这可以修复字符串文字，但是您会遇到更多错误.
就像@Paul在评论中暗示的那样， ST_GeomFromText() 期望

This fixes the string literal, but you have more errors.
Like @Paul hinted in a comment, ST_GeomFromText() expects the geometry WKT POINT(0 0). The explicit cast to float8 makes it look like you are trying to enter the Postgres function point() (had me confused at first). Simplify to:

UPDATE raw.geocoding
SET    the_geom = ST_Transform(ST_GeomFromText(
                  $$POINT(96.6864379495382 32.792527154088)$$, 4326), 3081)
WHERE  id = 10793455

还请注意，我如何使用美元引号是为了避免必须完全对单引号进行转义.修复语法后，由于字符串文字中没有任何单引号，因此不妨再次使用单引号.您的参数化查询:

Note also, how I use dollar quoting in the second example to avoid having to escape single quotes altogether. As there aren't any single quotes left in your string literal after fixing the syntax, you might as well use single quotes again. Your parametrized query:

UPDATE raw.geocoding
SET    the_geom = ST_Transform(ST_GeomFromText(
                  $$POINT(:longitude :latitude)$$::geometry, 4326), 3081)
WHERE  id = :id

您可以在geometry中添加类型转换，以使其清晰明了，就像@Paul在其评论中建议的那样.但是它也无需显式强制转换即可工作.

You can add a cast to geometry to make it clear, like @Paul advises in his comment. But it works without explicit cast, too.

这篇关于PostGIS查询由于转义字符串而失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！