CODEWITHSUNDEEP
mysqlgisspatiallatitude-longitude
Rob
Rob

Reputation: 119

MySQL GIS / Spatial Extensions - melting my brain

I'm new to GIS in MySQL and it's melting my brain!

I've created a table "places" as so:

    CREATE TABLE `places` (
  `id` int(6) unsigned zerofill NOT NULL auto_increment,
  `business_name` varchar(100) NOT NULL,
  `street_postcode` varchar(10) NOT NULL,
  `longitude` decimal(22,20) NOT NULL,
  `latitude` decimal(22,20) NOT NULL,
  `coord` point NOT NULL,
  UNIQUE KEY `id` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=25080 DEFAULT CHARSET=utf8

id, business_name and street_postcode where inserted into the table

I then updated the table with longitude and latitude coordinates in PHP as so:

UPDATE `places` SET `longitude` = '".$longitude."', `latitude` = '".$latitude."', `coord` = GeomFromText('POINT(".$coord.")') WHERE `id` = '".$row->id."' LIMIT 1

This all seems to have gone well, but I'm stuck when trying to find the nearest place to X coordinates. How do I find the 10 nearest places to X longitude and latitude?

This does not seem to work:

SELECT business_name, street_postcode, ROUND(GLength(LineStringFromWKB(LineString(AsBinary(coord), AsBinary('51.49437081 -0.2275573')))))
AS distance FROM places ORDER BY distance ASC LIMIT 10;

Eternal thanks in advance!

Upvotes: 0

Views: 750

Answers (1)

TheSteve0
TheSteve0

Reputation: 3526

That is because the spatial functions are not really implmented in MySQL. They stubbed out all the code but have not implemented all the methods.

I recommend using PostGIS or SpatiaLite.

Upvotes: 1

Related Questions