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 (
name VARCHAR(20) PRIMARY KEY,
location Point NOT NULL,
description VARCHAR(200),
SPATIAL INDEX(location)
);
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
http://dev.mysql.com/doc/refman/4.1/en/spatial-extensions.html
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 GeomFromText().
This function takes a string and returns a Geometry Object.
The format of the string is a GIS standard decribed at
http://dev.mysql.com/doc/refman/4.1/en/gis-wkt-format.html
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
geometries.
Due this approximation to abtain a correct result we have to filter the intersect results whith a pitagoric distance
computation.
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
geometries.
Due this approximation to abtain a correct result we have to filter the intersect results whith a pitagoric distance
computation.
SET @center = GeomFromText('POINT(10 10)');
SET @radius = 30;
SET @bbox = CONCAT('POLYGON((',
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, '))'
);
[1]
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:
[2]
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.
CREATE PROCEDURE fill_points(
IN size INT(10)
)
BEGIN
DECLARE i DOUBLE(10,1) DEFAULT size;
DECLARE lon FLOAT(7,4);
DECLARE lat FLOAT(6,4);
DECLARE position VARCHAR(100);
-- Deleting all.
DELETE FROM Points;
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;
END WHILE;
END
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.
43 comments:
I get an error saying: "Error Code : 1464
The used table type doesn't support SPATIAL indexes"
help?
Probably you have not enabled your table with Spatial indexes:
Error: 1464 SQLSTATE: HY000 (ER_TABLE_CANT_HANDLE_SPKEYS)
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.
OK - is there something I have to setup in the config to turn on geometry stuff?
Figured it out - was trying to us INNODB instead of MyISAM
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.
Is the Point coordinates the lat-long system or it just any arbitrary cartesian system?
What is the mesure unit of radius? Is it 30km, m or miles or other in this example? thnx
Pitagoric distance function? You might want to do some proofreading.
That was a nice article, thank u
thanks!
but what its the mesure unit???
Great post. I found this very helpful for performing the spatial queries I had been struggling with!
i am also having error when i tried it....
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
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.
Awesome, but you broke your HTML with SQL code at the and of STEP 3
wonderful blog..keep sharing your talent through this blog
visit Best MySQL Course Jaipur
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
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
The blog is too good, you describe the article very well great.
Thank you.
Python Training in Jaipur
salesforce Training in Jaipur
Digital marketing Training in Jaipur
Tally Training in Jaipur
Adobe Photoshop Training in Jaipur
MSME Training in jaipur
NETWORKING Training In Jaipur
amazing blog and I found much interesting stuff on this site. thank you so much for this.
iis university
Best academic environment at university campus
best girls university in jaipur
Best girls college in Rajasthan
Best deemed university Rajasthan
Admission in university with hostel facility in Rajasthan
nice blog great article you are provided very useful information thank you for sharing it.
Aviation institute
Air Hostess institute
airport ground staff training institute
hospitality and travel management institute in Jaipur
Cabin Crew training institute
computer classes for beginners near me
https://www.seekace.com/about
best data science course provides by SeekACE Solutions into an emerging leader for product development and customization with a strong blend of Data Science and AI.
https://www.seekace.com/about
When it comes to providing best assignment service online,
Unique Submission makes sure to complete assignment antecedently to offer scholars enough time to proof read the given assignment
before submitting it to their tutors/professors. We never compromise to timely delivery and deadlines given by our customers.
Assignment Help Services
Very nice article
best orthopedic hospital jaipur
best orthopedic doctor in jaipur
good and amazing blog.
Packers and movers
Crown Packers and movers
Packers and movers in Delhi
Packers and movers in Noida
Packers and movers in gurgaon
When it comes to providing best assignment service online,
Unique Submission makes sure to complete assignment antecedently to offer scholars enough time to proof read the given assignment
before submitting it to their tutors/professors. We never compromise to timely delivery and deadlines given by our customers.
Coursework Writing Help and Services
Myob Perdisco Assignment Help
Presentation Assignment Help Services
Proofreading and Editing Services
someone should realy rework this site. Outdated content and spammed
Really great info you provide here
thank you
Orthopedic hospital in jaipur
Neurology hospital in jaipur
cardiology hospital in jaipur
Super speciality hospital in jaipur
arthroscopic hospital in jaipur
asthma and Allergy hospital in jaipur
endocrinology hospital in jaipur
Cosmetic Surgery Hospital in Jaipur
diabetes hospital in jaipur
psychiatric hospital in jaipur
Dr Shri Niwash Jangir provides the best treatment for Depression In Jaipur
Dr Sandeep Nunia can give the best treatment for Kidney Stones in Jaipur
Nav Imperial Hospital is the best centre for Joint Replacement surgeries
hear again has the best audiologist of gurgaon
Thanks for your post which gathers more knowledge. I read your blog. Everything is helpful and effective.
Thanks for your post which gathers more knowledge. I read your blog. Everything is helpful and effective.
https://www.protocloudtechnologies.com/digital-marketing-course-in-jaipur/
Protocloud provides the best digital marketing course in Jaipur from the best-dedicated trainer with 100% Practical and lives according to your strength and lack.
Protocloud trainers grow knowledge and confidence in every student about digital Marketing.
Protocloud will provide you a certificate and prepare you for Google Certifications, which will give you many advantages. That has its value in the market.
Protocloud team grow interview skill to every student Because
When our students go to any company for interviews. They find it very easy to crack that interview rather than other students appearing there.
https://www.protocloudtechnologies.com/digital-marketing-course-in-jaipur/
Protocloud Digital Agency is one of the leading training institutes in Jaipur, offering basic level to advanced level digital marketing courses. Our training courses are designed professionally to enhance your skills in various aspects of online marketing. Moreover, our systems are streamlined according to modern standards and the latest trends in the industry.
https://www.protocloudtechnologies.com/digital-marketing-course-in-jaipur/
Wonderful posts your blog have. I'm now a regular reader of it.
Get Help With File Explorer In Windows 10
Delta VS Omicron | Main Differences Between Both Variants
Omicron Latest | Japan Developed Mask Could Help Fight Omicron
your post good
https://www.webmediaeducation.in/
Explore the range of Best Kratom Capsules section available at Top Extracts. These capsules provide a convenient and precise way to enjoy the benefits of Kratom without the need for measuring or mixing powders. Each capsule is filled with high-quality, lab-tested Kratom to ensure purity and potency.
Post a Comment