WGS84 lats and longs distance calculation
Posted by:
wab mort
Date: February 14, 2008 04:30AM
Hi. I am new to mysql and I cant find where to define the coordinate system I want to use in the documents..
I want to be able to store a wgs84 data and be able to query distances, intersects, etc.. I have modified the distance example and the distances are not calculated. any help would be appreciated , thanks.
############## SQL:
CREATE TABLE places102 (
place_name CHAR(40) NOT NULL,
place_loc POINT NOT NULL,
PRIMARY KEY(place_name)
);
INSERT INTO places102 VALUES('Rathby', GeomFromText('POINT(18.79837 -34.02468)'));
INSERT INTO places102 VALUES('Jamestown', GeomFromText('POINT(18.84891 -33.97798)'));
CREATE TABLE places_crossroads2 (
crossroad_name CHAR(40) NOT NULL,
crossroad_loc POINT NOT NULL,
PRIMARY KEY(crossroad_name)
);
INSERT INTO places_crossroads2 VALUES('Golf Course', GeomFromText('POINT(18.853230523 -33.959153445)'));
INSERT INTO places_crossroads2 VALUES('N1', GeomFromText('POINT(18.791392639 -33.820081278)'));
INSERT INTO places_crossroads2 VALUES('N1 R3', GeomFromText('POINT(18.791392639 -33.820081278)'));
INSERT INTO places_crossroads2 VALUES('N1 R4', GeomFromText('POINT(18.870903572 -33.798678919)'));
INSERT INTO places_crossroads2 VALUES('310 Turn off', GeomFromText('POINT(18.858149024 -33.922246345)'));
INSERT INTO places_crossroads2 VALUES('N2 R44', GeomFromText('POINT(18.821164761 -34.076997015)'));
SELECT c.crossroad_name, ROUND(GLength(LineStringFromWKB(LineString(AsBinary(c.crossroad_loc), AsBinary(a.place_loc))))) AS distance
FROM places_crossroads2 c, places102 a
WHERE a.place_name = 'Rathby' ORDER BY distance ASC LIMIT 4;
################### My Result
310 Turn off 0
Golf Course 0
N1 0
N1 R3 0
N1 R4 0
N2 R44 0