Npgsql参数化查询输出PostGIS的不兼容 [英] Npgsql parameterized query output incompatible with PostGIS

问题描述

我在这Npgsqlcommand参数化查询：

I have this parameterized query in an Npgsqlcommand:

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

：longutide 和：纬度为双击和<$ C $ 。C> ID 为 INT

这是实际运行对数据库的查询看起来是这样的：

The query that is actually run against the DB looks like this:

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)

感谢来自欧文Brandstetter修改的这里，这是明显的是，查询需要简化与PostGIS的工作。他建议这样的：

Thanks to help from Erwin Brandstetter here, it's apparent that the query needs to be simplified to work with PostGIS. He suggested this:

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

我想我可以用一个动态查询，在那里我手动每次我运行它时更新查询创建这个，但有一种方法，使一个Npgsql参数化查询这项工作？

I guess I could create this with a dynamic query, where I manually update the query every time I run it, but is there a way to make this work with a Npgsql parameterized query?

推荐答案

我不跟 npgsql 的专家，但我认为你的参数化查询可以工作是这样的：

I am not an expert with npgsql, but I think your parameterized query could work like this:

UPDATE raw.geocoding
SET    the_geom = ST_Transform(ST_GeomFromText(:mygeom, 4326), 3081)
WHERE  id = :id

和 mygeom 将持有该字符串：

POINT(96.6864379495382 32.792527154088)

..从其他变量预组装。会导致这样的查询：

.. pre-assembled from your other variables. Would result in a query like this:

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

这应该工作。

如果你有麻烦组装字符串（如您的评论证明），还有一个更优雅的方式。按照暗示来自@保罗在我以前的答案 - PostGIS的提供了一个专用功能：

If you have trouble assembling the string (like your comment demonstrates), there is a more elegant way. As per hint from @Paul on my previous answer - PostGIS provides a dedicated function for the purpose:

ST_MakePoint(double precision x, double precision y)

的>详细信息。

Details in the manual. With this, we finally arrive at:

UPDATE raw.geocoding
SET    the_geom = ST_Transform(ST_SetSRID(
               ST_MakePoint(:longitude, :latitude), 4326), 3081)
WHERE  id = :id

请注意逗号。将它终于现在的工作？结果
^{如果没有，只是用大锤战胜它。 GRML}

Note the comma. Will it finally work now?
^{If not, just beat it with a sledgehammer. Grml.}

它确实 - 用的 ST_SetSRID（） 现在，而不是 ST_GeomFromText（）。见注释。

It does - with ST_SetSRID() now instead of ST_GeomFromText(). See comment.

这篇关于Npgsql参数化查询输出PostGIS的不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！