由网友(发尾也羞涩)分享简介:我有一个表samplecol,其中包含(一个示例):vessel_hash | status | station | speed | latitude | longitude | course | heading | timestamp |...
我有一个表samplecol,其中包含(一个示例):
vessel_hash | status | station | speed | latitude | longitude | course | heading | timestamp | the_geom
--------------+--------+---------+-------+-------------+-------------+--------+---------+--------------------------+----------------------------------------------------
103079215239 | 99 | 841 | 5 | -5.41844510 | 36.12160900 | 314 | 511 | 2016-06-12T06:31:04.000Z | 0101000020E61000001BF33AE2900F424090AF4EDF7CAC15C0
103079215239 | 99 | 3008 | 0 | -5.41778710 | 36.12144900 | 117 | 511 | 2016-06-12T06:43:27.000Z | 0101000020E6100000E2900DA48B0F424042C3AC61D0AB15C0
103079215239 | 99 | 841 | 17 | -5.42236900 | 36.12356900 | 259 | 511 | 2016-06-12T06:50:27.000Z | 0101000020E610000054E6E61BD10F42407C60C77F81B015C0
103079215239 | 99 | 841 | 17 | -5.41781710 | 36.12147900 | 230 | 511 | 2016-06-12T06:27:03.000Z | 0101000020E61000004D13B69F8C0F424097D6F03ED8AB15C0
103079215239 | 99 | 841 | 61 | -5.42201900 | 36.13256100 | 157 | 511 | 2016-06-12T06:08:04.000Z | 0101000020E6100000CFDC43C2F71042409929ADBF25B015C0
103079215239 | 99 | 841 | 9 | -5.41834020 | 36.12225000 | 359 | 511 | 2016-06-12T06:33:03.000Z | 0101000020E6100000CFF753E3A50F42408D68965F61AC15C0
我尝试使用:
获取多边形内的所有点poisInpolygon = """SELECT col.vessel_hash,col.longitude,col.latitude,
ST_Contains(ST_GeomFromEWKT('SRID=4326; POLYGON((-15.0292969 47.6357836,-15.2050781 47.5172007,-16.2597656 29.3821751, 35.0683594 26.1159859, 38.0566406 47.6357836,-15.0292969 47.6357836))'),
ST_GeomFromEWKT(col.the_geom)) FROM samplecol As col;"""
输出为:
(103079215291L, Decimal('40.87123100'), Decimal('29.24107000'), False)
(103079215291L, Decimal('40.86702000'), Decimal('29.23967000'), False)
(103079215291L, Decimal('40.87208200'), Decimal('29.22113000'), False)
(103079215291L, Decimal('40.86973200'), Decimal('29.23963000'), False)
(103079215291L, Decimal('40.87770800'), Decimal('29.20229900'), False)
我不知道结果中什么是假的。这是正确的方式还是我做错了什么?
此代码还使用the_geom
?
推荐答案
查询返回false
,因为样例中的所有点都在给定的多边形外。这里概述了您的点(在坦桑尼亚东北部的某个地方)和多边形(南欧和北非):
为了测试您的查询,我在Málaga
中的某个位置添加了另一个点,该点位于您的多边形内部,它恰如其分地返回了true
(INSERT语句中的最后一个几何图形为EWKT)。以下是脚本:
CREATE TEMPORARY TABLE t (the_geom GEOMETRY);
INSERT INTO t VALUES ('0101000020E61000001BF33AE2900F424090AF4EDF7CAC15C0'),
('0101000020E6100000E2900DA48B0F424042C3AC61D0AB15C0'),
('0101000020E610000054E6E61BD10F42407C60C77F81B015C0'),
('0101000020E61000004D13B69F8C0F424097D6F03ED8AB15C0'),
('0101000020E6100000CFDC43C2F71042409929ADBF25B015C0'),
('0101000020E6100000CFF753E3A50F42408D68965F61AC15C0'),
(ST_GeomFromEWKT('SRID=4326;POINT(-4.4427 36.7233)'));
以下是您的查询:
db=# SELECT
ST_Contains(ST_GeomFromEWKT('SRID=4326; POLYGON((-15.0292969 47.6357836,-15.2050781 47.5172007,-16.2597656 29.3821751, 35.0683594 26.1159859, 38.0566406 47.6357836,-15.0292969 47.6357836))'),
ST_GeomFromEWKT(col.the_geom))
FROM t As col;
st_contains
-------------
f
f
f
f
f
f
t
(7 Zeilen)
相关推荐
最新文章