0

I have a table that looks like this

| id | name    | latitude          | langitude          | costLat          | sinLat            | cosLng            | sinLng           | 
| 1  | place 1 | 2.942743912327621 | 101.79377630352974 | 0.99868133582304 | 0.051337992546461 | -0.20438972214917 | 0.97888959616485 |

Referring to this article, it seems like a good idea to use st_within in order for me to search for locations within 5 km radius from a given latitute and langitude in my table above. But I totally have no idea how to do that.

The table is MyISAM, MySQL version 5.6

Sorry for not being clear on what I tried. From the documentation it mentions that

ST_Within(g1,g2)

Returns 1 or 0 to indicate whether g1 is spatially within g2.

So my understanding is, we need to pass 2 params to ST_Within. Sound simple enough, but when I looked at the sample query in the linked articles, it does (*note: I changed shape to CIRCLE in the query, as my assumption is my shape is CIRCLE because I'm searching for radius)

set @lat= 37.615223;
set @lon = -122.389979;
set @dist = 10;
set @rlon1 = @lon-@dist/abs(cos(radians(@lat))*69);
set @rlon2 = @lon+@dist/abs(cos(radians(@lat))*69);
set @rlat1 = @lat-(@dist/69);
set @rlat2 = @lat+(@dist/69);

SELECT ASTEXT("CIRCLE"), NAME FROM location_final 
WHERE st_within("CIRCLE", ENVELOPE(LINESTRING(POINT(@rlon1, @rlat1), POINT(@rlon2, @rlat2))))  
ORDER BY st_distance(POINT(@lon, @lat), "CIRCLE") LIMIT  10;

So looking at the query above, my confusion is, where do the comparison between the latitude and langitude happens? Where in the query should I mention about my column latitude and langitude?

Looking at the output at the given link, it display something like

+--------------------------------+-------------------------------+
| astext(shape)                  | name                          |
+--------------------------------+-------------------------------+
| POINT(-122.3890954 37.6145378) | Tram stop:Terminal A          |
| POINT(-122.3899 37.6165902)    | Tram stop:Terminal G          |

Where do the POINT come from?

imin
  • 4,504
  • 13
  • 56
  • 103
  • did you take a moment to read the st_within documenation. – e4c5 Nov 14 '16 at 09:24
  • The linked article contains sample code. Which part of it is not clear? – Shadow Nov 14 '16 at 09:29
  • @e4c5 I did and it's not very clear to me – imin Nov 14 '16 at 10:02
  • you can use your point field as the first parameter to st_within. However if you are only interested in finding ites with in a given distance from a location, see this: http://stackoverflow.com/a/38771805/267540 – e4c5 Nov 14 '16 at 10:18
  • @e4c5 actually my table don't have point field, that's from the article that I referred to. My table only have latitude and langitude field. And thanks, I just read about spatial data types from the link that you gave, but I don't need it in my case (I just have/need latlng). So in my case, the query should be something like 'select name from location_final where st_within(point(latitude,langitude), ENVELOPE(LINESTRING(POINT(@rlon1, @rlat1), POINT(@rlon2, @rlat2)))) ORDER BY st_distance(POINT(@lon, @lat), "CIRCLE")' ? I believe there's still something off here.. – imin Nov 14 '16 at 10:41
  • don't expect anyone to read an article just to answer your question. If you are using st_* functions you need to have spatial datatypes. In fact since the release of mysql 5.7 storing latitude, longitude in two separate fields became obsolete. – e4c5 Nov 14 '16 at 12:04

0 Answers0