MySQL vs Microsoft SQL vs SQL Anywhere
Comparing Geodatabases
We, at Disy, are big on Geodata (Duh!). Since we are already in league with the geodatabase giants, Oracle and PostgreSQL, we rounded up three currently popular geodatabases in the market that our beloved software doesn’t entirely support and decided to dig up on what makes them special.
The result is this blog post—a comparison of the geospatial capabilities of MySQL, Microsoft SQL Server (MSSQL) and SQL Anywhere (Sybase). So let’s get started!
A Little History
MySQL is an open-source and server-based database that is available under both free and commercial licenses. Associated with the likes of Facebook and Twitter, MySQL boasts to be the world’s most popular database in use for open-source web applications.
SAP’s commercial database SQL Anywhere—commonly mistaken for Sybase or Sybase Adaptive Server, understandably, because of its previous proprietor—is a file-based database supporting the standard operating systems like Linux and Windows. It also supports mobile operating systems like iOS, Android und BlackberryOS which makes it all the more interesting for us. (Well, you remember Cadenza Mobile, right?!)
Microsoft SQL Server (MSSQL), currently the third most popular relational database, is available only under (drum rolls, you guessed it!) Windows, although this will change by mid-2017. It was originally jointly developed by Microsoft and Sybase as variant of Sybase SQL Server (Surprise). Along with commercial licenses, a fully functional free-version is also available with limited storage, memory and number of processors.
Things that one would normally look for in any geo-database are:
- the types of geometries or data types it supports,
- the spatial operations it allows to be performed on these geometries,
- how easy it is to get the data in the database (e.g Data formats, import or export possibilities)
- and how well it handles the data (e.g. Querying, indexing, etc.)
And these are the important factors on which this comparison is based.
Supported Geometries
Let’s get one thing straight: All of the three databases in discussion support the basic geometry types—Point, Line String and Polygon. The table below shows other spatial data types and whether they are supported by the three databases.
| Data Type | MySQL | SQL Anywhere | MSSQL |
|---|---|---|---|
| Geometry | |||
| MultiPoint | |||
| MultiLinestring | |||
| MultiPolygon | |||
| GeometryCollection | |||
| Multisurface | |||
| Multicurve | |||
| Surface | |||
| Curve Polygon | |||
| Circular String | |||
| Compound Curve | |||
| Polyhedral Surface | |||
| Geography | |||
| FullGlobe |
Being the database to only recently adopt and provide spatial functionality, MySQL stops at supporting a few multi-geometries (Multipoint, Multiline and Mutlipolygon) along with the Geometry Collections.
SQL Anywhere, on the other hand, natively supports even complex (multi) geometries including Curves, Surfaces and Circular strings.
MSSQL takes the middle ground by allowing Curves and Circular Strings but not Surfaces. But the database goes the extra mile by introducing a new native Geometry type called FullGlobe. FullGlobe allows for geometries covering more than half the hemisphere of the globe.
The fact that MSSQL also supports the Geography datatype, which was introduced in PostGIS, is noteworthy. With the exception of MySQL all databases support the storage of 3D coordinates.
Since Cadenza does not yet support any of the complex geometry data types, the question still remains as to whether there is a need for the multitude of geometries in generic every-day applications using 2D maps covering one to few countries at most.
Spatial Functions
MySQL offers only about 80 functions related to spatial data,
of which only about 30 functions perform any real spatial analysis.
These include operations such as Buffer, Intersection and Union.
The rest of the functions are either to generate geometries from coordinates
or to convert one spatial data format to another.
Spatial queries like Contains, Within or Touches
that are used to determine the relation between two geometries or spatial objects
are performed used Minimum Bounding Rectangles (MBR).
MSSQL and SQL Anywhere have more to offer here
with over 80 and 100 spatial operations, respectively.
Both include analytic functions
such as Union, Difference, calculating lengths, distances and areas etc.
However, SQL Anywhere documentation
lists the following as unsupported spatial operations/functions:
ST_BufferST_LocateAlongST_LocateBetweenST_SegmentizeST_SimplifyST_Distance_SpheroidST_Length_Spheroid
The two highlighted functions, Buffer and Simplify, are interesting to us
because the former function is currently offered in Cadenza
and the latter is commonly used to optimize the visualization of spatial objects.
Supporting SQl Anywhere could hence mean
that Cadenza would have to compensate for (or compromise on) the lack of the functionality.
In regard to the 3D aspect of geodata both MSSQL and SQL Anywhere offer one or two functions to verify whether a geometry is 3D and to add a Z coordinate if required.
With PostgreSQL and Oracle offering close to 300 functions including both 2D and 3D, MSSQL and SQL Anywhere do not provide significant challenges while MySQL is coming nowhere close to the competition in this area.
Importing (or Exporting) Your Geodata
So how do we actually get our geodata into these databases?? The following table shows the native functions offered by the three databases to import and export spatial data.
| Data type | MySQL | SQL Anywhere | MSSQL |
|---|---|---|---|
| Well Known Text (WKT) | / | / | / |
| Well Known Binary (WKB) | / | / | / |
| Extended Well Known Text (EWKT) | / | ||
| Extended Well Known Binary (EWKB) | / | ||
| GML | / | ||
| KML | |||
| ESRI Shapefile | |||
| GeoJSON | / | * / * | |
| SVG |
Clearly, MySQL supports only 3 datatypes, all of which are text-based. It is relatively easy to import the spatial data unless it is in the Shapefile format. In that case, a data conversion tool such as FME is required. People biased against FME (I totally understand!!) can also use translator libraries such as GDAL’s OGR2OGR or the PHP-script, Shp2MySQL to do the job.
SQL Anywhere stands out in this round of comparison by offering the highest number of native functions to convert between the data formats. Although, it is worth mentioning that it took an awfully long time to import a Shapefile from Cadenza Demo Repository into this database. The reason was that SQL Anywhere verifies whether all the imported geometries lie within the extent (bounding box) defined in the spatial reference system, which is in turn provided by the SRID in the shapefile. The workaround was to define a custom reference system with the extended bounding box coordinates and to import the shapefile again with the new SRID (which was not all that great of a resolution).
Surprisingly, MSSQL is similar to MySQL in this case, supporting only the file formats of WKT and WKB. Information from GeoJSON formats can still indirectly be utilized by storing them as normal JSON datatype in the database and using OPENJSON table-function to access the information in the object (hence denoted by a * in the comparison table).
Spatial Index Support
In general, indexes help faster retrieval of data and spatial indexes enable exactly that but for geodata. Some of the common indexing methods are R‑Tree and B‑Tree with the former being the most preferred for spatial indexes.
In spite of its limited prowess in spatial functionality and data types, MySQL is in league with PostgreSQL and Oracle by supporting R‑Tree spatial indexes. It also allows non-spatial indexing on spatial data columns which is suitable for “exact value look-ups” but is not suitable for retrieving values within a range (“range scans”).
In SQL Anywhere, although there are commands available for creating an index on database table columns storing spatial information, the type of index used and the method of implementation is unfortunately not well-documented.
MSSQL takes a different approach to spatial indexing by implementing B‑Tree algorithms using the concepts of Tessellation and Grid-Hierarchy.
The image shows how a spatial area is contained within a grid and each cell in the grid is in turn turned into another grid. The level of division and number of cells in a grid can be customized. A more detailed explanation of Tessellation and Grid-Hierarchy can be found in the Spatial Indexes Overview article on MSDN (search for the terms).
A simple test was conducted in all the three databases where a single polygon‑geometry was intersected with all the countries of the world. All three databases returned results within a second. Extensive tests were not performed at the moment.
The Verdict
So when it comes to choosing the right database for your geospatial needs, the answer depends on your requirements:
- the extent of spatial functionality required
- the frequency of data conversions
- ease of use
- the cost of the database itself
There might also be other factors in play such as legacy systems already in place.
To sum it all up: MySQL, though coming up short in the advanced features area, provides all the basic support and functionality for geodata including spatial indexes that one can make do with. Whereas, SQL Anywhere with its myriad of data types, specialized GUI client, and native export and import functions is definitely a strong player. This gives an excuse to overlook the workarounds required to use certain functionalities in this database. Other than the support for only Windows operating systems (which is also changing in 2017) and rather few native data import functions there isn’t much to nit-pick over Microsoft SQL Server.
Based on this short comparison, it is clearly safe to say that when it comes to geodatabases, the giants—Oracle and PostgreSQL—still prevail over their dwarf and yet worthy competitors—MySQL, SQL Anywhere and Microsoft SQL.
Share via
