Almost every business process or decision includes a WHERE!

Like IP addresses encode the destination of a domain name, with a geographic information system (GIS) you can encode and analyze the location data allowing you to reveal relationships, patterns, and trends.

Weather Decision Technologies

Show embedded map in full-screen mode

The world of GIS is confusing. Here is my understanding of how the peaces go together and how you can get started.

We describe locations on the earth in terms of

  [Read more...]

In my quest to understand spatial GIS functionality, I have come to the ultimate goal: evaluation the actual database products themselves.

PostgreSQL / PostGIS

PostGIS is a variant of PostgreSQL with spatial extensions. The main reason for maintaining the GIS feature set outside of PostgreSQL proper seems to be licensing: the spatial extensions are LGPL GPL licensed.

PostGIS is widely recognized as the most mature and feature-rich GIS implementation for SQL databases (and perhaps any database), matched only by the costly Spatial extension for the Oracle Enterprise database. (See comparisons in the links below.)

read more

While the underlying index should be opaque to the user of a DBMS with spatial features, the API used to define spatial types and operate on them is of course more visible. The relevant standard in this space is often referred to as "OpenGIS", however the Open Geospatial Consortium in fact defines a long list of standards. The standard relevant to SQL databases is known more precisely as "OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option" aka "Simple feature access".

It is not meaningful to recite the standard at length in my blog, my focus is instead on actual implementations that I will blog about later. The following points are however worth noting:

read more

I work for a company that is the leading supplier of automotive maps, and wants to be the leading supplier of online maps. So it was only a matter of time that I needed to learn more about how spatial extensions work in different open source databases. Let's start from the beginning, understanding various spatial data structures that are used in implementations...

Links are provided to Wikipedia articles - which are both comprehensive, yet easy to understand - for those who want to get a deeper understanding of each structure. All Wikipedia articles on spatial indexes are listed here: http://en.wikipedia.org/wiki/Spatial_index#Spatial_index

B-tree

read more

One of the query optimization scenarios I’ve seen a lot over the years is finding something within some distance from a point. For example, finding people within some distance of yourself, apartments in a radius from a postal code, and so on.

These queries usually use the great-circle formula. That might be because Google finds lots of pages claiming that this is the right way to do a radius search. “The earth is not flat!”, they all say. That’s true, but it doesn’t mean that the great-circle formula is a good approach. It’s usually a really bad approach, in fact. It’s needlessly precise for most things, not precise enough for others, and it’s an expensive query to execute; all the trig functions tend to eat a bunch of CPU, and make it impossible to use ordinary indexes. This is true for all of the

  [Read more...]

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

  [Read more...]

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.

0,0 0,1 0,2 1,0 1,1 1,2 2,0 2,1 2,2

CREATE TABLE geom

  [Read more...]

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

  [Read more...]

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,

  [Read more...]

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.

Oracle

I'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

  [Read more...]

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

  [Read more...]