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.
1）。由于 SQL Server 中的空间对象基本上是 CLR 对象，因此语法感觉是倒退的。您编写的是 geom.STArea()，而不是 ST_Area(geom)，当您将函数链接在一起时，这一点变得更加明显。函数名称中删除下划线只是一个小麻烦。
2）。我有许多已被 SQL Server 接受的无效多边形，并且缺少 ST_MakeValid 函数可能会让这有点痛苦。
3）。仅限 Windows。一般来说，Microsoft 产品（如 ESRI 产品）被设计为能够很好地协同工作，但并不总是将标准的合规性和互操作性作为主要目标。如果您经营的是一家仅使用 Windows 的商店，这不是问题。
更新：在使用过 SQL Server 2012 之后，我可以说它已经有了显着的改进。现在有一个很好的几何验证功能，对地理数据类型有很好的支持，包括一个 FULL GLOBE 对象，它允许表示占据多个半球的对象并支持 复合曲线和圆弦，这对于精确和紧凑地表示弧（和圆）等非常有用。将坐标从一个 CRS 转换为另一个 CRS 仍然需要在第 3 方库中完成，尽管这在大多数应用程序中并不是一个阻碍。
我还没有使用具有足够大数据集的 SQL Server 来与 Postgis/MySQL 进行一对一比较，但从我所看到的功能来看，它们的行为正确，虽然不像 Postgis 那样功能齐全，但它对 MySQL 的产品来说是一个巨大的改进。
抱歉回答这么长，希望我这些年来所经历的痛苦和快乐可以对某人有所帮助。
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.
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.
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.html My 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 release InnoDB will finally support indexes on spatial columns, meaning that ACID, foreign keys and spatial indexes will finally be available in the same engine.
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:
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.
4). No support for rasters. Being able to do combined vector-raster analysis within a db is very useful GIS functionality.
5). No support for conversion from one spatial reference system to another.
6). Since acquisistion by Oracle, spatial has really been put on hold.
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.
Postgis
Given the issues we had with MySQL, we ultimately converted to Postgis. The key points of this experience have been.
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.
2). Rapid pace of development -- raster, topology, 3D support being recent examples of this.
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/
4). Plays very well with other applications, under the OSGeo umbrella, such as GeoServer and GDAL.
5). Stored procedures can be written in many languages, apart from the default plpgsql, such as Python or R.
5). Postgres is a very standards compliant, fully featured RDBMS, which aims to stay close to the ANSI standards.
6). Support for window functions and recursive queries -- not in MySQL, but in SQL Server. This has made writing more complex spatial queries cleaner.
SQL Server.
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.
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.
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.
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.
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 Strings which 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.
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.
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.
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
发布评论
评论（5）
绝对是PostGis。原因如下。
PostGis definitely. Here's why.
请注意，MySQL 终于添加了正确的 GIS 逻辑。
http:// /dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relations- Between-geometric-objects.html
但现阶段我无法评论成本或性能
Just an note that MySQL has finally added in proper GIS logic.
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
PostGIS 是最好的，因为它现在正在成为 GIS 应用程序的标准，而且 PostGIS 是免费的。性能远优于MySQL
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
我已经使用过所有三个数据库并完成了它们之间的迁移，所以希望我仍然可以在旧帖子中添加一些内容。十年前，我的任务是将一个庞大的（4.5 亿个空间对象）数据集从 GML 放入空间数据库。我决定尝试MySQL和Postgis，当时SQL Server没有空间，而且我们的启动氛围很小，所以MySQL似乎很适合。随后我参与了 MySQL，参加了几次会议/在几次会议上发表了演讲，并积极参与了 MySQL 中更多符合 GIS 功能的 Beta 测试，这些功能最终在 5.5 版本中发布。随后，我参与了将空间数据迁移到 Postgis 以及将公司数据（包含空间元素）迁移到 SQL Server 的工作。这些是我的发现。
MySQL
1).稳定性问题。在 5 年的时间里，我们遇到了多个数据库损坏问题，这些问题只能通过在索引文件上运行 myismachk 来修复，而在一个 4.5 亿行的表上，这个过程可能需要超过 24 个小时。
2）。直到最近，只有 MyISAM 表支持空间数据类型。这意味着如果您想要交易支持，那么您就不走运了。 InnoDB 表类型现在支持空间类型，但不支持它们的索引，考虑到空间数据集的典型大小，这并不是很有用。请参阅http://dev.mysql.com/doc/refman/5.0/en/innodb -restrictions.html 我参加会议的经验是，空间很大程度上是事后的想法——我们已经实现了复制、分区等，但它不适用于空间。
编辑：在 即将发布的 5.7.5 版本 InnoDB最终将支持空间列上的索引，这意味着 ACID、外键和空间索引最终将在同一个引擎中可用。
3）。与 Postgis 和 SQL Server 空间相比，空间功能极其有限。仍然没有作用于整个几何字段的 ST_Union 函数，这是我最常运行的查询之一，即，你不能写：
这在 GIS 上下文中非常有用。
从 some_table 中选择 ST_Union(geom1, const_geom)，即其中一个几何图形是硬编码的常量几何图形，相比之下有点限制。4).不支持光栅。能够在数据库内进行组合矢量栅格分析是非常有用的 GIS 功能。
5）。不支持从一种空间参考系统到另一种空间参考系统的转换。
6).自从被 Oracle 收购以来，空间确实被搁置了。
总的来说，公平地说，MySQL 多年来一直支持我们的网站、WMS 和一般空间处理，并且易于设置。不利的一面是，数据损坏是一个问题，并且被迫使用 MyISAM 表，您将放弃 RDBMS 的许多好处。
Postgis
鉴于我们在使用 MySQL 时遇到的问题，我们最终转换为 Postgis。这次经历的要点是。
1）。极其稳定。 5 年内没有数据损坏，我们现在 Centos 虚拟机上有大约 25 个 Postgres/GIS 盒子，负载程度不同。
2）。快速的开发速度——光栅、拓扑、3D 支持就是最近的例子。
3）。非常活跃的社区。 Postgis irc 频道和邮件列表是极好的资源。 Postgis 参考手册也很棒。 http://postgis.net/docs/manual-2.0/
4)。与 OSGeo 旗下的其他应用程序（例如 GeoServer 和 GDAL）配合得很好。
5）。除了默认的 plpgsql 之外，存储过程还可以用多种语言编写，例如 Python 或 R。5
)。 Postgres 是一个非常符合标准、功能齐全的 RDBMS，旨在接近 ANSI 标准。
6).支持窗口函数和递归查询——不是在 MySQL 中，而是在 SQL Server 中。这使得编写更复杂的空间查询变得更加清晰。
SQL Server。
我只使用过 SQL Server 2008 空间功能，以及该版本的许多烦恼 - 缺乏对从一种 CRS 到另一种 CRS 的转换的支持，需要将自己的参数添加到空间索引——现已解决。
1）。由于 SQL Server 中的空间对象基本上是 CLR 对象，因此语法感觉是倒退的。您编写的是 geom.STArea()，而不是 ST_Area(geom)，当您将函数链接在一起时，这一点变得更加明显。函数名称中删除下划线只是一个小麻烦。
2）。我有许多已被 SQL Server 接受的无效多边形，并且缺少 ST_MakeValid 函数可能会让这有点痛苦。
3）。仅限 Windows。一般来说，Microsoft 产品（如 ESRI 产品）被设计为能够很好地协同工作，但并不总是将标准的合规性和互操作性作为主要目标。如果您经营的是一家仅使用 Windows 的商店，这不是问题。
更新：在使用过 SQL Server 2012 之后，我可以说它已经有了显着的改进。现在有一个很好的几何验证功能，对地理数据类型有很好的支持，包括一个 FULL GLOBE 对象，它允许表示占据多个半球的对象并支持 复合曲线和圆弦，这对于精确和紧凑地表示弧（和圆）等非常有用。将坐标从一个 CRS 转换为另一个 CRS 仍然需要在第 3 方库中完成，尽管这在大多数应用程序中并不是一个阻碍。
我还没有使用具有足够大数据集的 SQL Server 来与 Postgis/MySQL 进行一对一比较，但从我所看到的功能来看，它们的行为正确，虽然不像 Postgis 那样功能齐全，但它对 MySQL 的产品来说是一个巨大的改进。
抱歉回答这么长，希望我这些年来所经历的痛苦和快乐可以对某人有所帮助。
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.
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.
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.html My 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 release InnoDB will finally support indexes on spatial columns, meaning that ACID, foreign keys and spatial indexes will finally be available in the same engine.
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:
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.4). No support for rasters. Being able to do combined vector-raster analysis within a db is very useful GIS functionality.
5). No support for conversion from one spatial reference system to another.
6). Since acquisistion by Oracle, spatial has really been put on hold.
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.
Postgis
Given the issues we had with MySQL, we ultimately converted to Postgis. The key points of this experience have been.
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.
2). Rapid pace of development -- raster, topology, 3D support being recent examples of this.
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/
4). Plays very well with other applications, under the OSGeo umbrella, such as GeoServer and GDAL.
5). Stored procedures can be written in many languages, apart from the default plpgsql, such as Python or R.
5). Postgres is a very standards compliant, fully featured RDBMS, which aims to stay close to the ANSI standards.
6). Support for window functions and recursive queries -- not in MySQL, but in SQL Server. This has made writing more complex spatial queries cleaner.
SQL Server.
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.
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.
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.
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.
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 Strings which 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.
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.
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.
如果您有兴趣进行彻底的比较，我推荐“交叉比较 SQL Server 2008 Spatial、PostgreSQL/ PostGIS 1.3-1.4、MySQL 5-6" 和/或"比较 SQL Server 2008 R2、Oracle 11G R2、PostgreSQL/PostGIS 1.5 空间特征”，作者：Boston GIS。
考虑到您的观点：
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.
Considering your points: