speed postgres large differences benchmark and mysql postgresql gis geocoding postgis

differences - postgresql vs mysql large database



GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server? (5)

EDITAR: He estado utilizando Postgres con PostGIS durante unos meses, y estoy satisfecho.

Necesito analizar unos pocos millones de registros geocodificados, cada uno de los cuales tendrá latitud y longitud. Estos registros incluyen datos de al menos tres tipos diferentes, y trataré de ver si cada conjunto influye en el otro.

¿Qué base de datos es la mejor para el almacén de datos subyacente para todos estos datos? Aquí están mis deseos:

  • Estoy familiarizado con el DBMS. Estoy más débil con PostgreSQL, pero estoy dispuesto a saber si todo lo demás se confirma.
  • Funciona bien con las consultas GIS. Las búsquedas de Google sugieren que PostgreSQL + PostGIS puede ser el más fuerte? Al menos muchos productos parecen usarlo. Las extensiones espaciales de MySQL parecen comparativamente mínimas.
  • Bajo costo. A pesar del límite de 10 GB de DB en SQL Server Express 2008 R2, no estoy seguro de querer vivir con esta y otras limitaciones de la versión gratuita.
  • No es antagónico con Microsoft .NET Framework. Gracias a Connector / Net 6.3.4, MySql funciona bien con los programas C # y .NET Framework 4. Es totalmente compatible con .NET 4 Entity Framework. No puedo encontrar ningún equivalente PostgreSQL no comercial, aunque no me opongo a pagar $ 180 por el dotConnect de Devart para PostgreSQL Professional Edition.
  • Compatible con R. Parece que los 3 de estos pueden hablar con R usando ODBC, por lo que puede no ser un problema.

Ya he hecho algo de desarrollo usando MySql, pero puedo cambiarlo si es necesario.

He trabajado con las tres bases de datos y he realizado migraciones entre ellas, así que espero poder agregar algo a una publicación anterior. Hace diez años me encomendaron la tarea de poner un gran conjunto de datos de 450 millones de objetos espaciales de GML en una base de datos espacial. Decidí probar MySQL y Postgis, en ese momento no había espacio en SQL Server y teníamos un ambiente de inicio pequeño, por lo que MySQL parecía una buena opción. Posteriormente participé en MySQL, asistí / hablé en un par de conferencias y estuve muy involucrado en la prueba beta de las funciones más compatibles con SIG en MySQL que finalmente se lanzó con la versión 5.5. Posteriormente participé en la migración de nuestros datos espaciales a Postgis y nuestros datos corporativos (con elementos espaciales) a SQL Server. Estos son mis hallazgos.

MySQL

1). Problemas de estabilidad En el transcurso de 5 años, tuvimos varios problemas de corrupción en la base de datos, que solo pudieron solucionarse al ejecutar myismachk en el archivo de índice, un proceso que puede durar más de 24 horas en una tabla de 450 millones de filas.

2). Hasta hace poco, solo las tablas MyISAM admitían el tipo de datos espaciales. Esto significa que si quieres soporte de transacciones no tienes suerte. El tipo de tabla InnoDB admite ahora tipos espaciales, pero no índices, que, dados los tamaños típicos de conjuntos de datos espaciales, no son muy útiles. Ver http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html Mi experiencia de ir a conferencias fue que el espacio fue una idea de último momento: hemos implementado la replicación, la creación de particiones, etc. pero no funciona con espacial EDITAR: En la próxima versión 5.7.5 InnoDB finalmente admitirá índices en columnas espaciales, lo que significa que ACID, claves externas e índices espaciales finalmente estarán disponibles en el mismo motor.

3). La funcionalidad espacial es extremadamente limitada en comparación con Postgis y SQL Server espacial. Todavía no hay una función ST_Union que actúe sobre un campo de geometría completo, una de las consultas que ejecuto con más frecuencia, es decir, no puede escribir:

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

que es muy útil en un contexto GIS. Select ST_Union(geom1, const_geom) from some_table , es decir, una de las geometrías es una geometría constante codificada que es un poco limitante en comparación.

4). No es compatible con rásteres. Ser capaz de hacer un análisis combinado de raster de vectores dentro de un db es una funcionalidad SIG muy útil.

5). No admite la conversión de un sistema de referencia espacial a otro.

6). Desde la adquisición por Oracle, espacial realmente ha sido puesto en espera.

En general, para ser justos con MySQL, admitió nuestro sitio web, WMS y el procesamiento espacial general durante varios años, y fue fácil de configurar. En el lado negativo, la corrupción de datos fue un problema, y ​​al verse obligado a usar tablas MyISAM está renunciando a muchos de los beneficios de un RDBMS.

Postgis

Debido a los problemas que tuvimos con MySQL, finalmente nos convertimos a Postgis. Los puntos clave de esta experiencia han sido.

1). Estabilidad extrema No hay corrupción de datos en 5 años y ahora tenemos alrededor de 25 cajas de Postgres / GIS en máquinas virtuales de centos, bajo diversos grados de carga.

2). Rápido ritmo de desarrollo: la trama, la topología y el soporte en 3D son ejemplos recientes de esto.

3). Comunidad muy activa. El canal de irg y la lista de correo de Postgis son excelentes recursos. El manual de referencia de Postgis también es excelente. http://postgis.net/docs/manual-2.0/

4). Se reproduce muy bien con otras aplicaciones, bajo el paraguas de OSGeo, como GeoServer y GDAL.

5). Los procedimientos almacenados se pueden escribir en muchos idiomas, además del plpgsql predeterminado, como Python o R.

5). Postgres es un RDBMS con todas las funciones, que cumple con los estándares, y que apunta a mantenerse cerca de los estándares de ANSI.

6). Soporte para funciones de ventana y consultas recursivas, no en MySQL, sino en SQL Server. Esto ha hecho que la escritura de consultas espaciales más complejas sea más clara.

Servidor SQL.

Solo he utilizado la funcionalidad espacial de SQL Server 2008, y muchas de las molestias de esa versión (falta de soporte para las conversiones de un CRS a otro, la necesidad de agregar sus propios parámetros a los índices espaciales) ya se han resuelto.

1). Como los objetos espaciales en SQL Server son básicamente objetos CLR, la sintaxis se siente hacia atrás. En lugar de ST_Area (geom) escribes geom.STArea () y esto se vuelve aún más obvio cuando encadenas funciones juntas. La caída del guión bajo en los nombres de función es simplemente una molestia menor.

2). He tenido varios polígonos inválidos que han sido aceptados por SQL Server, y la falta de una función ST_MakeValid puede hacer esto un poco doloroso.

3). Solo Windows En general, los productos de Microsoft (como los de ESRI) están diseñados para funcionar muy bien entre ellos, pero no siempre tienen el cumplimiento e interoperabilidad del estándar como objetivos principales. Si está ejecutando una tienda solo de Windows, esto no es un problema.

ACTUALIZACIÓN : habiendo jugado un poco con SQL Server 2012, puedo decir que se ha mejorado significativamente. Ahora existe una buena función de validación de geometría, existe un buen soporte para el tipo de datos Geography, incluyendo un objeto FULL GLOBE, que permite representar objetos que ocupan más de un hemisferio y soporte para Compound Curves y Circular Strings que es útil para precisión y compacidad representaciones de arcos (y círculos), entre otras cosas. La transformación de coordenadas de un CRS a otro todavía debe hacerse en bibliotecas de terceros, aunque esto no es un impedimento para la mayoría de las aplicaciones.

No he utilizado SQL Server con conjuntos de datos lo suficientemente grandes para comparar uno a uno con Postgis / MySQL, pero por lo que he visto las funciones se comportan correctamente, y aunque no tan completo como Postgis, es una gran mejora en las ofertas de MySQL .

Perdón por una respuesta tan larga, espero que algo del dolor y la alegría que he sufrido a lo largo de los años pueda ser de ayuda para alguien.

PostGIS es mejor porque se está convirtiendo en un estándar en aplicaciones SIG en la actualidad y PostGIS es gratuito. Es muy superior a MySQL en rendimiento

PostGis definitivamente. Este es el por qué.

  1. Postgres es muy superior a MySQL en rendimiento. El servidor es más tolerante a fallas, tiene herramientas listas para usar para balanceo de carga, almacenamiento en caché y optimización.
  2. PostGIS se está convirtiendo en un estándar en aplicaciones GIS.
  3. Es gratis.

Si está interesado en una comparación exhaustiva, le recomiendo "Comparar el SQL Server 2008 Spatial, PostgreSQL / PostGIS 1.3-1.4, MySQL 5-6" y / o "Comparar SQL Server 2008 R2, Oracle 11G R2, PostgreSQL / PostGIS 1.5 Spatial Características " por Boston GIS.

Teniendo en cuenta tus puntos:

  • Estoy familiarizado con el DBMS: la configuración de una base de datos PostGIS en Windows es fácil, el uso de la administración de PgAdmin3 también es sencillo.
  • Funciona bien con las consultas GIS: PostGIS es definitivamente el más fuerte de los tres, solo Oracle Spatial sería comparable pero está descalificado si se consideran los costos
  • Bajo costo: +1 para PostGIS seguro
  • No es antagónico con Microsoft .NET Framework: al menos debería poder conectarse a través de ODBC ( consulte la wiki de Postgres )
  • Compatible con R: no debería ser un problema con ninguno de los tres