SpatialDB Advisor

SpatialWare 4.9 Released

I wrote this blog originally on the 18th of August when the announcement first hit my intray. I kept it hidden until I was happy with the factualness of the information it contains.

Simon

Earlier this year MapInfo announced that the release of version 4.9 of SpatialWare for SQL Server (2005). (SQL Server 2000 is still a supported platform for 4.9.)

For those who didn’t know, SpatialWare is a spatial type system for the Informix Dynamic Server (IDS) and SQL Server relational databases and offers the following functionality:

• A complete integration of spatial and attribute data;
• Spatial indexing;
• Universal accessibility to geospatial data (eg execute spatial queries directly against SQL Server from inside Crystal Reports);
• Standard Database security;
• Scalability.
  The spatial data type enhancements are implemented entirely on the server side to exploit the scalability offered by Microsoft SQL Server;
• A rich set of spatial functions (over 150 spatial functions – and the richest set of functionality in its class);
• Standards compliance (aligns with ISO SQL-1999 and ISO SQL/MM standards);
• Programmatic interfaces.
  SpatialWare requires no middle tier to access the database, so you can access SpatialWare functionality through standard programmatic interfaces, such as Transact-SQL (T-SQL), JDBC, ODBC, ADO, and .NET.

SpatialWare also has a long and distinguished pedigree: See Background 1 at the end of this posting for some history.

The 4.9 release is specifically designed for Microsoft SQL Server 2005. It also continues support for SQL Server 2000. Spatialware’s original port to SQL Server was done for version 7.0 (which did not have a User-DefinedTypes – UDT – or User-DefinedFunction – UDF – framework) and then enhanced when SQL Server 2000 was released with UDT/UDFs. MapInfo even has a version for DB2 that is still used by a few customers but was withdrawn from sale many years ago. They withdrew their original Oracle version when Oracle themselves released their Sdo_Geometry implementation nearly 10 years ago.

However, one thing struck me in the MapInfo release announcement. There was a clause saying that Spatialware is “to be used with MapInfo applications”.

I fundamentally disagree with this piece of product marketing and positioning. (So, ESRI-ites take note: I even disagree with this bit of MapInfo marketing!) IHMO Spatialware should be have been pushed for its own virtues as a database extension product and not some MapInfo Professional geospatial data storage manager!

But, if MapInfo is wondering why there are, supposedly, not that many installations of Spatialware (See Google Trends for SpatialDBs or Interfaces), then perhaps it is because of product release announcements like this one!

So, MapInfo, other than congratulations on another release of an excellent product:

• Take the marketing shackles off the product!
• Promote it!
• Write an FDO provider for it!
• Stop hiding it!

Liberate the potential.

Background 1: SpatialWare

Spatialware comes from one of the great, seminal, products in the GIS business: System/9. MapInfo, as I understand the history, bought it from Unisys who in turn bought it from Wild Systems – the Swiss survey instruments company (now Leica GeoSystems). I first saw System/9 back at the University of New York (Buffalo) back in 1988 and, as a database person, I was “blown away” by its Spatial SQL. The only thing MapInfo seems to have done with the product, besides port it to databases other than Empress, is to continue the tradition of hiding it from view just as a private purchaser of a great work of art would do by only hanging it on the wall of his dining room!

ESRI and MapInfo
(The rest of this post is to compare and contrast MapInfo’s approach with what ESRI has announced they will do inside SQL Server 2005.)

ESRI Inc has announced that ”... [it] will implement an SQL API for Microsoft SQL Server. That work depends on the 2005 release of the database, since it will be the first to support abstract types [ADTs].” (emphasis added).

The ESRI claim that their implementation depends on the SQL Server 2005 release being the first to support abstract types allows us some space to discuss why especially given that MapInfo’s SpatialWare has been “inside” the database since SQL Server 7.0. By inside I mean that one can create triggers, build spatial processing into T-SQL scripts and run them inside the database without any need for a client GIS package. The database itself has been empowered to “understand” the spatial data type and its processing functions.

SQL Server 2000 had a User-DefinedTypes/User-DefinedFunction (UDT/UDF) framework which, while not perfect, was sufficient for the MapInfo team to build a good server-side implementation of a spatial type system. (See Background 2 at the end of this blog posting to read about the difference between UFT/UDF and ADT implementations.) MapInfo used this UDT/UDF framework to build SpatialWare for SQL Server 2000.

One major limitation of SQL Server 2000 which MapInfo had to content with was that the varbinary column, on which it has based its UDT implementation (and which is used to store the WKB encoded geometries), was limited to 8k. Since a lot of geometries are larger than this, it meant that T-SQL (cf Oracle PL/SQL) processing of geometries was convoluted (having to append/concatenate fragments when processing). At SQL Server 2005 a varbinary can be declared as “varbinary(max)”. (BTW these sorts of limits affect the ESRI ArcSDE development team with their SDEBINARY version. They are capable of being worked around!)

That ESRI has chosen the ADT approach is sensible and potentially more elegant, but they will have to cope with the sorts of limitations in SQL Server that MapInfo have had to content with.

Like what?

From information supplied to me by someone with more expertise than I do, the difference between UDT and ADT implementations only has practical value if you can inherit from the geometry object (see ISO Geometry Object Model): SQL Server doesn’t yet support inheritance (a limit the Oracle Spatial team had when it first released its Sdo_Geometry implementation on 8i). Also, the ESRI announcement alludes to another problem. In it they said that “The implementation will be a bit more complex than with Oracle, as ESRI will need to write its own index for the new data type”. This is understandable because even SQL Server 2005 doesn’t provide everthing needed to implement user-defined indexing.

Is the ESRI approach any better than MapInfo’s?

While it is said that ADTs provide a more efficient implementation than a UDT/UDF based approach and are “cleaner” from an object/method interface perspective, generally, this is not that visible to users like you and me, showing itself mainly as what looks like minor syntactic differences.

An example might help illustrate this difference.

With ADTs we get the area of a geometry via this type of query:

select geometry.area() from parcels

rather than this with UDT/UDFs:

select area(geometry) from parcels

(i.e. A minor syntactic difference.)

So, in summary, I think that, on MapInfo’s behalf, we can claim, categorically, that implementing an SQL/MM compliant spatial type within SQL Server never really depended that much on SQL Server 2005 being the first to support abstract types! Will MapInfo change SpatialWare’s implementation from UDTs to ADTs? Not for me to say. For there are a myriad of technical issues to be addressed that is beyond this small blog. For example, there is the issue for companies as to whether to code those implementations in traditional languages like C or C++ as against Managed Code such as C# (there are still performance issues with Managed Code inside SQL Server 2005 – particularly for double floating point operations – that mitigate against wholesale migration). This is compounded when cross-platform support is needed from a common code base (.NET languages on Solaris?).

But, in the end, does anyone care as long as it “just works”? And SpatialWare has a history of doing just that!

The first one, User-DefinedTypes (UDT – first seen in SQL Server 2000 but which other vendors have had for years) are often called in other systems called user-defined functions. These are new datatypes that are, in essense, calculated functions of existing datatypes. Thus SpatialWare is built by extending the existing varbinary datatype. (Hence why the breaking of size limits in SQL Server 2005 is so important.)

The second one, Abstract DataTypes (ADTs), is found mainly in Oracle, DB2, Informix and PostgresSQL. (I remember studying these during my final year in Computing Science at the University of Tasmania back in 1985! So, ADTs have a solid background in science.) ADTs offer a way to add totally new datatypes into a relational system, with their own data access methods (e.g., index structures). This is where the term post-relational or object-relation (O-RDBMS) comes from: the ability to “extend” the basic type system of a relational database to be more than numbers, dates and strings. Though, even ADT developers are forced to use more fundamental data types for their implementation (eg Oracle’s Sdo_Geometry uses Oracle Objects and VArrays).

Using either mechanism (UDT or ADT) we get new full-text, geospatial, image etc capabilities incorporated into existing database products.