9

I want to store latitude and longitude values of places in a mysql database table. With the future in mind I will want to be able to find these places within a certain radius of a specific location. Having said that, what datatypes should I store the latitude and longitude values in? Please could you provide me with a create table script for columns like so:

place_id  |  lat  |  long

Is there perhaps a column I am missing in the above table that will provide me with additional information that I may not see I need at the current time?

Thanks for any help.

Martin
  • 10,294
  • 11
  • 63
  • 83
  • possible duplicate of [What is the ideal data type to use when storing latitude / longitudes in a MySQL database?](http://stackoverflow.com/questions/159255/what-is-the-ideal-data-type-to-use-when-storing-latitude-longitudes-in-a-mysql) – Gajus Jun 15 '15 at 19:28

2 Answers2

21

You should store the points in a singe column of datatype Point which you can index with a SPATIAL index (if your table type is MyISAM):

CREATE SPATIAL INDEX sx_place_location ON place (location)

SELECT  *
FROM    mytable
WHERE   MBRContains
               (
               LineString
                       (
                       Point($x - $radius, $y - $radius),
                       Point($x + $radius, $y + $radius)
                       )
               location
               )
        AND Distance(Point($x, $y), location) <= $radius

This will drastically improve the speed of queries like "find all within a given radius".

Note that it is better to use plain TM metrical coordinates (easting and northing) instead of polar (latitude and longitude). For small radii, they are accurate enough, and the calculations are simplified greatly. If all your points are in one hemishpere and are far from the poles, you can use a single central meridian.

You still can use polar coordinates of course, but the formulae for calculating the MBR and the distance will be more complex.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    All my tables use InnoDB as I use foreign keys. Is it not a problem to have tables in both InnoDB and MyISAM? – Martin Jan 18 '11 at 15:58
  • @Martin: `MyISAM` is transactionless and you cannot use `FOREIGN KEY` constrains on it. For only `SELECT` queries, there are no problems with mixing the tables of different types. – Quassnoi Jan 18 '11 at 16:00
  • Yes I understand that foreign keys don't work in MyISAM however if I have an additional table for the coordinates and have that as MyISAM and all my other tables are InnoDB is that not a problem? – Martin Jan 18 '11 at 16:03
  • @Martin: For `SELECT` queries, this is not a problem. – Quassnoi Jan 18 '11 at 16:05
  • Thanks for the great answer! One more thing though - how would I insert a place with lat=51.484804 and long=0.296631 for example? – Martin Jan 18 '11 at 16:14
  • 2
    @Martin: `INSERT INTO mytable VALUES (Point(51.484804, 0.296631))`. For earlier versions of `MySQL` (`5.0` and probably some early `5.1`) this may require `WKT` translations like this: `INSERT INTO mytable VALUES (GeomFromText('POINT(51.484804, 0.296631)'))`. If all your points are in UK I would really suggest using `UTM` coordinates in zone `30`. – Quassnoi Jan 18 '11 at 16:17
  • All the points will actually be in South Africa, I don't know if that makes any difference :) Thanks for all the information! – Martin Jan 18 '11 at 16:35
  • @Martin: no, this is even better, since SA is closer to the equator :) – Quassnoi Jan 18 '11 at 16:44
  • 1
    newer versions of MySQL support spatial on innodb:http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html – Ben Miller Mar 25 '12 at 20:32
  • @BenMiller: InnoDB does not support spatial indexes even now in `5.6`. The doc you're referencing clearly tells: *For spatial columns, `MyISAM` supports both `SPATIAL` and non-`SPATIAL` indexes. Other storage engines support non-`SPATIAL` indexes.* Please note that `5.0` you're referencing was out in 2006, it's not "newer". – Quassnoi Mar 25 '12 at 21:32
  • @Quassnoi: My mistake, i saw " As of MySQL 5.0.16, InnoDB, NDB, BDB, and ARCHIVE also support spatial features." and missed that this did not also apply to spatial indexes. – Ben Miller Mar 25 '12 at 23:08
  • Two things: First, you missed the comma after `LineString()`. Second, `$radius` is roughly `kilometers (km) / 100`. – caw Nov 26 '14 at 04:59
  • Thank you. Can I get the distance between in my rows data? Like If I get 2 rows, one of them are 0.5 km away, and one is 2km away, etc. – user3121056 Apr 16 '15 at 20:16
  • @user3121056: please post it as a question. – Quassnoi Apr 16 '15 at 20:29
  • Please take a look at it: http://stackoverflow.com/questions/29691870/lat-long-setup-through-geometry-points-mysql-get-distance-in-result-rows – user3121056 Apr 17 '15 at 06:28
0

I've digged few hours through tons of topics and could nowhere find a query, returning points in a radius, defined by km. ST_Distance_Sphere does it, however, the server is MariaDB 5.5, not supporting ST_Distance_Sphere().

Managed to get something working, so here is my solution, compatible with Doctrine 2.5 and the Doctrine CrEOF Spatial Library:

    $sqlPoint = sprintf('POINT(%f %f)', $lng, $lat);

    $rsm = new ResultSetMappingBuilder($this->manager);
    $rsm->addRootEntityFromClassMetadata('ApiBundle\\Entity\\Place', 'p');

    $query = $this->manager->createNativeQuery(
        'SELECT p.*, AsBinary(p.location) as location FROM place p ' .
        'WHERE (6371 * acos( cos( radians(Y(ST_GeomFromText(?))) ) ' .
        '* cos( radians( Y(p.location) ) ) * cos( radians( X(p.location) ) ' .
        '- radians(X(ST_GeomFromText(?))) ) + sin( radians(Y(ST_GeomFromText(?))) ) * sin( radians( Y(p.location) ) ) )) <= ?',
        $rsm
    );

    $query->setParameter(1, $sqlPoint, 'string');
    $query->setParameter(2, $sqlPoint, 'string');
    $query->setParameter(3, $sqlPoint, 'string');
    $query->setParameter(4, $radius, 'float');

    $result = $query->getResult();

Assuming lng and lat is XY of the fixed point, Place is the entity with a "location" field POINT type. I could not use DQL directly due to problems with the param binding of MySQL, that's why the low-level native query. The rsm is required to map results into entity objects. Can live without it, though.

Feel free to use it. I hope it will save you some time.

Thomas P.
  • 31
  • 3