GIS：PostGIS/PostgreSQL vs. MySql vs. SQL Server？

GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server?

提问by Aren Cambre

EDIT: I have been using Postgres with PostGIS for a few months now, and I am satisfied.

编辑：我几个月来一直在使用 Postgres 和 PostGIS，我很满意。

I need to analyze a few million geocoded records, each of which will have latitude and longitude. These records include data of at least three different types, and I will be trying to see if each set influences the other.

我需要分析几百万条地理编码记录，每条记录都有纬度和经度。这些记录包括至少三种不同类型的数据，我将尝试查看每个集合是否会影响另一个。

What database is best for the underlying data store for all this data? Here's my desires:

什么数据库最适合所有这些数据的底层数据存储？这是我的愿望：

• I'm familiar with the DBMS.I'm weakest with PostgreSQL, but I am willing to learn if everything else checks out.
• It does well with GIS queries.Google searches suggest that PostgreSQL + PostGIS may be the strongest? At least a lot of products seem to use it. MySql's Spatial Extensions seem comparatively minimal?
• Low cost.Despite the 10GB DB limit in SQL Server Express 2008 R2, I'm not sure I want to live with this and other limitations of the free version.
• Not antagonistic with Microsoft .NET Framework.Thanks to Connector/Net 6.3.4, MySql works well C# and .NET Framework 4 programs. It fully supports .NET 4's Entity Framework. I cannot find any noncommercial PostgreSQL equivalent, although I'm not opposed to paying $180 for Devart's dotConnect for PostgreSQL Professional Edition.
• Compatible with R.It appears all 3 of these can talk with R using ODBC, so may not be an issue.

• 我熟悉 DBMS。我在 PostgreSQL 方面最弱，但我愿意学习是否其他一切都检查出来。
• 它适用于 GIS 查询。谷歌搜索提示 PostgreSQL + PostGIS 可能是最强的？至少很多产品似乎都在使用它。MySql 的空间扩展似乎比较少？
• 低成本。尽管 SQL Server Express 2008 R2 中有 10GB 的 DB 限制，但我不确定我是否愿意忍受免费版本的这个和其他限制。
• 不与 Microsoft .NET Framework 为敌。感谢 Connector/Net 6.3.4，MySql 可以很好地运行 C# 和 .NET Framework 4 程序。它完全支持 .NET 4 的实体框架。我找不到任何非商业的 PostgreSQL 等价物，尽管我不反对为 Devart 的 dotConnect for PostgreSQL Professional Edition 支付 180 美元。
• 与 R 兼容。看起来所有这三个都可以使用 ODBC 与 R 通信，所以可能不是问题。

I've already done some development using MySql, but I can change if necessary.

我已经使用 MySql 进行了一些开发，但如果需要，我可以进行更改。

采纳答案by underdark

If you are interested in a thorough comparison, I recommend "Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6"and/or "Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features"by Boston GIS.

如果您有兴趣进行彻底的比较，我推荐“Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6”和/或“Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial”特征”由波士顿 GIS 提供。

Considering your points:

考虑到你的观点：

• I'm familiar with the DBMS:setting up a PostGIS database on Windows is easy, using PgAdmin3 management is straight-forward too
• It does well with GIS queries:PostGIS is definitely strongest of the three, only Oracle Spatial would be comparable but is disqualified if you consider its costs
• Low cost:+1 for PostGIS for sure
• Not antagonistic with Microsoft .NET Framework:You should at least be able to connect via ODBC (see Postgres wiki)
• Compatible with R:shouldn't be a problem with any of the three

• 我熟悉 DBMS：在 Windows 上设置 PostGIS 数据库很容易，使用 PgAdmin3 管理也很简单
• 它适用于 GIS 查询：PostGIS 绝对是三者中最强的，只有 Oracle Spatial 具有可比性，但如果考虑其成本，则不合格
• 低成本：肯定为 PostGIS +1
• 不与 Microsoft .NET Framework 对抗：您至少应该能够通过 ODBC 进行连接（请参阅 Postgres wiki）
• 与R兼容：三者中的任何一个都应该没有问题

回答by John Powell

I have worked with all three databases and done migrations between them, so hopefully I can still add something to an old post. Ten years ago I was tasked with putting a largish -- 450 million spatial objects -- dataset from GML to a spatial database. I decided to try out MySQL and Postgis, at the time there was no spatial in SQL Server and we had a small startup atmosphere, so MySQL seemed a good fit. I subsequently was involved in MySQL, I attended/spoke at a couple of conferences and was heavily involved in the beta testing of the more GIS-compliant functions in MySQL that was finally released with version 5.5. I have subsequently been involved with migrating our spatial data to Postgis and our corporate data (with spatial elements) to SQL Server. These are my findings.

我已经使用了所有三个数据库并在它们之间进行了迁移，所以希望我仍然可以在旧帖子中添加一些内容。十年前，我的任务是将一个庞大的——4.5 亿个空间对象——数据集从 GML 放到一个空间数据库中。我决定尝试一下 MySQL 和 Postgis，当时 SQL Server 没有空间，我们的启动氛围很小，所以 MySQL 似乎很合适。随后我参与了 MySQL，我参加了几次会议/发言，并积极参与了 MySQL 中更符合 GIS 的功能的 beta 测试，该功能最终在 5.5 版中发布。随后，我参与了将我们的空间数据迁移到 Postgis 以及将我们的公司数据（带有空间元素）迁移到 SQL Server 的工作。这些是我的发现。

MySQL

MySQL

1). Stability issues. Over the course of 5 years, we had several database corruptions issues, which could only be fixed by running myismachk on the index file, a process than can take well over 24 hours on a 450 million row table.

1）。稳定性问题。在 5 年的时间里，我们遇到了几个数据库损坏问题，这些问题只能通过在索引文件上运行 myismachk 来修复，这个过程在 4.5 亿行表上可能需要超过 24 小时。

2). Until recently only MyISAM tables supported the spatial data type. This means if you want transaction support you are out of luck. InnoDB table type does now support spatial types, but not indexes on them, which given the typical sizes of spatial data sets, isn't terribly useful. See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.htmlMy experience from going to conferences was that spatial was very much an afterthought -- we've implemented replication, partitioning, etc, but it doesn't work with spatial. EDIT: In the upcoming 5.7.5 releaseInnoDB will finally support indexes on spatial columns, meaning that ACID, foreign keys and spatial indexes will finally be available in the same engine.

2）。直到最近，只有 MyISAM 表支持空间数据类型。这意味着如果你想要交易支持，你就不走运了。InnoDB 表类型现在确实支持空间类型，但不支持它们的索引，考虑到空间数据集的典型大小，这不是非常有用。参见http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html我参加会议的经验是空间是事后才想到的——我们已经实现了复制、分区等，但它不适用于空间。编辑：在即将发布的 5.7.5 版本中，InnoDB 最终将支持空间列上的索引，这意味着 ACID、外键和空间索引最终将在同一引擎中可用。

3). The spatial functionality is extremely limited compared to both Postgis and SQL Server spatial. There is still no ST_Union function that acts on an entire geometry field, one of the queries I run most often, ie, you can't write:

3）。与 Postgis 和 SQL Server 空间相比，空间功能极其有限。仍然没有 ST_Union 函数作用于整个几何字段，这是我最常运行的查询之一，即你不能写：

select attribute, ST_Union(geom) from some_table group by some_attribute

which is very useful in a GIS context. Select ST_Union(geom1, const_geom) from some_table, ie, one of the geometries is a hard-coded constant geometry is a bit limiting in comparison.

这在 GIS 环境中非常有用。Select ST_Union(geom1, const_geom) from some_table，即其中一个几何图形是硬编码的常量几何图形，相比之下有点限制。

4). No support for rasters. Being able to do combined vector-raster analysis within a db is very useful GIS functionality.

4）。不支持栅格。能够在 db 中进行矢量栅格组合分析是非常有用的 GIS 功能。

5). No support for conversion from one spatial reference system to another.

5）。不支持从一个空间参考系统到另一个空间参考系统的转换。

6). Since acquisistion by Oracle, spatial has really been put on hold.

6). 自从被甲骨文收购后，spatial 就真的被搁置了。

Overall, to be fair to MySQL it supported our website, WMS and general spatial processing for several years, and was easy to set up. On the downside, data corruption was an issue, and by being forced to use MyISAM tables you are giving up a lot of the benefits of an RDBMS.

总的来说，公平地说，MySQL 多年来一直支持我们的网站、WMS 和一般空间处理，并且易于设置。不利的一面是，数据损坏是一个问题，并且通过被迫使用 MyISAM 表，您放弃了 RDBMS 的许多好处。

Postgis

邮政地理信息系统

Given the issues we had with MySQL, we ultimately converted to Postgis. The key points of this experience have been.

鉴于我们在 MySQL 中遇到的问题，我们最终转换为 Postgis。这段经历的关键点是。

1). Extreme stability. No data corruption in 5 years and we now have around 25 Postgres/GIS boxes on centos virtual machines, under varying degrees of load.

1）。极端的稳定性。5 年内没有数据损坏，我们现在在 centos 虚拟机上有大约 25 个 Postgres/GIS 机器，在不同程度的负载下。

2). Rapid pace of development -- raster, topology, 3D support being recent examples of this.

2）。快速发展——光栅、拓扑、3D 支持是最近的例子。

3). Very active community. The Postgis irc channel and mailing list are excellent resources. The Postgis reference manual is also excellent. http://postgis.net/docs/manual-2.0/

3）。非常活跃的社区。Postgis irc 频道和邮件列表是极好的资源。Postgis 参考手册也很棒。http://postgis.net/docs/manual-2.0/

4). Plays very well with other applications, under the OSGeo umbrella, such as GeoServer and GDAL.

4）。与 OSGeo 保护伞下的其他应用程序（例如 GeoServer 和 GDAL）配合得非常好。

5). Stored procedures can be written in many languages, apart from the default plpgsql, such as Python or R.

5）。存储过程可以用多种语言编写，除了默认的 plpgsql，如 Python 或 R。

5). Postgres is a very standards compliant, fully featured RDBMS, which aims to stay close to the ANSI standards.

5）。Postgres 是一个非常符合标准的、功能齐全的 RDBMS，旨在接近 ANSI 标准。

6). Support for window functions and recursive queries -- not in MySQL, but in SQL Server. This has made writing more complex spatial queries cleaner.

6). 支持窗口函数和递归查询——不是在 MySQL 中，而是在 SQL Server 中。这使得编写更复杂的空间查询更清晰。

SQL Server.

SQL 服务器。

I have only used SQL Server 2008 spatial functionality, and many of the annoyances of that release -- lack of support for conversions from one CRS to another, the need to add your own parameters to spatial indexes -- have now been resolved.

我只使用了 SQL Server 2008 空间功能，该版本的许多烦恼——缺乏对从一个 CRS 到另一个 CRS 的转换的支持，需要将你自己的参数添加到空间索引——现在已经得到解决。

1). As spatial objects in SQL Server are basically CLR objects, the syntax feels backwards. Instead of ST_Area(geom) you write geom.STArea() and this becomes even more obvious when you chain functions together. The dropping of the underscore in function names is merely a minor annoyance.

1）。由于 SQL Server 中的空间对象基本上是 CLR 对象，因此语法感觉倒退。您编写 geom.STArea() 而不是 ST_Area(geom) ，当您将函数链接在一起时，这一点变得更加明显。删除函数名称中的下划线只是一个小烦恼。

2). I have had a number of invalid polygons that have been accepted by SQL Server, and the lack of a ST_MakeValid function can make this a bit painful.

2）。我有许多无效的多边形已被 SQL Server 接受，缺少 ST_MakeValid 函数会使这有点痛苦。

3). Windows only. In general, Microsoft products (like ESRI ones) are designed to work very well with each other, but don't always have standard's compliance and interoperability as primary objectives. If you are running a windows only shop, this is not an issue.

3）。仅限 Windows。一般而言，Microsoft 产品（如 ESRI 产品）旨在相互配合良好，但并不总是将标准的合规性和互操作性作为主要目标。如果您经营的是一家仅限 Windows 的商店，这不是问题。

UPDATE: having played a bit with SQL Server 2012, I can say that it has been improved significantly. There is now a good geometry validation function, there is good support for the Geography data type, including a FULL GLOBE object, which allows representing objects that occupy more than one hemisphere and support for Compound Curves and Circular Stringswhich is useful for accurate and compact representations of arcs (and circles) among other things. Transforming coordinates from one CRS to another still needs to be done in 3rd party libraries, though this is not a show stopper in most applications.

更新：在使用 SQL Server 2012 后，我可以说它已经有了显着的改进。现在有一个很好的几何验证功能，对 Geography 数据类型有很好的支持，包括一个 FULL GLOBE 对象，它允许表示占据多个半球的对象，并支持复合曲线和圆形字符串，这对于准确和紧凑很有用圆弧（和圆）的表示等等。将坐标从一个 CRS 转换为另一个 CRS 仍然需要在 3rd 方库中完成，尽管这在大多数应用程序中并不是一个障碍。

I haven't used SQL Server with large enough datasets to compare one on one with Postgis/MySQL, but from what I have seen the functions behave correctly, and while not quite as fully featured as Postgis, it is a huge improvement on MySQL's offerings.

我还没有使用具有足够大数据集的 SQL Server 与 Postgis/MySQL 进行一对一比较，但从我所看到的函数行为正确，虽然不像 Postgis 那样功能齐全，但它是对 MySQL 产品的巨大改进.

Sorry for such a long answer, I hope some of the pain and joy I have suffered over the years might be of help to someone.

抱歉回答这么长，希望这些年来我所遭受的一些痛苦和快乐可能对某人有所帮助。

回答by dekomote

PostGis definitely. Here's why.

PostGis 绝对是。这是为什么。

1. Postgres is far superior to MySQL in performance. Server is more fault tolerant, has out of the box tools for load-balancing, caching and optimization.
2. PostGIS is becoming a standard in GIS apps.
3. It's free.

1. Postgres 在性能上远远优于 MySQL。服务器具有更高的容错性，具有用于负载平衡、缓存和优化的开箱即用工具。
2. PostGIS 正在成为 GIS 应用程序的标准。
3. 免费。

回答by ErichBSchulz

Just an note that MySQL has finally added in proper GIS logic.

请注意，MySQL 终于添加了适当的 GIS 逻辑。

http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations-between-geometric-objects.html

http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations-between-geometric-objects.html

But I can't comment on cost or performance at this stage

但现阶段我无法评论成本或性能

回答by Khalid Mahmood

PostGIS is best because it is becoming a standard in GIS applications these days and PostGIS is free. It is far superior to MySQL in performance

PostGIS 是最好的，因为现在它正在成为 GIS 应用程序的标准，并且 PostGIS 是免费的。性能远超MySQL