Is MySQL’s GIS really worth using?

Is GIS worth using in MySQL? In the past few post, I have explored what GIS is and how it is used. GIS encoded data is wonderful and can help with all kinds of cool queries.  I’m late getting this article written so lets get right to it.

The most common geographical  query is for all the point within some distance from a given point. I’ll try to focus on ways to answer this type of query. Accuracy of the answer is always important. Think carefully about your query. Do you want every pizza place within a radius of a port or within a square mile? Or, do you really want it within a miles walking distance?

I’m using the common city_lookup table for these tests. Here is the schema.

CREATE TABLE `city_lookup` (
`city_id` INT(7) NOT NULL DEFAULT '0',
`feature` VARCHAR(20) NULL DEFAULT NULL,
`name` VARCHAR(50) NULL DEFAULT NULL,
`pop_2000` INT(10) NULL DEFAULT …

WHAT CAN YOU DO WITH GEO DATA?

Geo spatial indexes are what make this type of data valuable.  With shape and point data you can find relationships between object in our physical world.  How close is the lightning in the storm front?  What homes where hailed on? (WDT) What schools are in my city?  With a list of homes for sale, how fare are they from their nearest school?  What picture where take in this area. (TwitPic)

Lets start with a simple grid of coordinates by creating a table for it call geom, adding our data points in and out of our grid and then searching with a small bounding box. The grid looks like this.

What data is available?

GEO data is expensive to create, so has been created by governments.  In the past governments charged for this data.  In 1980 the USGS was charging $300 (usd) per county for Oklahoma GEO data. (I complained to my congressman.) Today, a quick Internet search turns up lots of free GIS data.

I was hoping to find a neat collection of basic GEO data.   It would be nice if there was one place you could get world political borders (Polygons), postal codes (Polygons) and  points of interest like hospitals and airports.  What you can find is lots of  lists, often collections of odd data created for a virility of complex political purpose.  For example, The Global Change Master Directory is a large list of data sources on earth …

In my business (weather) we use lots map based (Geo) information.  Almost every table has latitude and longitude. Working with this kind of data can be exciting and frustrating.  This should give you a quick start into GIS with MySQL.

“A geographic information system (GIS), or geographical information system, is any system that captures, stores, analyzes, manages, and presents data that are linked to location. In the simplest terms, GIS is the merging of cartography, statistical analysis, and database technology. GIS systems are used in cartography, remote sensing, land surveying, …

Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.
OracleI'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you refer to the documentation? And how often does …

We've now published a new set of binary packages including the extended GIS functionality from the mysql-5.1-wl1326 source tree.

This release is based on the MySQL 5.1.35 code base and fixes the bugs mentioned below. It includes some improvements to the GIS functionality as well, so please use these packages for future testing of the MySQL GIS functionality.

The following GIS-related bugs were fixed in this version:

• Bug#31753: Buffer/area functions only return first row of set
• Bug#32032: Contains() does not work on MultiPolygons, may force a disconnect and/or result in extremely long query times.
• Bug#32100: contains, …

Normal geocoding is the process of taking an address and converting it into latitude and longitude. Reverse geocoding goes the other direction; given a latitude and longitude we want to know the closest street address.

Google Maps API, Yahoo Maps, Microsoft, GeoNames.Org and others can provide both these services, but they either cost, or have restrictive terms of service that makes them unavailable for high volume applications. See Geo::Coder::US (for US addresses only). This uses a local data source, and requires you to download the data from the US Census manually. It can import a format known as …

MySQL has had basic support for Geospatial Data since 4.1, but has lacked some of the features of the OpenGIS specifications since then. The good news is, this is rapidly changing. Our own Holyfoot has been hammering away at WorkLog #1327, to provide precise functions for our GIS support.

Even better, it’s fast. How fast? Well, the good people at Oki Labs, apart from having implemented several new GIS functions for MySQL, have done some benchmarking, and it’s looking good. If you’ll excuse the cliched comparison to Postgres, here are the response times (seconds) of MySQL GIS vs. PostGIS in Oki’s test: