postgresql 如何在 Sequelize ORM 中插入 PostGIS GEOMETRY 点？

How to insert a PostGIS GEOMETRY Point in Sequelize ORM?

提问by Muhamed Krli?

I am trying to insert a row in a table that has a geometry column in Sequelize.js ORM. I have latitude, longitude and altitude and need to convert it to a point first so I can Insert it as a geometry.

我正在尝试在 Sequelize.js ORM 中具有几何列的表中插入一行。我有纬度、经度和高度，需要先将其转换为一个点，以便我可以将其作为几何体插入。

The PostGIS stored procedure that does the converting is

进行转换的 PostGIS 存储过程是

ST_MakePoint( longitude, latitude, altitude ) 

To Insert a row I am using the sequelize model.create function

要插入一行，我正在使用 sequelize model.create 函数

models.Data.create({    
  location: "ST_MakePoint("+request.params.lon+", "+request.params.lat+", "+request.params.alt+")", // PSUEDO code, How can I call this function?
  speed: request.params.spd,
  azimuth: request.params.azi,
  accuracy: request.params.acc
});

Now what I want to do Is make the field locationhave the returned result of "ST_MakePoint("+request.params.lon+", "+request.params.lat+", "+request.params.alt+")"when I insert the row.

现在我想做的是使该字段location具有"ST_MakePoint("+request.params.lon+", "+request.params.lat+", "+request.params.alt+")"插入行时的返回结果。

How can I do that?

我怎样才能做到这一点？

回答by Evan Siroky

Expanding on l0oky's answer, the integration testhas a lot of good clues on how to use the json with varying types of Geometry. Basically, it appears that sequelize will stringify the provided geometry object assuming that it is valid GeoJSON and pipe that into the PostGIS function ST_GeomFromGeoJSON. Therefore, one can just follow the GeoJSON specfor geometry objects.

扩展 l0oky 的答案，集成测试有很多关于如何使用具有不同几何类型的 json 的好线索。基本上，似乎 sequelize 将字符串化提供的几何对象，假设它是有效的 GeoJSON，并将其通过管道传输到 PostGIS 函数 ST_GeomFromGeoJSON。因此，可以只遵循几何对象的GeoJSON 规范。

Points:

积分：

var point = { type: 'Point', coordinates: [39.807222,-76.984722]};

User.create({username: 'username', geometry: point }).then(function(newUser) {
...
});

Linestrings:

线串：

var line = { type: 'LineString', 'coordinates': [ [100.0, 0.0], [101.0, 1.0] ] };

User.create({username: 'username', geometry: line }).then(function(newUser) {
...
});

Polygons:

多边形：

var polygon = { type: 'Polygon', coordinates: [
             [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
               [100.0, 1.0], [100.0, 0.0] ]
             ]};

User.create({username: 'username', geometry: polygon }).then(function(newUser) {
...
});

Setting a custom SRID:

设置自定义 SRID：

var point = { 
  type: 'Point', 
  coordinates: [39.807222,-76.984722],
  crs: { type: 'name', properties: { name: 'EPSG:4326'} }
};

User.create({username: 'username', geometry: point }).then(function(newUser) {
...
});

回答by Muhamed Krli?

After a bit of researching I found that Sequelize 3.5.1 ( is supporting GEOMETRY ) had a testthat inserts a Point.

经过一番研究，我发现 Sequelize 3.5.1（支持 GEOMETRY）有一个测试，插入了Point.

var point = { type: 'Point', coordinates: [39.807222,-76.984722] }; 
return User.create({ username: 'user', email: ['[email protected]'], location: point})

Where locationis a GEOMETRY field. This way I don't need to call ST_MakePointmanually, sequelize takes care of that.

locationGEOMETRY 字段在哪里。这样我就不需要ST_MakePoint手动调用了，sequelize 可以解决这个问题。