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.

CREATE TABLE geom (`OGR_FID` int(11) NOT NULL AUTO_INCREMENT, name varchar(100), geo GEOMETRY NOT NULL,
UNIQUE KEY `OGR_FID` (`OGR_FID`),   SPATIAL KEY geo (geo) ) ENGINE=MyISAM ;
INSERT INTO geom VALUES (NULL,"place1",GeomFromText('Point(1 1)'));
INSERT INTO geom VALUES (NULL,"place2",GeomFromText('Point(2 1)'));
INSERT INTO geom VALUES (NULL,"place3",GeomFromText('Point(3 3)'));
SELECT name,AsText(geo) FROM geom WHERE MBRContains(GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'),geo);

+--------+------------+
| name   | AsText(geo)|
+--------+------------+
| place2 | POINT(2 1) |
| place1 | POINT(1 1) |
+--------+------------+

The first statement creates the database with three fields, OGR_FID, name and geo.  The next three lines insert the grid data into the database. The last line looks for any point in our grid by drawing a bounding box of its four corners by “drawing” all four sides.

The MySQL function MBRContains returns a 1 or 0 (True or False) to indicate whether the MBR hold the data you are checking.   This select statement returns a (1).

select MBRContains(GeomFromText(‘POLYGON((-98 36, -97 36, -97 35, -98 35,-98 36))’), point( -97.5,35.5) )  ;

NOTE: If you search for data and don’t get any results  try reversing LAT and LON.  Coordinate data can be confusing and I’ve found not every database uses data in the same order. Also, Graphical tools like HeidiSQL, SQLyog and Toad for MySQL don’t display GIS data well.  Of these HeidiSQL did the best job.

Let’s try this on some real data?  The data in this example was download in MySQL Part 3.

select name, category, Y(SHAPE) as lat, X(SHAPE) as lng  from oklahoma_poi
where MBRContains(GeomFromText(‘POLYGON((-98 36, -97 36, -97 35, -98 35, -98 36))’), SHAPE ) limit 3;

 +-------------+------------+------------+-------------+
 | name        | category   | lat        | lng         |
 +-------------+------------+------------+-------------+
 | "Stop" sign | Automotive |  35.682066 |  -97.674018 |
 | "Stop" sign | Automotive | 35.7259863 | -97.6739644 |
 | "Stop" sign | Automotive | 35.6820227 | -97.6563292 |
 +-------------+------------+------------+-------------+

Now for some real FUN.  With two searchs, one for counties starting with “Oklahoma” and second for points of interest where the name starts with “School:”.

 set @LOC = (select astext(ogc_geom) from oklahoma_county where COUNTY_NAM like "Oklahoma%"  ) ;
 select   name, category, Y(SHAPE) as lat, X(SHAPE) as lng  from oklahoma_poi
 where  MBRContains(GeomFromText(@LOC), SHAPE ) and name like 'School:%' ;

+---------------------------------------+--------------------------------+------------+-------------+
| name                                  | category                       | lat        | lng         |
+---------------------------------------+--------------------------------+------------+-------------+
| School:Deer Creek School              | Government and Public Services | 35.6825513 | -97.6211545 |
| School:Northridge School              | Government and Public Services |  35.557277 | -97.6667095 |
| School:Harvest Hills School           | Government and Public Services |  35.576721 | -97.6605986 |
| School:Saint Francis DeSales Seminary | Government and Public Services | 35.5628328 | -97.6453203 |
~~~~~~~
| School:Clegern School                 | Government and Public Services | 35.6481103 | -97.4767064 |
| School:Risner School                  | Government and Public Services | 35.6278333 | -97.4750394 |
| School:Central Mid High School        | Government and Public Services | 35.6450549 | -97.4733729 |
| School:Central State University       | Government and Public Services | 35.6553323 | -97.4719841 |
+---------------------------------------+--------------------------------+------------+-------------+
 296 rows in set (0.07 sec)

This is a lot faster then searching each each row with a clause like “WHERE (POW(lat – 35.123456, 2)+POW(lon – -97.123456, 2))“.  How much faster? You’ll have to wait for for another posting.  Here is whats coming.

Tweet