如何检查多边形包含点Sequelize和postgres

Question

我使用的是nodejs，orm是sequelize，数据库是postgresql。我想得到多边形从另一个表和车辆的位置表这是代码：

const geoRegion = await models.GeoRegion.find({
where: {
id: id,
},
});
const scooters = await models.Vehicle.findAll({
where: {
$and: models.sequelize.where(models.sequelize.fn('ST_Intersects', geoRegion.polygon, models.sequelize.fn('ST_SetSRID', models.sequelize.fn('ST_MakePoint', models.sequelize.col('lastReportedLocation')), '4326')), true),
},
plain: true,
});

geoRegion有字段多边形，我想检查多边形内的车辆这是错误：

SELECT * FROM "Vehicles" AS "Vehicle" WHERE ST_Intersects("lastReportedLocation", "type" = 'Polygon' AND "coordinates" IN (ARRAY[ARRAY[105.293,21.145],...)
"name": "SequelizeDatabaseError",
"error": "column "type" does not exist"

Answer 1

ST_Intercepts需要两个几何图形/地理位置作为参数。所以，我想，你的查询应该是这样的：

select v.* 
from Vehicles v
join geoRegion r
on st_intersects(v.lastReportedLocation, r.polygon)

假设Vehicles表中的字段"lastReportedLocation"和geoRegion表中的"polygon"都包含有效的几何图形，则应该为您提供感兴趣区域内的车辆。