Storing geolocation of a point in MySQL using Spatial Data Type

AS we  know todays developing location-based application is most common. we know, we can get the location of a point in earth using Latitude and Longitude. we can uniquely define any point in a Earth using latitude and longitude, together they used to defined geographical co-ordinate of Earth's and represent the angular distance of any location from the center of the Earth.

The best way of storing latitude and longitude or position of a point in MySQL is  POINT class of MySQL Spatial Data Types.

When using POINT class, the order of the arguments for storing co-ordinates must be POINT(longitude, latitude).

For example suppose geo-location of a point is -

 long = 77.18563599999993       // long represents longitude
 lat    =    28.513096                   // lat represent latitude

Step to store and retrieve these points in MySQL

Step 1 : first we create a table for storing location of a point using POINT class of  Spatial Data Type.

--
-- Table structure for table `site`
--

        CREATE TABLE `site` (
             `id` tinyint(4) NOT NULL,
              `position` point NOT NULL)
              ENGINE=InnoDB

Step 2 : SQL syntax for storing geolocation using Spatial

   INSERT INTO `site` (`id`, `location`) VALUES ('1', GeomFromText('POINT(77.18563599999993 28.513096)'));

This SQL commands store the location of a  point as binary GEOMETRY object. GeomFromText(), this function return the geometry object.

Step 3 : Retrieve points from table

         SELECT *, AsText(position) FROM site