Pages

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

No comments:

Post a Comment