postgresql 将 POINT 插入 postgres 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41494211/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert POINT into postgres database
提问by Deepak Bandi
I need to insert/update a point column type in postgres database.
我需要在 postgres 数据库中插入/更新点列类型。
I'm using node-postgres
我正在使用 node-postgres
The script generated using POSTGRES admin panel shows the update query as
使用 POSTGRES 管理面板生成的脚本将更新查询显示为
UPDATE public.places SET id=?, user_id=?, business_name=?, alternate_name=?, primary_category=?, categories=?, description=?, address=?, city=?, state=?, country=?, zip=?, point WHERE <condition>;
UPDATE public.places SET id=?, user_id=?, business_name=?, alternate_name=?, primary_category=?, categories=?, description=?, address=?, city=?, state=?, country=?, zip=?, point WHERE <condition>;
How do I achieve point from latitude and longitude?
我如何从纬度和经度获得点？
I have seen couple of answers using POSTGIS, but could not get it working.
我已经使用 POSTGIS 看到了几个答案，但无法使其正常工作。
In the documentation of POSTGRES (https://www.postgresql.org/docs/9.2/static/xfunc-sql.html) it is mentioned we can use point '(2,1)', but this does not work with pg query.
在 POSTGRES ( https://www.postgresql.org/docs/9.2/static/xfunc-sql.html)的文档中提到我们可以使用point '(2,1)'，但这不适用于 pg 查询。
What I have now :
我现在所拥有的：
var config = {
user: 'postgres',
database: 'PGDATABASE',
password: 'PGPASSWORD!',
host: 'localhost',
port: 5432,
max: 10,
idleTimeoutMillis: 30000
};
And the update part :
和更新部分：
app.post('/updatePlaces', function(req, res, next) {
console.log("Update");
console.log(req.body.places);
pool.query('UPDATE places SET address = , alternate_name = , business_name = , categories = , city = , country = , description = , point = , primary_category = , state = , zip = ', [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]);
});
});
Tried many different ways for passing point :
尝试了许多不同的传球方式：
- (req.body.places.point.x, req.body.places.point.y)
- point(req.body.places.point.x, req.body.places.point.y)
- point '(2,1)'
- (req.body.places.point.x, req.body.places.point.y)
- 点（req.body.places.point.x，req.body.places.point.y）
- 点'(2,1)'
All the above throws error. Do I need to use POSTGIS?
以上所有抛出错误。我需要使用 POSTGIS 吗？
回答by Deepak Bandi
After couple of combinations, found out this works.!!
经过几次组合后，发现这是有效的。！！
( '(' + req.body.places.point.x + ',' + req.body.places.point.y +')' )
( '(' + req.body.places.point.x + ',' + req.body.places.point.y +')' )
Posting as answer if someone is trying to do this just using node-postgres.
如果有人试图仅使用node-postgres.
So you can use single-quoted points: insert into x values ( '(1,2)' );
所以你可以使用单引号： insert into x values ( '(1,2)' );
But using insert into x values (point(1,2));in the query does not work.
但是insert into x values (point(1,2));在查询中使用不起作用。
回答by joanolo
This works if you write SQL "directly":
如果您“直接”编写 SQL，这将起作用：
CREATE TEMP TABLE x(p point) ;
INSERT INTO x VALUES ('(1,2)');
INSERT INTO x VALUES (point(3, 4));
SELECT * FROM x ;
Results
结果
(1,2)
(3,4)
回答by vitaly-t
If you are using pg-promise, then custom types can be formatted automatically, see Custom Type Formatting.
如果您使用pg-promise，则自定义类型可以自动格式化，请参阅自定义类型格式。
You can introduce your own type like this:
你可以像这样介绍你自己的类型：
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 + ')';
};
}
At some point you would create your objects:
在某些时候你会创建你的对象：
var p = new Point(11, 22);
And then you can use such variables as regular types:
然后你可以使用这样的变量作为常规类型：
db.query('INSERT INTO places(place) VALUES(ST_SetSRID(, 4326))', [p]);
See also: Geometry Constructors.
另请参阅：几何构造函数。
回答by Chris
I recently came across a similar problem using node-postgreswhen inserting into a postgisdatabase with a geography (point) column. My solution was to use:
我最近在使用地理（点）列node-postgres插入postgis数据库时遇到了类似的问题。我的解决方案是使用：
pool.query("INSERT INTO table (name, geography) VALUES (, ST_SetSRID(ST_POINT(, ), 4326))",
[req.body.name, req.body.lat, req.body.lng ]);
