MySQL vs. PostgreSQL + PostGIS

MySQL vs. PostgreSQL

2. Mysql

   Relatively young, appearing for the first 1994 years. It claims to be the most popular open source database. MySQL is the M in the lamp (for web development packages, including Linux, Apache, and Perl/php/python). Most applications built on the lamp stack use MySQL, including those that are well-known applications such as WordPress, Drupal, Zend, and phpBB.

   To begin, MySQL was designed to become a fast Web server backend, using a fast indexed sequence access method (ISAM), which does not support acid. After an early and rapid development, MySQL began to support more storage engines and achieved acid through the InnoDB engine. MySQL also supports other storage engines, provides temporary table functionality (using the memory storage engine), implements a high-speed read database through the MyISAM engine, and other core storage engines and third-party engines.

   Now there are more databases to choose from, based on the original MySQL code, because several of the core MySQL developers have already released the MySQL branch. One of the original MySQL creators, Michael "Monty", Widenius seems to regret selling MySQL to Sun, and has developed his own MySQL branch mariadb, which is free, based on the GPL license. The branch drizzle created by well-known MySQL developer Brian Aker has been extensively rewritten, especially for multi-CPU, cloud, network applications and high concurrency.

   Currently only data tables for the MyISAM engine support the storage of geospatial data. MySQL for the spatial database itself is supported, but not very comprehensive support, in fact, the professional space database is not postgis, and the function of the map is not very high, if there is no high demand for analysis capabilities, The choice of MySQL for spatial data storage can be achieved on read requirements. At the same time the MyISAM engine has a high insert, query speed, but does not support transactions, which is also a major drawback.

4. PostgreSQL

   PostgreSQL advertises itself as the most advanced open source database in the world. Some of PostgreSQL's fans say it is comparable to Oracle and has less expensive price and arrogant customer service. It has a long history, originally developed in 1985 at the University of California, Berkeley, as a successor to the Ingres database.

   PostgreSQL is a fully community-driven open source project maintained by more than 1000 contributors worldwide. It provides a single, full-featured version, unlike MySQL, which offers a number of different community, commercial, and Enterprise editions. PostgreSQL is based on a free Bsd/mit license, and the organization can use, copy, modify, and redistribute the code, only to provide a copyright notice.

   Reliability is the highest priority for PostgreSQL. It is known for rock-solid quality and good engineering, supporting high-transaction, mission-critical applications. PostgreSQL's documentation is excellent, provides a large number of free online manuals, and provides an archived reference manual for older versions. PostgreSQL community support is great, and business support from independent vendors.

   Data consistency and integrity are also high-priority features of PostgreSQL. PostgreSQL fully supports the acid feature, providing a powerful security guarantee for database access, leveraging enterprise security tools such as Kerberos and OpenSSL. You can define your own checks to ensure data quality according to your business rules. Among the many management features, point-in-time Recovery (PITR) is a great feature, a flexible, high-availability feature that provides the ability to create hot backups and snapshots and restores for failed recoveries. But this is not the whole of PostgreSQL, and the project offers several ways to manage PostgreSQL for high availability, load balancing and replication, so you can use features that suit your specific needs.

6. Platform comparison
   MySQL and PostgreSQL appear on some high-traffic Web sites
   Mysql:slashdot, Twitter, Facebook
   Postgresql:yahoo uses a modified PostgreSQL database to handle billions of events per day, and Reddit and Disqus

   MySQL and PostgreSQL can run on multiple operating systems, such as Linux, Unix, Mac OS x and Windows. They are open source and free, so the only cost to test them is your time and hardware. They are flexible and scalable, and can be used on small systems and large distributed systems. MySQL is going a step further in one area than PostgreSQL, which is that its tentacles extend to the embedded realm, which is achieved through LIBMYSQLD. PostgreSQL does not support embedded applications and remains on the traditional client/server architecture.

   MySQL is often thought of as a fast database backend for websites and applications, capable of fast reads and a large number of queries, but is less than satisfactory in terms of complex features and data integrity checks. PostgreSQL is a serious, well-functioning database for transactional enterprise applications that supports strong acid characteristics and many data integrity checks. Both of them have very fast speeds on certain tasks, and MySQL has a much different behavior than the other storage engines. The MyISAM engine is the fastest because it performs very little data integrity checks and is suitable for sites with more back-end reads, but it is a disaster for read/write databases that contain sensitive data because the MyISAM table can eventually become corrupted. MySQL provides the tools to fix MySQL tables, but for sensitive data, innodb that support the acid feature is a better choice. In contrast to

   , PostgreSQL is a fully integrated database with only a single storage engine. You can improve performance by adjusting the parameters of the postgresql.conf file, or you can adjust queries and transactions. The PostgreSQL documentation provides a very detailed introduction to performance tuning. The

   MySQL and PostgreSQL are highly configurable and can be optimized for different tasks. They all support the ability to add additional functionality through extensions.

8. Summary
   PostgreSQL, with its reliability, good at protecting data, and a community project, will not fall into the cage of a maker. MySQL is more flexible and offers more options for tailoring to different tasks and faster to get started.

Current status of spatial data storage management

Because spatial data has the characteristics of spatial location, unstructured, spatial relation, classification coding and mass data, the general commercial database management system is difficult to meet the requirements.

In order to improve the ability of database management system (DBMS) to manage spatial data, it has appeared at home and abroad: Mixed management system of file and relational database, all-relational spatial database management system, relational database + spatial data engine, extended Object Relational database management system, and object-oriented spatial database management system and other solutions. At present, domestic and foreign more popular mainly focus on "relational database + Spatial data Engine", "Extended Object Relational database" two aspects.

Therefore, the relational database is only a container for storing spatial data, and the spatial data Engine is the conversion channel of spatial data in and out of the container. Typical representations of this type of system are ESRI's ArcSDE and MapInfo Spatialware. The advantage is that the access speed is fast, supports the common relational database management system, the spatial data is accessed by BLOB, can cross database platform, and integrates tightly with the specific GIS platform, and the application is flexible. The main disadvantage is that the space operation and processing can not be implemented in the database kernel, the data model is more complex, the expansion of SQL is difficult, it is not easy to realize data sharing and interoperation.

Extended Object Relational database management system is a solution for managing spatial data developed by database vendors. Because the relational database is difficult to manage the unstructured data (also including the spatial data), the database vendor has developed the object-relational database management system by drawing on the object-oriented technology.

This system supports the definition of abstract data type (ADT) and its related operations, which allows users to increase spatial data types and related functions, thus transferring spatial data types and functions from the middleware (spatial data Engine) to the database management system. Instead of programming with a dedicated interface to the spatial data engine, customers can manipulate spatial data using the standard extended SQL language of the increased spatial data types and functions.

This type of product supports space expansion with Oracle spatial Oracle, IBM DB2 Spatial Extender, and the spatial datablade of Informix. The advantage is that the management of spatial data integrates with the Universal database system, the spatial data can be accessed by object, the space operation and processing in the database kernel, the extension of SQL is convenient, and the data sharing and interoperability are easy to realize. Its shortcomings are mainly manifested in the difficulty of implementation, compression data is difficult, the current function and performance and the first type of system there is still a gap.

At present, the most excellent database software in the field of open source space information software belongs to PostgreSQL database, and the space object extension module built on it PostGIS makes it become a real large space database. PostGIS adds the ability to store and manage spatial data on object-relational database PostgreSQL, equivalent to the spatial portion of Oracle. The biggest feature of PostGIS is that it conforms to and implements some specifications of OpenGIS, and is the most famous open source GIS database.

PostgreSQL + PostGIS: Extended Object-relational database management system

Some basic collection entity types have been defined in PostgreSQL, including points (point), lines (line), Segment (LSEG), Square (BOX), Polygon (POLYGON), and Circle (circle), and so on; PostgreSQL defines a series of functions and operators to implement the operations and operations of the geometry type, while PostgreSQL introduces spatial data index R-tree.

While PostgreSQL provides the above features to support spatial data, its spatial characteristics are difficult to meet the requirements of GIS, mainly manifested in: the lack of complex spatial types, no spatial analysis, no projection transformation function provided. In order to make PostgreSQL better provide space information service, PostGIS came into being.

2. The role of PostGIS
   PostGIS is an extension of the object-relational database system PostgreSQL, PostGIS provides the following spatial information service functions: Spatial objects, spatial indexes, spatial manipulation functions, and space operators. At the same time, PostGIS follows the OpenGIS specification.

3. The properties of

4. PostGIS
   PostGIS Support all spatial data types, including point, Line (LINESTRING), Polygon (POLYGON), multipoint (MULTIPOINT), Multi-line (multilinestring), poly (Multipolygon), and collection object Set (GeometryCollection). PostGIS supports all object expression methods, such as wkt and WKB. The

   PostGIS supports all data access and construction methods, such as Geomfromtext (), Asbinary (), and Geometryn (). The

   PostGIS provides simple spatial analysis functions such as area and length, as well as other functions with complex analysis functions, such as distance. The

   PostGIS provides support for metadata, such as Geometry_columns and Spatial_ref_sys, and PostGIS also provides support functions. such as Addgeometrycolumn and Dropgeometrycolumn. The

   PostGIS provides a series of two-tuple predicates (such as contains, within, overlaps, and touches) to detect spatial relationships between spatial objects, while returning a Boolean value to characterize the relationship between objects. The

   PostGIS provides spatial operators, such as union and difference, for spatial data operations. For example, the union operator blends the boundaries between polygons. Two overlapping polygons form a new polygon through the Union, and the new polygon has a boundary of two polygons with the largest boundary.

6. Additional features of the PostGIS
   Database coordinate transformations:
   The geometry types in the database can be transformed from one projection system to another through the transform function.

   Sphere length Operation:
   Collection types stored in a common geographic coordinate system if no coordinate transformations are performed, the coordinate transformations provided by OpenGIS make it possible to calculate the degree of accumulation types.

   Three-dimensional geometry type:
   The SFSQL specification is for a two-dimensional collection type only. OpenGIS provides support for the three-dimensional collection type, which uses the input collection type dimension to determine how the output behaves. For example, even if all of the geometries are stored in three dimensions, the purely two-dimensional intersections are usually returned in two-dimensional form. In addition, the ability to convert geometric objects between different dimensions is also provided.

   Spatial aggregation functions:
   In a database, a clustered function is a function that performs all data operations on a property column. For example, sum and average,sum are the sum of the data for a relational attribute column, and average is the average of the data for a relational attribute column. In this correspondence, the spatial aggregation function performs the same operation, but the object of the operation is spatial data. For example, the aggregate function EXTENT returns the largest package rectangle in a series of features, such as "Select EXTENT (GEOM) from ROADS", the result of which is to return all the bounding rectangle boxes in this data table.

   Raster data type:
   PostGIS provides storage for large raster data objects through a new piece of data type. The slice consists of the following parts: Wrap rectangle, SRID, type, and a sequence of bytes. By controlling the size of the slice below the database page value (32x32), fast, immediate access becomes possible. A large picture is also stored in a database by cutting it into 32x32 pixels.

Summarize:

MySQL and PostgreSQL are open source relational databases, and both support standard SQL, which is similar to the cost of learning. MySQL has a slight upper hand in flexibility, reading on MySQL as a simple attribute data is fast and reading support for spatial data is friendly, but if you do other things with spatial data, MySQL is hard to do at this point.

PostgreSQL is completely free, and many communities are maintaining it, and PostgreSQL is much more powerful than MySQL in many ways, such as complex SQL execution, stored procedures, triggers, and indexes. While PostgreSQL is multi-process, and MySQL is a thread, although concurrency is not high, MySQL processing speed, but when the concurrency is high, for now multicore single machine, MySQL thread can not fully utilize the CPU capacity, The overall processing performance of MySQL is not as good as PostgreSQL, so in general, PostgreSQL is better than MySQL in terms of stability and performance. At the same time, PostGIS, as a separate extension object of PostgreSQL, has strong support for spatial data, excellent analytical capability of spatial data, and is in line with the trend of "extended object-relational database management system" to carry out spatial data management.

MySQL vs. PostgreSQL + PostGIS