Wednesday, November 7, 2007

Using Circular Area Selection

This mini tutorial explains how to use the Spatial extensions of MySql 5.x and demonstrates the high performaces that can be obtained if used in the correct way.

The tutorial is intended to users who know SQL and MySQL in particular.

Step 1. Creation of a Spatial table.

The first step explains how to create a table containing geographic data named Points.

create table Points (
location Point NOT NULL,
description VARCHAR(200),

This DDL command creates a table named Points containing a set of records
with a name and a location characterized by a Point Geometry.

As you can see a spatial extension can be used as any other MySQL data type
simply including it in the definition of a field.

The base class of every Spatial Type dat is Geometry.

The complete hyerarchy of geometry data type supported by MySQL can be found at

Step 2. Insert data inside the Spatial Table.

This step shows how is easy to insert data inside the Points table.

INSERT INTO Points (name, location) VALUES ( 'point1' , GeomFromText( ' POINT(31.5 42.2) ' ) )

This is just a common SQL INSERT operation, the only news is the use of the function
This function takes a string and returns a Geometry Object.
The format of the string is a GIS standard decribed at

Step 3. Retrieve data from the Spatial Table.

Retrieve data from the Points table is even simpler:

SELECT name, AsText(location) FROM Points;

This returns a result set which location is converted in the same GIS format introduced in Step2,
in fact the AsText function converts the internal representation of a geometry to a string format.

There are another couple of functions very useful that can be introduced at this point:

SELECT name, AsText(location) FROM Points WHERE X(location) < style="font-weight: bold;">Y(location) > 12;

This SELECT returns a result set of points which location X() (longitude) is minor of 10 and Y() (latitude) is major of 12.

Step 4. Advanced queries on the Spatial Table.

Converts as readable text the envelope of the specified Geometry.

SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));

Returns the size (float) of the specified Geometry.
SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));

Returns the Geometry type (varchar) of the specified Geometry.
SELECT GeometryType(GeomFromText('POINT(1 1)'));

Find points in a bounding box.

SET @bbox = 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))';
SELECT name, AsText(location) FROM Points WHERE Intersects( location, GeomFromText(@bbox) );

Step 5. Find points in a circular area.

This step want to show how to perform queries of point geometries on circular areas (that areas described by a a center and a radius.)

The first solution you could mind to find entities in a circular area would be:

SET @point = 'POINT(10 10)';
SET @radius = 20;
SELECT name, AsText(location) FROM Points WHERE Distance(location, GeomFromText(@point)) < @radius; But this solution DOESN'T WORK, because the Distance function is not implemented.
In fact the MySQL Spatial Extension docs specifies that only a subset of the OpenGis standard
has being covered by their implementatation !!

A good roundtrip to this problem is to use the intersect function.

NOTE: The MySQL Spatial Documentation specifies that the Intersect function for
every Geometry is approximated to the Intersect function of the bounding boxes of the involved

Due this approximation to abtain a correct result we have to filter the intersect results whith a pitagoric distance

SET @center = GeomFromText('POINT(10 10)');
SET @radius = 30;
X(@center) - @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) - @radius, ',',
X(@center) + @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) + @radius, ',',
X(@center) - @radius, ' ', Y(@center) - @radius, '))'

SELECT name, AsText(location)
FROM Points
WHERE Intersects( location, GeomFromText(@bbox) )
AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius; To Obtain a result ordered by distance from the center of the selection area:

SELECT name, AsText(location), SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) AS distance
FROM Points
WHERE Intersects( location, GeomFromText(@bbox) )
AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius
ORDER BY distance;

Step 6. Verify performances

This last step want to provide a method to verify performances in querying large dataset on Spatial Data.

First of all is provided a storage procedure able to fill the Points table with a given number of random records.

IN size INT(10)

DECLARE position VARCHAR(100);

-- Deleting all.

WHILE i > 0 DO
SET lon = RAND() * 360 - 180;
SET lat = RAND() * 180 - 90;

SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' );

INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) );

SET i = i - 1;

Then invoke the stored procedure with a significant number

One million records for example.

CALL fill_points(1000000);

And then performs the queries [1] and [2].

On my Intel Core Duo 2.0 GHz Laptop I obtain these results:

Circular area selection without sorting [1]

43862 rows in set ~1.10 sec with 1.000.000 records

Circular area selection with sorting [2]

43862 rows in set ~1.72 sec with 1.000.000 records

NOTE: your results can change in size being the record locations randomatically generated.


Ellis said...

I get an error saying: "Error Code : 1464
The used table type doesn't support SPATIAL indexes"


Michele Mostarda said...

Probably you have not enabled your table with Spatial indexes:


Message: The used table type doesn't support SPATIAL indexes

To do it declare at least a field of your table with a Geometry object.

Ellis said...

OK - is there something I have to setup in the config to turn on geometry stuff?

Ellis said...

Figured it out - was trying to us INNODB instead of MyISAM

Alex said...

I haven't tried your solution yet, but I see a possible improvement of the code - instead of doing SQRT(XXX) < radius, you could simply do XXX < POW(radius,2), which would do the same thing, but given that you can pre-calculate POW(radius,2) will avoid calculating the square root of the sums for each row.

Unknown said...

Is the Point coordinates the lat-long system or it just any arbitrary cartesian system?

Das Autorenteam said...
This comment has been removed by the author.
Unknown said...

What is the mesure unit of radius? Is it 30km, m or miles or other in this example? thnx

Anonymous said...

Hookah Hookah Shisha

Instant Light Charcoal

Starbuzz Premium Tobacco Starbuzz

Hookah Tobacco Al-Fakher

Hookahs Wholesale Hookah Shisha

Unknown said...

Genuine Leather Belt Stainless Steel Metal Square Studs Design Over 100 Steel Metal Studs Buckle Width: About 1-1/2 inches, 3.8 cm The buckle can be removed so the belt can be used as a snap belt for any buckle. The belts are dozen pre-packed in assorted sizes
Unique belts for men that goes with any outfit. Our urban leather customize belts are highly detailed with intricate and colorful designs inspired by popular fashion designers. We have leather belts for every type of style. For the punk rockers we have leather studded belts and belts with skulls. Studded leather belts come in various colors for a stylish and edgy look.

Matt Montag said...

Pitagoric distance function? You might want to do some proofreading.

Bling For Fun Belts said...

Wholesale Belt, wholesale chains, wholesale belt buckles, wholesale leather belts, wholesale fashion rings
wholesale belts
wholesale chain
wholesale buckles
belt leather wholesale
wholesale western belts
hiphop wholesale
wholesale hiphop watches
wholesale rings

viswanath said...

That was a nice article, thank u

x2l2 said...


but what its the mesure unit???

Unknown said...

Shivam LA Blingicedoutjewelry Offer Great Prices on large selection of Bling Hip Hop Jewelry, Bling Buckles, Belts, Bracelets, Chain,Pendants, Watches & Earrings. Large Selection at wholesale prices.
Wholesale watches
Hip Hop Jewelry
Bling Buckles
Hiphop Watches

Unknown said...

Great post. I found this very helpful for performing the spatial queries I had been struggling with!

johndavid said...

What a Great Article it its really informative and innovative keep us posted with new updates. its was really valuable.Hookah thanks a lot. Thanks for sharing this information.Hookah shisha said...

i am also having error when i tried it....

Ishu said...

This approach will present an extreme problem if you want to present your data out of a webservice to the clients who wont recognize this sorta datatype. You might need to pre-process this for your clients.
iPhone Developer

sebastian said...

Some people asked what measure unit it is !

I do THINK it is degrees. It is hard to calculate an exact distance in miles or kilometres.
But I think (distance*69) should be a kilometre value.
Correct me if I am wrong.

general manager said...

This is really good share,

Fernando Wolf Bona said...

Awesome, but you broke your HTML with SQL code at the and of STEP 3

Unknown said...
This comment has been removed by the author.
Unknown said...

wonderful blog..keep sharing your talent through this blog
visit Best MySQL Course Jaipur

Unknown said...

Blog is too good..Amazing blog..
Python Training in Jaipur

Hadoop Training in Jaipur

Software Testing training in Jaipur

MVC Training in Jaipur

Adobe Photoshop Training in Jaipur

NETWORKING Training In Jaipur

Bharat Rajput said...

Thank you so much for sharing this informative article finally i found many interesting stuff on your site i will share too some information....

How To Create Database And User In MYSQL

What is the full form of HTML | HTML INFORMATION

How To Enable Cookies In Your Chrome Browser

BrnInfotech said...

Thank you for your post. This is useful information.
Here we provide our special one's.
iphone training classes
ios app development course
iphone app development in hyderabad
iphone training in hyderabad
ios training centers in hyderabad