MySQL Forums
Forum List  »  GIS

Re: Help - build table of POINT
Posted by: Tom Pouce
Date: October 25, 2008 12:22PM

The command
UPDATE cities SET coord=GeomFromWKB(Point(lng,lat));
works :)

Thanks for that. But then when I calculate the distance between 2 points, it doesn't sound right:

SELECT
c1.name, c2.name,
12734 * ASIN(SQRT(POWER(SIN((c1.lat - c2.lat)*pi()/360), 2) + COS(c1.lat * pi()/180) * COS(c2.lat * pi()/180) * POWER(SIN((c1.lng - c2.lng) * pi()/360), 2))) AS distance2,
12734*GLength(LineFromWKB(LineString(AsBinary(c1.coord), AsBinary(c2.coord)))) AS distance3
FROM cities c1, cities c2
WHERE (c1.name = 'T') AND (c2.name='C');

+----------+--------+-----------------+-----------------+
| name | name | distance2 | distance3 |
+----------+--------+-----------------+-----------------+
| T | C | 421.84073313958 | 52673.696420869 |
| T | C | 424.31784079402 | 52803.474042613 |
| T | C | 418.81561568877 | 52412.117428389 |
+----------+--------+-----------------+-----------------+

The distance2 is correct in km.
I don't know what distance3 is worth. There is no linear factor between the 2.

Any idea of what's going on?

Thanks again.

Options: ReplyQuote


Subject
Views
Written By
Posted
4668
October 19, 2008 07:16AM
3195
October 23, 2008 12:24AM
Re: Help - build table of POINT
2912
October 25, 2008 12:22PM
3119
October 27, 2008 03:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.