Spring JdbcTemplate更新Postgis地理专栏 [英] Spring JdbcTemplate update Postgis geography column

问题描述

我正在尝试使用下面的代码用Lon和Lat更新Postgis地理专栏

I'm trying to update the Postgis geography column with Lon and Lat with the code below

public void updateGeoLocation(String lat, String lon) {
    template.update(
            "UPDATE property set geo = ST_GeomFromText('POINT(? ?)', 4326) where id = 'b15e7a7e-3b27-4a2f-b312-33ebbed594b5'",
            lon, lat);
}

但是我得到了以下异常

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [UPDATE属性集geo = ST_GeomFromText('POINT(??)'，4326)其中id = 'b15e7a7e-3b27-4a2f-b312-33ebbed594b5']；列索引超出 范围:1，列数:0.嵌套的异常是 org.postgresql.util.PSQLException:列索引超出范围: 1，列数:0.

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [UPDATE property set geo = ST_GeomFromText('POINT(? ?)', 4326) where id = 'b15e7a7e-3b27-4a2f-b312-33ebbed594b5']; The column index is out of range: 1, number of columns: 0.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

我手动尝试了以下查询，它可以工作，但是在使其与JdbcTemplate一起工作时遇到问题

I tried the following query manually and it works, but I am having issues getting it to work with JdbcTemplate

update property set geo = ST_GeomFromText('POINT(-71.060316 48.432044)', 4326) where id = 'b15e7a7e-3b27-4a2f-b312-33ebbed594b5'

如何使用JdbcTemplate更新/插入Postgis地理专栏?

How can I update/insert lon lat for the Postgis geography column using JdbcTemplate?

推荐答案

我应用了ST_GeogFromText函数，并像这样使用它:

I applied function ST_GeogFromText, and used it like this:

"ST_GeogFromText('SRID=4326;POINT(" + longitude + " " + latitude + ")')"

请注意ST_GeogFromText和ST_GeomFromText之间的区别

Notice the difference between ST_GeogFromText and ST_GeomFromText

这篇关于Spring JdbcTemplate更新Postgis地理专栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！