Geek Answers Handbook

Find the distance between two points in MYSQL. (using dot type)

Suppose I have a table with two columns:

| user_id | int(11) | NO | UNI | NULL | | | utm | point | NO | MUL | NULL | |

As you can see, it is very simple. utm is a Point data type. I insert it like this:

 INSERT INTO mytable(user_id, utm) VALUES(1, PointFromWKB(point(50, 50)));

Then I create a spatial index.

 ALTER TABLE mytable ...add spatial index on(utm) or something. (forgot)

Good, all is well. Now I want to select *, where distance <99999. But that will not work!

 //This is supposed to select all where the distance is less than 99999999. set @mypoint = PointFromWKB(point(20,20)) select * from mytable where GLength(LineString(utm, @mypoint)) < 9999999; Empty set (0.00 sec) select * from mytable where GLength(LineStringFromWKB(LineString(utm, @mypoint))) < 9999; Empty set (0.00 sec)

By the way, I tried to INSERT INTO without PointFromWKB ... and it didn’t work ... why someone suggested PointFromWKB to me.

+7
database mysql indexing point
source share
3 answers

solved. This is what I did:

 where GLength(LineStringFromWKB(LineString(asbinary(utm), asbinary(@mypoint)))) < 9999999999999;
+3
source share

You can also do it this way. Not sure if this is faster or not.

 select * from mytable where glength(geomfromtext(concat('linestring(', x(utm), ' ', y(utm), ',20 20', ')'))) < 99999999
+1
source share

As far as I know, you need to try this way -

 select * from mytable where ( GLength( LineStringFromWKB( LineString( geoPoint, GeomFromText('POINT(51.5177 -0.0968)') ) ) ) ) < 99999999

More details in this answer .

0
source share

More articles:


All Articles