PostgreSQL中的空间数据 [英] Spatial Data in PostgreSQL

问题描述

PostgreSQL支持多种几何类型框，以及许多几何运算符和 GiST索引，它似乎提供了数据的空间索引。

PostgreSQL supports a variety of geometric types out of the box, along with lots of geometric operators and GiST indexes which seem to offer spatial indexing of data.

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

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

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

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

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

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?

推荐答案

首先，我要澄清GiST索引：GiST实际上是一个框架，用于为新数据类型创建索引，而不是任何特定的索引方案本身。该框架碰巧用于Postgres随附的几何类型，但也用于标准文本列上三元组匹配的文本相似性索引，当然，许多外部软件包的索引方案都使用该框架，我们可以编号PostGIS。

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.

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

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

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

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.

相反，内部几何类型及其索引的集合要少得多。精巧。没有真正的通用几何类型；相反，您必须选择使列的类型为点，线，圆，多边形或您所拥有的类型；对于组合，您可能必须使用多个列。索引不是很好。不能为许多不同类型的形状建立索引（尽管您可以通过为它们使用单​​独的列并手动生成边界框来添加边界框支持），并且索引在某些情况下可能不那么快。另一方面，如果内部几何类型满足您的需求，则您将获得优势，即您的应用程序更易于移植到其他装有Postgres但未安装PostGIS的系统中。

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.

我的建议是研究内部几何类型，看看效果如何。如果您开始遇到问题，请尝试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.

这篇关于PostgreSQL中的空间数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！