使用PostgreSQL的多边形中点的SQL查询 [英] SQL query for point-in-polygon using PostgreSQL

问题描述

我有以下简单表格:

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))'))

并遇到此错误:

列"poly"的类型为多边形，但表达式的类型为几何"

column "poly" is of type polygon but expression is of type geometry

哪个比较la脚.所以我的第一个问题是:

Which is lame. So my first questions is:

1. 我真的必须投吗?

无论如何，施放后仍然有效.现在，我正在尝试执行一个简单的ST_Contains查询:

Anyway, after casting it works. And now I'm trying to do a simple ST_Contains query:

select id, poly from tbl_test where ST_Contains(poly, Point(GeomFromText('POINT(9 2)')))

哪个出现错误:

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.

我该怎么办?

以下作品:

select st_contains(st_geomfromtext('POLYGON((0 0, 10 10, 10 0, 0 0))'), st_geomfromtext('POINT(0 0)'))

但这可能是因为两个参数均为Geometry类型.针对表数据的实际查询不起作用.

But that's probably because both arguments are of type Geometry. The actual query against the table data doesn't work.

答案:

土井！问题是我创建的数据库不是基于postgis模板数据库(因此没有相关的功能和几何列表等). 最后，我想指出一下，PostGIS要求您向数据库中添加数百个功能，行和一些表的方式仅仅是为了让GIS支持完全la脚.它使架构的备份变得更加复杂并且非常容易出错(如果您忽略调用AddGeometryColumn并自己添加几何列，则禁止使用天堂.)

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).

推荐答案

多边形是PostGIS构建于其上的基本Postgres类型.您可以使用PostGIS功能select AddGeometryColumn(...)启用几何列.否则，您将使用直线多边形:

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:

=> 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)

这是多边形的中心点

=> select circle(space) from gt where id = 1;
             circle             
--------------------------------
 <(2.25,3.25),1.93994028704315>
(1 row)

这是多边形的最小边界圆，表示为Postgres circle类型.在此处:http://www.postgresql.org中记录了所有几何运算符/docs/8.3/interactive/functions-geometry.html 基础多边形没有任何投影数据，SRID等，因此，如果它与PostGIS一起使用，则可能只是默认设置而已，很幸运.但是，当然，在很多情况下，您只需要在次ge骨尺度上进行几何处理即可.

This is the minimum bounding circle of the polygon, expressed as a Postgres circle type. All the geometric operators are documented here: http://www.postgresql.org/docs/8.3/interactive/functions-geometry.html The 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.

这篇关于使用PostgreSQL的多边形中点的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！