user3756607 user3756607 - 5 months ago 13
MySQL Question

calculate distance between two points in a flat land

I have a table of co-ords and I want to find the closest point, or find an easy way to mesure distance.

here is what i have atm. with my current location being (1500,1300)

SELECT `key` , `name` , SQRT( (
`x` - 1500
) + ( `z` - 1300 ) )
FROM `stargates`
WHERE 1


and I am getting null return values

I am dealing with a flat world of x and z co-ords.

table:

CREATE TABLE IF NOT EXISTS `locations` (
`key` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`x` int(11) NOT NULL,
`z` int(11) NOT NULL,
`discription` varchar(255) DEFAULT NULL,
`status` int(11) NOT NULL DEFAULT '1',
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`key`)
)

INSERT INTO `stargates` (`key`, `name`, `x`, `z`, `discription`, `status`, `created`, `modified`) VALUES
(1, 'PlaceA', -4461, 73, -2612, NULL, 0, '2014-07-07 14:10:35', '2014-07-07 14:10:35'),
(3, 'PlaceB', 1156, 66, -1915, NULL, 0, '2014-07-07 14:10:35', '2014-07-07 14:10:35'),
(5, 'PlaceC', 4541, 71, 3091, NULL, 0, '2014-07-07 14:10:35', '2014-07-07 14:10:35'),
(7, 'PlaceD', 5, 118, -66, NULL, 0, '2014-07-07 14:10:35', '2014-07-07 14:10:35'),
(9, 'PlaceE', -4441, 72, -2604, NULL, 0, '2014-07-07 14:10:35', '2014-07-07 14:10:35'),
(11, 'PlaceF', 71, 75, -66, NULL, 0, '2014-07-07 14:10:35', '2014-07-07 14:10:35')

Answer

You missed an important part of your formula You're wanting

SQRT( POWER('x' - 1500, 2) + POWER(  'z' - 1300, 2 ) )

Mind you, the general form is

SQRT( POWER('x2' - 'x1', 2) + POWER( 'z2' - 'z1', 2 ) )

So make sure you're using what you want as your x1, z1 reference point. This will give you distances from the point (1500, 1300).

Comments