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

Question

EDIT : J'utilise Postgres avec PostGIS depuis quelques mois maintenant, et je suis satisfait.

J'ai besoin d'analyser quelques millions d'enregistrements géocodés, dont chacun aura une latitude et une longitude. Ces enregistrements comprennent des données d'au moins trois types différents, et je vais essayer de voir si chaque ensemble influence l'autre.

Quelle est la meilleure base de données pour le magasin de données sous-jacent pour toutes ces données ? Voici ce que je souhaite :

• Je suis familier avec le SGBD. Je suis le plus faible avec PostgreSQL, mais je suis prêt à apprendre si tout le reste se vérifie.
• Il se débrouille bien avec les requêtes SIG. Les recherches sur Google suggèrent que PostgreSQL + PostGIS est peut-être le plus fort ? En tout cas, de nombreux produits semblent l'utiliser. Les extensions spatiales de MySql semblent comparativement minimes ?
• Faible coût. Malgré la limite de 10 Go pour les bases de données dans SQL Server Express 2008 R2, je ne suis pas sûr de vouloir vivre avec cette limite et les autres limitations de la version gratuite.
• Pas d'antagonisme avec Microsoft .NET Framework. Grâce à Connector/Net 6.3.4, MySql fonctionne bien avec les programmes C# et .NET Framework 4. Il prend entièrement en charge l'Entity Framework de .NET 4. Je ne trouve pas d'équivalent PostgreSQL non commercial, bien que je ne sois pas opposé à payer 180 $ pour dotConnect for PostgreSQL Professional Edition de Devart.
• Compatible avec R. Il semble que les trois peuvent communiquer avec R en utilisant ODBC, ce qui n'est pas un problème.

J'ai déjà fait un peu de développement en utilisant MySql, mais je peux changer si nécessaire.

Answer 1

J'ai travaillé avec les trois bases de données et effectué des migrations entre elles. J'espère donc pouvoir encore ajouter quelque chose à un ancien article. Il y a dix ans, j'ai été chargé de transférer un vaste ensemble de données - 450 millions d'objets spatiaux - de GML vers une base de données spatiale. J'ai décidé d'essayer MySQL et Postgis. À l'époque, il n'y avait pas de base de données spatiales dans SQL Server et nous étions une petite entreprise en démarrage, donc MySQL semblait convenir. Par la suite, j'ai été impliqué dans MySQL, j'ai participé à plusieurs conférences et j'ai été fortement impliqué dans le test bêta des fonctions plus compatibles avec les SIG dans MySQL, qui a finalement été publié avec la version 5.5. J'ai ensuite participé à la migration de nos données spatiales vers Postgis et de nos données d'entreprise (avec des éléments spatiaux) vers SQL Server. Voici mes conclusions.

MySQL

1). Problèmes de stabilité. Au cours des 5 dernières années, nous avons eu plusieurs problèmes de corruption de la base de données, qui ne pouvaient être résolus qu'en exécutant myismachk sur le fichier d'index, un processus qui peut prendre bien plus de 24 heures sur une table de 450 millions de lignes.

2). Jusqu'à récemment, seules les tables MyISAM supportaient le type de données spatiales. Cela signifie que si vous souhaitez une prise en charge des transactions, vous n'avez pas de chance. Le type de table InnoDB prend désormais en charge les types de données spatiales, mais pas les index, ce qui, étant donné la taille typique des ensembles de données spatiales, n'est pas très utile. Voir http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html D'après ce que j'ai pu constater lors des conférences, le spatial n'a été envisagé qu'après coup : nous avons mis en place la réplication, le partitionnement, etc. mais cela ne fonctionne pas avec le spatial. EDIT : Dans le prochaine version 5.7.5 InnoDB va enfin prendre en charge les index sur les colonnes spatiales, ce qui signifie que les ACID, les clés étrangères et les index spatiaux seront enfin disponibles dans le même moteur.

3). La fonctionnalité spatiale est extrêmement limitée par rapport à Postgis et SQL Server spatial. Il n'y a toujours pas de fonction ST_Union qui agit sur un champ géométrique entier, l'une des requêtes que j'exécute le plus souvent, c'est-à-dire que l'on ne peut pas écrire :

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

ce qui est très utile dans un contexte SIG. Select ST_Union(geom1, const_geom) from some_table Le fait que l'une des géométries soit une géométrie constante codée en dur est un peu limité en comparaison.

4). Pas de support pour les rasters. La possibilité d'effectuer une analyse combinée vecteur-raster dans une base de données est une fonctionnalité SIG très utile.

5). Aucun support pour la conversion d'un système de référence spatiale à un autre.

6). Depuis l'acquisition par Oracle, le spatial a vraiment été mis en veilleuse.

Dans l'ensemble, pour être juste envers MySQL, il a supporté notre site web, notre WMS et le traitement spatial général pendant plusieurs années, et a été facile à mettre en place. En revanche, la corruption des données était un problème, et en étant obligé d'utiliser des tables MyISAM, vous renoncez à une grande partie des avantages d'un SGBDR.

Postgis

Compte tenu des problèmes que nous rencontrions avec MySQL, nous avons finalement opté pour Postgis. Les points clés de cette expérience ont été.

1). Extrême stabilité. Aucune corruption de données en 5 ans et nous avons maintenant environ 25 boîtes Postgres/GIS sur des machines virtuelles Centos, à des degrés divers de charge.

2). Le rythme rapide de développement - raster, topologie, support 3D en sont des exemples récents.

3). Communauté très active. Le canal irc et la liste de diffusion Postgis sont d'excellentes ressources. Le manuel de référence de Postgis est également excellent. http://postgis.net/docs/manual-2.0/

4). Joue très bien avec d'autres applications, sous le parapluie OSGeo, telles que GeoServer et GDAL.

5). Les procédures stockées peuvent être écrites dans de nombreux langages, en dehors du langage par défaut plpgsql, comme Python ou R.

5). Postgres est un SGBDR très conforme aux normes, doté de toutes les fonctionnalités, qui vise à rester proche des normes ANSI.

6). Prise en charge des fonctions de fenêtre et des requêtes récursives -- non pas dans MySQL, mais dans SQL Server. Cela a rendu plus propre l'écriture de requêtes spatiales plus complexes.

SQL Server.

Je n'ai utilisé que la fonctionnalité spatiale de SQL Server 2008, et bon nombre des inconvénients de cette version - absence de prise en charge des conversions d'un SRC à un autre, nécessité d'ajouter ses propres paramètres aux index spatiaux - ont été résolus.

1). Comme les objets spatiaux dans SQL Server sont essentiellement des objets CLR, la syntaxe semble rétrograde. Au lieu de ST_Area(geom), vous écrivez geom.STArea() et cela devient encore plus évident lorsque vous enchaînez les fonctions. L'abandon de l'underscore dans les noms de fonctions n'est qu'un inconvénient mineur.

2). J'ai eu un certain nombre de polygones invalides qui ont été acceptés par le serveur SQL, et l'absence d'une fonction ST_MakeValid peut rendre la tâche un peu pénible.

3). Windows uniquement. En général, les produits Microsoft (comme ceux d'ESRI) sont conçus pour fonctionner très bien les uns avec les autres, mais n'ont pas toujours pour objectifs principaux la conformité aux normes et l'interopérabilité. Si vous gérez un atelier uniquement sous Windows, ce n'est pas un problème.

UPDATE : Après avoir joué un peu avec SQL Server 2012, je peux dire qu'il a été considérablement amélioré. Il y a maintenant une bonne fonction de validation de la géométrie, il y a un bon support pour le type de données Géographie, y compris un objet FULL GLOBE, qui permet de représenter des objets qui occupent plus d'un hémisphère et un support pour les éléments suivants Courbes composées et cordes circulaires qui est utile, entre autres, pour les représentations précises et compactes des arcs (et des cercles). La transformation des coordonnées d'un CRS à un autre doit encore être effectuée dans des bibliothèques tierces, bien que cela ne soit pas un obstacle pour la plupart des applications.

Je n'ai pas utilisé SQL Server avec des ensembles de données suffisamment importants pour pouvoir comparer Postgis/MySQL, mais d'après ce que j'ai vu, les fonctions se comportent correctement et, même si elles ne sont pas aussi complètes que celles de Postgis, elles constituent une amélioration considérable par rapport aux offres de MySQL.

Désolé pour cette longue réponse, j'espère que certaines des douleurs et des joies que j'ai connues au fil des ans pourront être utiles à quelqu'un.

Answer 2

Si vous êtes intéressé par une comparaison approfondie, je recommande "Comparaison croisée SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6" et/ou "Comparer les fonctionnalités spatiales de SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5". par le SIG de Boston.

Considérant vos points :

• Je suis familier avec le SGBD : la mise en place d'une base de données PostGIS sous Windows est facile, la gestion par PgAdmin3 est également très simple
• Il se débrouille bien avec les requêtes SIG : PostGIS est certainement le plus fort des trois, seul Oracle Spatial serait comparable mais il est disqualifié si l'on considère ses coûts.
• Faible coût : +1 pour PostGIS, c'est sûr
• Pas d'antagonisme avec Microsoft .NET Framework : Vous devriez au moins pouvoir vous connecter via ODBC ( voir le wiki Postgres )
• Compatible avec R : ne devrait pas être un problème avec aucun des trois

Answer 3

PostGis définitivement. Voici pourquoi.

1. Postgres est de loin supérieur à MySQL en termes de performances. Le serveur est plus tolérant aux pannes et dispose d'outils prêts à l'emploi pour l'équilibrage des charges, la mise en cache et l'optimisation.
2. PostGIS est en train de devenir un standard dans les applications SIG.
3. C'est gratuit.

Answer 4

Il est à noter que MySQL a finalement ajouté une logique GIS appropriée.

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

Mais je ne peux pas me prononcer sur le coût ou les performances à ce stade.

Answer 5

PostGIS est le meilleur car il devient un standard dans les applications SIG de nos jours et PostGIS est gratuit. Il est de loin supérieur à MySQL en termes de performances