Function |
Mysql spatial extension |
PostGIS |
Spatial Index |
Only MyISAM supports the R tree index, but InnoDB does not. |
GIST tree index (variant of the R tree) |
Supported space types |
Only two-dimensional data |
Two-dimensional, three-dimensional, and curve |
Spatial operation functions |
Limited Space Functions |
Basic implementation of space operation functions defined by OGC |
For example, to find the points in a blue polygon, mysql spatial extension can only find the points in the smallest outsourcing rectangle (red box), while postgis can find the points in any polygon. # FormatImgID_0 # |
Spatial projection |
Not Supported |
Supports multiple commonly used projection Coordinate Systems |
For example, you want to find the distance between two points. MySQL Spatial can only calculate the Euclidean Spatial distance, while PostGIS can calculate the real Spatial distance in different projection coordinate systems. |
Transaction support |
Not Supported |
PostGIS provides a series of long transaction support, which can effectively support complex spatial analysis functions. |
Query Efficiency |
Loading Speed: MySQL> PostGIS (Transaction) Create a spatial index: MySQL <PostGIS (diff split algo) Query: MySQL PostGIS (different query results have different characteristics) |
GIS system usage |
Use less |
For example, the database background of openstreetmap is Postgresql + Postgis. |
Summary: If you only use simple GIS or LBS applications, the spatial extensions provided by MySQL can basically meet the requirements. However, if the required functions are more complex, the functions provided by MySQL spatial extensions may not be enough. In this case, Postgresql + postGIS may be more suitable.