使用 PostgreSQL 的多边形点 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1012504/
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
SQL query for point-in-polygon using PostgreSQL
提问by Assaf Lavie
I have the following simple table:
我有以下简单的表：
CREATE TABLE tbl_test
(
id serial NOT NULL,
poly polygon NOT NULL
)
WITH (OIDS=FALSE);
I then try to insert a row with a polygon:
然后我尝试用多边形插入一行：
insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))'))
And run into this error:
并遇到此错误：
column "poly" is of type polygon but expression is of type geometry
列“poly”是多边形类型，但表达式是几何类型
Which is lame. So my first questions is:
这是蹩脚的。所以我的第一个问题是：
- Do I really have to cast?
- 我真的必须投吗？
Anyway, after casting it works. And now I'm trying to do a simple ST_Contains query:
无论如何，在铸造之后它就起作用了。现在我正在尝试做一个简单的 ST_Contains 查询：
select id, poly from tbl_test where ST_Contains(poly, Point(GeomFromText('POINT(9 2)')))
Which gives the error:
这给出了错误：
ERROR: function st_contains(polygon, point) does not exist
LINE 1: select id, poly from tbl_test where ST_Contains(poly, Point(...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
What am I supposed to do?
我应该做些什么？
The following works:
以下工作：
select st_contains(st_geomfromtext('POLYGON((0 0, 10 10, 10 0, 0 0))'), st_geomfromtext('POINT(0 0)'))
But that's probably because both arguments are of type Geometry. The actual query against the table data doesn't work.
但这可能是因为这两个参数都是 Geometry 类型。针对表数据的实际查询不起作用。
Answer:
回答：
Doi! The problem was that the DB I created was not based on the postgis template DB (and therefor did not have the relevant functions and geometry column tables, etc.). May I just remark, in conclusion, that the way PostGIS requires you to add hundreds of functions, rows and a few tables to your DB just so you'd have GIS support is completely lame. It makes backup of the schema that much more complex and is very error prone (heaven forbid if you neglect to call AddGeometryColumn and just add a geometry column yourself).
土！问题是我创建的DB不是基于postgis模板DB的（因此没有相关的函数和几何列表等）。最后，我可以说一下，PostGIS 要求您将数百个函数、行和几个表添加到您的数据库中，以便您获得 GIS 支持的方式是完全蹩脚的。它使模式的备份变得更加复杂并且非常容易出错（如果你忽略调用 AddGeometryColumn 而只是自己添加一个几何列，那么绝对禁止）。
采纳答案by unmounted
The polygon is a fundamental Postgres type which PostGIS builds on top of. You enable the geometry columns with the PostGIS function select AddGeometryColumn(...). Otherwise you are working with straight polygons:
多边形是 PostGIS 构建在其之上的基本 Postgres 类型。您可以使用 PostGIS 功能启用几何列select AddGeometryColumn(...)。否则，您正在使用直多边形：
=> create table gt (id int, space polygon);
=> insert into gt values (1, '((2,2),(3,4),(3,6),(1,1))');
INSERT 0 1
=> select point(space) from gt where id = 1;
point
-------------
(2.25,3.25)
(1 row)
This is the center point of the polygon
这是多边形的中心点
=> select circle(space) from gt where id = 1;
circle
--------------------------------
<(2.25,3.25),1.93994028704315>
(1 row)
This is the minimum bounding circle of the polygon, expressed as a Postgres circletype. All the geometric operators are documented here: http://www.postgresql.org/docs/8.3/interactive/functions-geometry.htmlThe base polygon does not have any projection data, SRID, etc., so if it works with PostGIS it is probably just defaulting to presets and getting lucky. But of course there are tons of cases where you simply need geometry on a sub-geospatial scale.
这是多边形的最小边界圆，表示为 Postgrescircle类型。所有几何运算符都记录在此处：http://www.postgresql.org/docs/8.3/interactive/functions-geometry.html基础多边形没有任何投影数据、SRID 等，因此如果它适用于 PostGIS它可能只是默认为预设并且很幸运。但当然，在很多情况下，您只需要亚地理空间尺度上的几何图形。
回答by Assaf Lavie
Ok, weird, I found out the following much simpler syntax works:
好吧，奇怪的是，我发现以下更简单的语法有效：
insert into tbl_test (poly) values ('(0,0),(0,10),(10, 10), (0, 0)')
select * from tbl_test where poly @> '(2, 8)'
But I'm struggling to figure out the difference between these sets of functions and operators. Does this shorter syntax (which isn't really OpenGIS compliant) take advantage of the same spatial indexes, etc.?
但我正在努力弄清楚这些函数集和运算符之间的区别。这种较短的语法（实际上并不符合 OpenGIS）是否利用了相同的空间索引等？
