PostgreSQL 中的空间数据

Spatial Data in PostgreSQL

提问by Assaf Lavie

PostgreSQL supports a variety of geometric typesout of the box, along with lots of geometric operatorsand GiST indexeswhich seem to offer spatial indexing of data.

PostgreSQL 支持各种开箱即用的几何类型，以及许多似乎提供数据空间索引的几何运算符和GiST 索引。

And then there's also PostGIS, which is an extension to PG.

然后还有PostGIS，它是 PG 的扩展。

What is the difference between the built-in spatial support in PG and PostGIS?

PG 和 PostGIS 中内置的空间支持有什么区别？

If my application needs to store geographical coordinates (points, areas, polygons) and then efficiently do queries (such as point-in-polygon, polygon intersection), do I need PostGIS or can I use the (arguably) more convenient and simpler built-in data types / syntax?

如果我的应用程序需要存储地理坐标（点、区域、多边形），然后有效地进行查询（例如多边形中的点、多边形交集），我是否需要 PostGIS 或者我可以使用（可以说）更方便和更简单的构建-in 数据类型/语法？

回答by cjs

First I'd like to clarify GiST indexes: GiST is actually a framework for creating indexes for new data types, not any particular indexing scheme itself. This framework happens to be used for the geometric types that come with Postgres, but it's also used for a trigram-matching text similarity index on standard text columns, and of course is used by the indexing schemes of many external packages, among which we can number PostGIS.

首先我想澄清一下 GiST 索引：GiST 实际上是一个为新数据类型创建索引的框架，而不是任何特定的索引方案本身。这个框架正好用于Postgres自带的几何类型，但也用于标准文本列上的trigram-matching文本相似度索引，当然也被很多外部包的索引方案使用，其中我们可以数字邮政地理信息系统。

Whether the standard geometric data types will work for you or you need PostGIS depends entirely on your application.

标准几何数据类型是否适合您或您需要 PostGIS 完全取决于您的应用程序。

PostGIS stores geometrical data in a column of type "geometry"; in this you can store more-or-less arbitrary data (points, circles, polygons, what-have-you). The indexing is fast and quite sophisticated: it can do things like lossy indexing using bounding boxes for complex shapes that are not indexable in any reasonable way otherwise. Different spatial reference systems are supported, with automatic conversion of the results of queries. PostGIS also supports industry-standard OpenGIS formats, which can help with sharing data with other systems.

PostGIS 将几何数据存储在“几何”类型的列中；在这里你可以存储或多或少的任意数据（点、圆、多边形、你有什么）。索引快速且非常复杂：它可以使用边界框对复杂形状进行有损索引等操作，否则这些形状无法以任何合理的方式进行索引。支持不同的空间参考系统，查询结果自动转换。PostGIS 还支持行业标准的 OpenGIS 格式，这有助于与其他系统共享数据。

In contrast, the set of internal geometric types and their indexes is a lot less sophisticated. There's no real "generic" geometry type; instead you have to chose to have a column's type be a point, line, circle, polygon, or what-have-you; for combinations, you will probably have to use multiple columns. The indexing is not as good; not as many different types of shapes can be indexed (though you could add bounding box support by using a separate column for them and generating the bounding boxes manually) and the indexes are probably not as fast in some situations. On the other hand, if the internal geometric types fill your needs, you gain the advantage that your application is more easily portable to other systems that have Postgres but not PostGIS installed.

相比之下，内部几何类型及其索引的集合要复杂得多。没有真正的“通用”几何类型；相反，您必须选择将列的类型设为点、线、圆、多边形或您拥有的东西；对于组合，您可能必须使用多列。索引不太好；没有那么多不同类型的形状可以被索引（尽管您可以通过为它们使用单​​独的列并手动生成边界框来添加边界框支持）并且在某些情况下索引可能没有那么快。另一方面，如果内部几何类型满足您的需求，您的应用程序可以更轻松地移植到安装了 Postgres 但未安装 PostGIS 的其他系统。

My advice would be to play around with the internal geometric types and see how well that works out for you; if you start to run into issues, try out PostGIS.

我的建议是尝试使用内部几何类型，看看它对你的效果如何；如果您开始遇到问题，请尝试使用 PostGIS。