MySQL 4.1 introduces spatial functionality in MySQL. This article describes some of the uses of spatial extensions in a relational database, how it can be implemented in a relational database, what features are present in MySQL and some simple examples.

Often the spatial functions are called geographic information system (GIS) functions, because GIS applications are the most obvious use-case for the spatial functionality. The spatial functions can be used both to provide a means to organize GIS data together with more traditional types of data and to represent non-GIS data with a spatial attribute.

GIS applications then and now

A GIS (geographic information system) stores and looks up objects which have one or more spatial attributes, such as size and position, and is used to process such objects. A simple example would be a system that stores addresses in a town using geographic coordinates. If this rather static data was then combined with other information, such as the location of a taxi-cab, then this data could be used to find the closest cab to a certain location. Many GIS applications are very specialized, in areas such as the mapping of real-world objects, map creation and meteorology.

Among the many attributes of a general GIS is that the objects may have multiple dimensions, and that complex shapes are supported. Typically two-dimensional objects with nearly unlimited complexity need to be supported.

Another attribute of a general GIS is that it supports combining objects and looking for different types of overlaps, such as looking for points that are contained within a given geometrical object. Lastly, a GIS provides a means of organizing objects in layers, i.e. REGIONs inside DISTRICTs.

A good example of an application where GIS is of importance and which also has real-world implications is in the area of meteorology. When it comes to producing weather maps we see every day on the TV screen, meteorology is a complex science. Very advanced and high-powered IT systems that are concerned with the actual number-crunching are combined with large databases. The issue is not that data is not available, the issue is what data to use and how, and how the different types of data are matched. The data from the number-crunching systems is supplied in the form of large files with multi-dimensional arrays of prognosticated data. The issue now is, how can a specific data object be matched with another, and provide a relevant prognosis of the weather in the coming five days.

In other words, we have data covering at least three dimensions (two-dimensional geography plus a height of a measurement for example, maybe combined with a time dimension).

What is obvious from the above discussion is that a database that only works on simple data types, such as INTEGER or DECIMAL, will just not be good enough for this type of application. The geographical shapes might be any type of polygon that then needs to be matched with some other object, i.e. what is the total area of two objects (which is not necessarily the sum of the areas of the two objects, as they might overlap).

The traditional solution to all this has been to use specialized database systems that use proprietary spatial indexing, and proprietary interfaces. These systems are complex though, and not only that, any GIS stores some non-GIS data (for example, a meteorological database might store a temperature measurement at a given geographical location, height and time as a DECIMAL). And for non-GIS data, SQL has become the standard. As applications for GIS have expanded beyond what goes on in labs and high-powered scientific institutions and are found in more traditional applications, such a taxicab scheduling systems, the need for a more standardized solution has exploded. Another factor here is the availability of inexpensive and easy to use GPS systems which may be combined with mobile communication systems, so that getting a real-time position from, a taxi-cab, a ship on the sea or a police car is no longer complex or expensive.

OpenGIS - Linking SQL with GIS functionality

The desire to combine SQL with GIS was initially driven by the developers of GIS applications desiring to expand into other areas, more than by SQL database vendors getting into the GIS market.

The driving organization behind opening up GIS to a broader market and making GIS technologies available everywhere the Open Geospatial Consortium (OGC). OGC has been around for about 10 years and is a non-profit organization that works on many areas of GIS and provides specifications of interoperability with many other standards. One such standard that OGC has provided is a specification for interoperability SQL databases.

This specification, in short, defines extensions to a SQL based relational database to allow for GIS objects and operations. There are four important areas here:

• Data types. There needs to be data types to store the GIS information. This is best illustrated with an example, a POINT in a 2-dimensional system.
• Operations. There must be additional operators to support the management of multi-dimensional objects, again, this is best illustrated with an example, a function that computes the AREA of a polygon of any shape.
• The ability to input and output GIS data. To make systems interoperable, OGC has specified how contents of GIS objects are represented in binary and text format.
• Indexing of spatial data. To use the different operators, some means of indexing of GIS data is needed, or in technical terms, spatial indexing.

In addition to the above, there is also a need for GIS metadata, and in some cases for using different coordinate systems. MySQL currently supports a planar coordinate system. The other major coordinate system in use is the geocentric one, i.e. a coordinate system on the Earth's surface, which is not yet supported by MySQL.

Non-GIS use for MySQL spatial extensions

There are uses for spatial extensions outside of the pure GIS world. Spatial data is not limited to maps or to the Earth's geography. The definition of spatial data and operations is wide, and even though MySQL follows the OpenGIS specification to a large extent, this specification does not limit the use of spatial data to GIS applications.

Any type of data that has more than one dimension can be treated as a spatial entity. The specification does not limit the axis on the coordinate system to any particular unit, such as inches or centimeters, and each axis does not necessarily have to represent the same unit: they are just numbers. Of course, certain functionality assumes that the X and Y axis have similar properties, such as the Area() function.

Fact is, a lot of real-world data has two dimensions if we look at it this way, in particular if we assume that one dimension is a date. For example we could store the maximum and minimum price for a stock as a POINT, where the X axis is the date and the Y axis is the price. We can then use the spatial functions to check for overlaps and intersections with other stocks.

The OpenGIS® Simple Features specifications for SQL

This specification is what is used as the ground for almost all implementations of GIS-functions within an SQL-based relational database. This standard defines the data types, operations, input and output format, functions and much more. This is the standard that is followed by almost all SQL databases with spatial extensions, including MySQL.

The data types start from the most generic at the top of the hierarchy, GEOMETRY, to a number of specific types, such as POINT and LINESTRING. Some of the data types are "abstract" in the sense that you cannot create objects of this type, such as the GEOMETRY type. This does not mean that you cannot have a column of the type GEOMETRY, just that you cannot have any value in that column of the type GEOMETRY, but you can have values of any other spatial type in that column. Among the abstract object types, only GEOMETRY can be used as a column type.

Among the functions that can be performed on spatial objects are functions that evaluate the proximity of objects, such as Overlap(), functions that combine two spatial objects to create another spatial object, such as Envelope() and functions that perform conversion to and from text and binary formats, such as AsText() and GeomFromText(). As any spatial object can be treated as a GEOMETRY object, any function that operate on a GEOMETRY can operate on any other type of spatial object, such as a POINT, which doesn't necessarily mean that this operation always is meaningful.

The specification includes the definition of two formats for external representation of spatial data, WKB (Well Known Binary) and WKT (Well Known Text). This allows data to be exported and imported in binary and text formats. To complement these formats, there are functions that convert between the WKB and WKT formats and all of the spatial data types.

Spatial Indexing

Spatial data may be indexed, just like other data in MySQL. To make this effective, a special type of indexing is used for spatial data called R-tree ("R" stands for Region) indexing. The involves organizing the minimum bounding rectangle (MBR) of the spatial objects in a tree structure that is then used by the different spatial functions. There are a few variations of R-tree indexing, MySQL uses R-trees with quadratic splitting, which is one of the standard methods of building an R-tree index.

An R-tree index is similar to a B+-tree in many ways, and organizes the indexed nodes in a hierarchy where the nodes in the index represent the MBR of the objects in the node. The leaf nodes in the index contain references to the row that contain the data, just like a B+-tree index.

Current Spatial features in MySQL

In the current implementation of spatial extensions in MySQL, parts of the OpenGIS Simple Features have been left out. Among these are:

• Functions - Some functions are missing, among them some that deal with creating new spatial data values from existing ones, such as Union() (not to be confused with the SQL UNION operator) and Intersection() (not to be confused with the Intersect() spatial function).
• GIS Metadata - The OpenGIS Simple Feature specification defines certain metadata tables for GIS data.
• SRID - A Spatial Reference Identifier (SRID) defines the properties of the coordinate system. MySQL stores the SRID of any spatial objects and it can be extracted, but it is not used and the coordinate system is always supposed to be planar.

Except the above, all of the features of the OpenGIS Simple Features specification are included in MySQL 4.1 Spatial Extensions, support for the missing features will be added in future MySQL versions.

Intel is a brand synonymous with microprocessors, the little chips that give computers the power to process data. And in technology markets with millions of independent consumers, if brand power and technical power get into a mutual reinforcement loop, it is quite some force. That's the 'Intel inside' story.

AMD, short for Advanced Micro Devices, is a brand synonymous with. . . nothing particularly, though it is a brand of chips to rival Intel's. And in an Indian market with millions of independent consumers, increasing numbers have been evaluating AMD's proposition with an independent mind. And they're beginning to bite in. Could this become an "AMD insight" story? Read on.

End of Intelopoly

The US-based AMD made its first tentative foray into India four years ago. And according to IDC data, it put its chips in as many as 15 per cent of the 3.5 million computers sold in 2004-05 in India (that includes desktop PCs, servers and laptops). The winning combination?

Aggressive pricing, technological innovation and quick expansion of distribution channels. Delhi-based Skoch Consulting, which tracks the market, expects AMD to have at least a fifth of the market by 2006-07 -- despite rearguard action from Intel to protect its flanks.

Ajay Marathe, president, AMD India, wants a bigger slice than that. "Our target is to grow two to three times higher than the overall market growth rate. We are looking at growing at 70-100 per cent, annually." That could mean over a quarter of the market by 2006-07.

Should Intel worry? AMD has been rapidly expanding its offer basket to include almost everything Intel offers. In fact, having secured its presence in regular computers, AMD is now aiming at the lucrative server market, just 76,000 units strong but worth $295 million last year. AMD wants a 30-per cent share of this segment.

For his part, Surendra Arora, director, Intel India, does not want to display any more concern than necessary. "Let the customer judge the value of Intel," he says.

In some ways, the AMD challenge in India is a reflection of the global market scenario, where the challenger has already grabbed over 40 per cent of the consumer desktop market and around 8 per cent of the server space (and has sued Intel in the US for the alleged use of strongarm tactics to keep PC makers in exclusive loyalty). But the Indian experience is not quite the same.

Price Value Flux

So what has AMD done in India? It has stolen the lead in taking the customer up the power curve. The technical story focuses on AMD's innovation in offering a 64-bit processor at roughly the same price of Intel's 32-bit alternative.

For the uninitiated, most PCs are loaded with 32-bit chips, which means they can process 32 bits of data every second. AMD's 64 bit offering is not only four times faster, it offers more Random Access Memory (RAM), the instant memory that is needed to perform various tasks while the computer is on.

That's part of a twofold strategy, says Marathe. First, score a technological edge with 64-bit chips, segment by segment (desktops in 2001, servers in 2003 and laptops in 2005), thus upstaging Intel's upgradation curve (it launched desktop 64-bit chips only in March, and for servers only last month, and has left laptops unattended).

And then, turn up the knob on the sales pitch. Its advice to customers has been simple: why opt for weak old technology when extra power is already within reach?

For the customer willing to listen to a story of finer algebraic detail, AMD has its "dual core technology" to talk about. This offers virtually two processors in one, which, working in conjunction, delivers more than twice the performance.

The brand's popular image, however, continues to be that of a price warrior. Chip to chip, AMD sells at list prices about 20-30 per cent lower than Intel, enabling PC makers to meet lower price points in the Indian market.

Listen to Ravi Pradhan, country manager, India, Via Technologies India, part of a Taiwan-based firm that has a tie-up with AMD to bundle processors with its chipsets. "Of course they're cheaper," he says, "without compromising processing abilities, and that is why many PC makers are using AMD chips to offer PCs at Rs 10,000."

The idea of this price point, the magic figure of Rs 10,000, is to touch off an explosion in volumes. To this end, AMD is also working with HCL Infosystems.

"Nobody is making money at this price," admits Marathe, "as all the vendors have reduced their prices to make it possible. But if you get demand of 5 million units, you can make money."

This is part of the "light computer" vision of networking the country with cheap lightly-loaded machines. These would be machines that are just adequate for internet surfing, but which can also draw on remotely located heavy resources if needed.

Selling cheap machines requires deeper market penetration, and for this, AMD intends doubling its channel partners to 6,000 (across 300 cities instead of the current 150) within the next 12 months -- which it claims would put it on par with Intel's distribution.

Intel's Composure

Intel, meanwhile, has not lapsed into complacency. "We will do everything to win back customers we might have lost," vows Arora, who remains alert to any possible threat to Intel's long-running dominance.

Among Intel's big starting advantages has been the calibration of its technology to suit the software made by Microsoft for Windows, the standard man-machine user interface (hence the term "Wintel monopoly"). Intel and Microsoft tend to operate in alliance, taking customers up the technology upgradation curve.

So, argue Intel supporters, what's the point of a 64-bit machine so long before Windows Vista comes out (scheduled next year) to make appropriate use of 64 bit processing? Says Intel's Arora, "We come out with products when the market is ready."

At the market's lower end, Intel prefers to talk of monthly-outgo affordability rather than product price points. Says Arora, "We are not looking at it as a Rs 10,000 PC, but as a question of affordability: how much does he have to pay per month as an EMI to own a PC?"

Through its 'My First Intel' scheme, the company has tied up with public sector banks to offer loans at rates as low as Rs 300 a month. It is working out deals for bundled offerings too. So price is not a very potent weapon in AMD's arsenal after all.

Moreover, as Skoch's CEO Sameer Kochhar sees it, the price edge gets blunt as you go from the Rs 10,000 level to mid-range PCs for around Rs 25,000, where a saving of about Rs 1,000 does not easily get a switch from Intel to AMD. And the price-point game can drain AMD of cash.

"They are in the bleeding edge of the market," says Kochhar, "those who want to make inexpensive machines buy AMD, they have a lead there but you don't make money. Their challenge is to move up the value chain."

But in the server market, AMD is finding the going steep, according to market watchers. Intel's relationships with corporate and government bodies are proving quite durable, though government contracts no longer specify Intel supplies.

So, realistically speaking, can AMD pose more than a perfunctory challenge to Intel?

Marathe is convinced it can. He explains the server segment's poor showing so far, for example, as an issue of selling a product suited to Indian market peculiarities. The machines used in India as network servers tend to be upgraded desktops.

"We did not have a product here earlier. Intel was selling Pentium 4 in this market. But now we have introduced the Optron 100, which is a server-grade processor, and reliable -- with a price tag similar to a desktop's."

While Intel has good reason not to be daunted by AMD, perhaps the most interesting part of the story lies ahead.

As a brand, AMD has been uncertain of itself. Now it is slowly shedding its popularly held image as a price warrior in favour of a nicely differentiated value proposition. This should be interesting to watch. But can it combine a genuine market insight with its "dual core" advantage to set the mutual reinforcement loop into motion?

The agreement brings actual federated IM to the industry after years of mostly talk about the prospect. During a press conference today, the companies said being able to instant message between IM communities is one of the features most requested by MSN Messenger and Yahoo Messenger users.

The deal means that, in addition to exchanging instant messages, consumers from both communities can see if friends are online or not, swap emoticons and generally communicate without being on the same IM network. Yahoo and Microsoft said they would launch the interconnectivity capabilities between MSN Messenger and Yahoo Messenger in the second quarter of 2006.

The two companies will look into providing more enhanced features, such as PC-to-PC VoIP (define), once they get the new IM network servers up and running, which will be based on the SIMPLE (define) protcol. For the time being, IM enhancements will focus on PC-to-PC communications, so Yahoo's recent acquisition of Dialpad Communications technology, picked up in part for its PC-to-Phone service, won't likely fall under that category.

Officials said complexity is the main reason they are not incorporating more features into the interoperable IM service next year. SIMPLE, while a proven technology, has not been scaled to the extent Yahoo and Microsoft want to deliver, they said.

"This is good for consumers; it's just a lot harder to do when you're flying an airplane as fast as these companies are flying and you want to change the engine at the same time," said Dan Rosensweig, Yahoo COO, in a press conference. "We have to get it right."

The combination of MSN Messenger and Yahoo Messenger networks could put a serious competitive dent in AOL's dominance in the IM world -- or bring the company closer to working out a federated IM deal with the other IM providers. Microsoft, Yahoo and AOL run the three largest IM networks in the world.

A recent study by research firm the Radicati Group reports some 867 million IM accounts worldwide today, a number expected to grow to 1.2 billion accounts in 2009. Public IM networks, such as AIM, MSN Messenger and Yahoo Messenger, primarily make up those numbers.

While Internet citizens have long called for interoperability between the top three, there has been no real serious work undertaken to let an MSN Messenger user talk to an AIM user or Yahoo Messenger user. Every once in a while, two of the three would come to the table but nothing ever came of those meetings.

Blake Irving, MSN communications services and member platform group corporate vice president, would not say whether recent talks with AOL touched on IM interoperability. Even if AOL and Microsoft were to come to some kind of agreement over federated IM, they probably wouldn't be able to do anything about it right now, he said in the press conference.

"The complexity of a deal like [Yahoo and Microsoft] and trying to execute is such that you can't do it with more than one partner at a time," he said.

When asked about a possible similar arrangement with Google, which launched its own IM service earlier in August.

"Certainly, we'd be willing to talk to them, but in terms of executing against the deal we described earlier, we've got to get this right," Irving said.

The closest public IM users have come to IM interoperability is through IM managers, such as Trillian, which lets users combine their AIM, Yahoo Messenger, MSN Messenger, IRC, Jabber and ICQ identities under one console.

What's the next big killer app from search companies? Quickly and easily searching telephone calls for a particular word or phrase—in essence, to Google your calls—is a likely candidate. And it isn't as far off as it might seem.

In the past two years, a number of customer service calling center operators for hire, some with thousands of employees working at the phones, have invested in the technology to identify inept operators and other measures of quality control.

While that's far from a mainstream scenario, these pioneering commercial applications are nonetheless an important first step toward a future in which phone calls will be among the Web pages available by visiting Google, Yahoo and other search engines.

Indeed, Ferris Research analyst Richi Jennings said, leading search engine giants like Google, Yahoo and Microsoft's MSN could introduce rudimentary searchable voice services right now.

"The big three of search could all do a good enough job of it to be of some value," Jennings said. "I do see adding into the search universe the ability to search what you said in phone calls."

While acknowledging such a feature is conceivable, and in some cases being worked on, executives from major search companies cautioned during recent interviews that it will be a long, slow slog to get there, and each will have to battle any number of factors beyond each companies' control.

"We are definitely evaluating whether to make available a feature like searching voice calls and voice mail and other advanced voice features," said Yahoo spokesperson Terrell Karlsten.

Analysts envision searchable voice applications that would help companies more easily comply with records-intensive Sarbanes-Oxley corporate governance mandates.

More mainstream applications would turn home phone call history into a searchable database to find telephone numbers, names, addresses or even documenting the type of calls someone makes.

Some analysts even believe phone calls will be listed among the results from using search engines design to mine information stored on someone's computer.

One reason searchable voice applications now appears on technology's horizon is because of the growing consumer interest in VOIP (voice over IP), which is freely available software that lets someone make phone calls using his or her Internet connection.

By treating the phone calls just like a Web page, e-mail or Instant Message, VOIP service providers create yet another Internet-based application for search engines to capture, archive and search.

VOIP calls also makes the transcribing process involved in a searchable voice feature much easier because of their revolutionary technology behind them.

The calls are digitized, and then packaged in routing instructions known as the IP, which is the most common way for machines of all kinds to communicate.

It was IP that, in essence, created the common language that Google and other search engines rely on.

For now, though, there are only a relatively few number of VOIP users—an estimated 5 million in the United States, according to several estimates.

Yet VOIP is clearly on the minds of each of the top search companies, and even companies like eBay that seem an unlikely choice to become a telephone operator.

On Aug. 9, Yahoo formally unveiled Yahoo Messenger with Voice, a version of Yahoo's Instant Messager that improves upon the call's sound quality.

Microsoft this week said it had acquired San Francisco-based Internet phone company Teleo.

Future versions of MSN Messenger, Microsoft's instant messaging application, will use Teleo's technology in order to let users make calls from PCs to land-line or mobile phones.

Also, VOIP has a rosy future, according to some analysts. VOIP lines are expected to grow significantly to 25 million in the next two years, or about 20 percent of the total number of traditional home phone lines in the United States.

Another positive sign for searchable voice is that speech recognition, which plays a critical role in the process, is improving and becoming less expensive. Once just able to spot a particular word, companies including U.K.-based Autonomy, a call center specialist, has developed technology to recognize spoken phrases, a quantum leap in effectiveness.

The company also recently purchased Irving, Texas-based eTalk, a provider of call center technology.

The combination will hasten the adoption of searchable voice, believes eTalk marketing director Kathy Kuehne.

Baby steps are being taken when it comes to archiving all those calls into a searchable database, for now a big investment in machines and manpower.

It's such a daunting task that Google isn't archiving the PC-to-PC phone calls capable among users of Google's relatively new GoogleTalk, its instant Messaging application. (IM is predominantly designed to be a stripped-down version of e-mail).