将POINT插入postgres数据库

Question

我需要在Postgres数据库中插入/更新一个点列类型。

我正在使用node-postgres

使用POSTGRES管理面板生成的脚本显示更新查询为

UPDATE public.places SET id=?, user_id=?, business_name=?, alternate_name=?, primary_category=?, categories=?, description=?, address=?, city=?, state=?, country=?, zip=?, point WHERE <condition>;

如何从纬度和经度获取point？

我看过一些使用POSTGIS的答案，但无法使其工作。

在POSTGRES的文档（https://www.postgresql.org/docs/9.2/static/xfunc-sql.html）中提到我们可以使用 point '(2,1)'，但是这在pg查询中不起作用。

现在我的问题是:

var config = {
  user: 'postgres',
  database: 'PGDATABASE',
  password: 'PGPASSWORD!',
  host: 'localhost',
  port: 5432,
  max: 10,
  idleTimeoutMillis: 30000
};

更新部分：

app.post('/updatePlaces', function(req, res, next) {
    console.log("Update");
    console.log(req.body.places);
    pool.query('UPDATE places SET address = $1, alternate_name = $2, business_name = $3, categories = $4, city = $5, country = $6, description = $7, point = $8, primary_category = $9, state = $10, zip = $11', [req.body.places.address, req.body.places.alternate_name, req.body.places.business_name, req.body.places.categories, req.body.places.city, req.body.places.country, req.body.places.description, (req.body.places.point.x, req.body.places.point.y), req.body.places.primary_category, req.body.places.state, req.body.places.zip], function(err, result) {
      if(err) {
          console.log(err);
          return err;
      }

      res.send(result.rows[0]);
    });
});

尝试了多种不同的方式来通过点：

1. (req.body.places.point.x, req.body.places.point.y)
2. point(req.body.places.point.x, req.body.places.point.y)
3. point '(2,1)'

以上所有方法都会抛出错误。我需要使用POSTGIS吗？

- Deepak Bandi

---

如果使用 pg-promise，它可以自动完成。如果您有兴趣，我可以添加一个带有示例的答案 ;) - vitaly-t

是的，请@vitaly-t - Deepak Bandi

Answer 1

如果您直接编写 SQL，则此方法有效：

CREATE TEMP TABLE x(p point) ;
INSERT INTO x VALUES ('(1,2)');
INSERT INTO x VALUES (point(3, 4));
SELECT * FROM x ;

结果

(1,2)
(3,4)

- joanolo

---

在X语言中提出的问题应该用X语言回答。 - jcollum

Answer 2

经过几次尝试，发现这个方法可行。!!

( '(' + req.body.places.point.x + ',' + req.body.places.point.y +')' )

如果有人尝试使用node-postgres实现此功能，可以将其作为答案发布。

因此，您可以使用单引号的点：insert into x values ( '(1,2)' );

但是，在查询中使用insert into x values (point(1,2));不起作用。

- Deepak Bandi

Answer 3

最近我在使用node-postgres插入一列包含地理位置（point）的postgis数据库时遇到了相似的问题。我的解决方法是使用以下代码：

pool.query("INSERT INTO table (name, geography) VALUES ($1, ST_SetSRID(ST_POINT($2, $3), 4326))",
      [req.body.name, req.body.lat, req.body.lng ]);

- Chris

Answer 4

当前版本(Postgres 12，pg 8)应该可以简单使用Postgres的POINT函数来设置点列的值。

示例：

export async function setPoint(client, x, y, id) {
    const sql = `UPDATE table_name SET my_point = POINT($1,$2) WHERE id = $3 RETURNING my_point`;
    const result = await client.query(sql, [x, y, id]);
    return result.rows[0];
}
await setPoint(client, 10, 20, 5);

结果:

{x: 10.0, y: 20.0}

- msg45f

Answer 5

如果您正在使用pg-promise，则自定义类型可以自动格式化，请参见自定义类型格式化。

您可以像这样引入自己的类型：

function Point(x, y) {
    this.x = x;
    this.y = y;

    // Custom Type Formatting:
    this._rawDBType = true; // to make the type return the string without escaping it;

    this.formatDBType = function () {
        return 'ST_MakePoint(' + this.x + ',' + this.y + ')';
    };
}

在某个时候，您会创建您的对象：

var p = new Point(11, 22);

然后您可以像使用常规类型一样使用这些变量：

db.query('INSERT INTO places(place) VALUES(ST_SetSRID($1, 4326))', [p]);

参见：几何构造函数。

- vitaly-t

---

谢谢@vitaly-t，我还有一个问题，我的数据库更多是面向位置的，我应该使用postGIS吗？ - Deepak Bandi

是的，这通常是一个好主意，因为PostGIS现在就像PostgreSQL的本地组件一样，提供了所有基于位置的搜索所需的智能搜索功能 ;) - vitaly-t

很好，谢谢。 :) - Deepak Bandi

嘿，最后那个链接失效了，你有最新版本吗？ - jcollum

@jcollum 链接已更新 ;) - vitaly-t

太好了，正是我需要的，谢谢。 - jcollum