postgresql - PostGIS ST_Contains 返回数据失败

Question

我在使用 PostGIS 2.1.7 和 PostgreSQL 9.4 时遇到了一个奇怪的情况。

我正在尝试将位于加利福尼亚州(纬度:36.671112，经度:-121.662636，正如我们在这张 map 上看到的:https://www.google.com/maps/d/viewer?mid=zHggIvE-1G0A.kW1YFh5cNOJU&usp=sharing)内的一个点与以下查询相匹配:

select name
from zones
where ST_Contains(area, ST_SetSRID(ST_Point(-121.662636, 36.671112), 4326))

其中 zones 定义如下:

create table zones (
  name text,
  kml text,
  area geometry
)

insert into zones (name, kml)
values (
  'California',
  '<Polygon><outerBoundaryIs><LinearRing><coordinates>-118.603375,33.478098,0 -118.60403,33.47654,0 -118.601185,33.469853,0 -118.593969,33.467198,0 -118.575901,33.448261,0 -118.570927,33.439351,0 -118.563442,33.434381,0 -118.558715,33.433419,0 -118.53738,33.434608,0 -118.52323,33.430733,0 -118.516267,33.425075,0 -118.515914,33.422417,0 -118.503952,33.424234,0 -118.48875,33.419826,0 -118.484949,33.412131,0 -118.478465,33.38632,0 -118.482609,33.369914,0 -118.48877,33.356649,0 -118.481886,33.344123,0 -118.465368,33.326056,0 -118.456309,33.32182,0 -118.440047,33.318638,0 -118.402941,33.320901,0 -118.374768,33.320065,0 -118.360332,33.31533,0 -118.343249,33.305234,0 -118.325244,33.299075,0 -118.316787,33.301137,0 -118.305084,33.310323,0 -118.303174,33.320264,0 -118.310213,33.335795,0 -118.316083,33.342928,0 -118.32446,33.348782,0 -118.365094,33.388374,0 -118.368301,33.40711,0 -118.370323,33.409285,0 -118.382037,33.409883,0 -118.423576,33.427258,0 -118.445812,33.428907,0 -118.477646,33.448392,0 -118.48557,33.446213,0 -118.499669,33.447879,0 -118.500212,33.449592,0 -118.530702,33.468071,0 -118.54453,33.474119,0 -118.585936,33.473819,0 -118.598783,33.477939,0 -118.603375,33.478098,0 </coordinates></LinearRing></outerBoundaryIs></Polygon>'
)

update zones
set area = ST_GeomFromKML(kml)

这是返回的内容:

 name 
------
(0 row)

虽然它应该返回这个:

   name 
-----------
California
(1 row)

您是否发现查询或表创建脚本有任何问题？

(我不得不说这不是与点本身相关的边缘情况，因为我遇到了超过 60,000 个不同点的相同问题，所有点都位于加利福尼亚州内)

Answer 1

它没有返回匹配项，因为该点不在该区域内。

试试这个查询:

select st_xmax(area), st_ymax(area), st_xmin(area), st_ymin(area) from zones;

您会看到该点位于最大点和最小点之外:

"st_xmax","st_ymax","st_xmin","st_ymin"
-118.303174,33.478098,-118.60403,33.299075

您的区域似乎是洛杉矶海岸外的一个岛屿(该岛上有一个名为“Avalon”的城镇)，而不是整个州。

(给你的小提示。你可以使用像 QGIS 这样的 GIS 来可视化你的图层 - 你很快就会发现问题。这就是我所做的!)。