Poi based on latitude and longitude

I think you can use mysql function. If you need help kindly post details about records, your input, expected output etc

Dear Urtrivedi,
Below is some sample data from table poi. So for instance my input now is long 100.295 and lat 6.234. So my output will poiID 1 as that is the nearest point. So how best can I do in mysql because I am afraid I got a lot of data then it will need to do a full table search.

poiID  type      locationName               state       city       long  lat      
1      Airport   Sultan Abdul Halim Airport Kedah        Alor Setar 100.395 6.19207 
2      Airport   Lapangan Terbang Sultan Abdul Aziz Shah  Selangor    101.554 3.13335 
3       Airport   Lapangan Terbang Kertih   Terengganu    Kemaman 103.429 4.54132

Here is mysql function, open sql table in phpmyadmin, copy following to sql window, type $$ IN DELIMETER text box below.

CREATE  FUNCTION `get_poi_distance`(plong float,plat float, rowlong float, rowlat float) 
RETURNS float
BEGIN

DECLARE distance float;	


set distance :=(3958*3.1415926*sqrt((plat-rowlat)*(plat-rowlat) + cos(plat/57.29578)*cos(rowlat/57.29578)*(plong-

rowlong)*(plong-rowlong))/180);


RETURN distance ;
END$$

Now run following query in new sql tab.
I am limiting here result rows to 2, You may change it to 1.

SELECT a.*,get_poi_distance(100.295, 6.234,`long`,`lat`) distance FROM `poi` a WHERE 1
order by distance  limit 2

I am not sure how it will perform with thousands of records

Dear Urtrivedi,
By looking at it I guess it will do a full table scan right? Do you agree? How to remove the function if I want to disable it ?

If you want to find closest distance, you have to search for whole table in any case.

Instead of using self php loop code, I think keeping it in mysql will handle it effciently.

You need not to disable function. It uses resources only when you use it in your query.

any ways if you want to remove it permanently from mysql database then you may run following query

drop function get_poi_distance

Dear Urtrivedi,
It tells me I got a syntax error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2 what erro is it?

CREATE  FUNCTION `get_poi_distance`(plong float,plat float, rowlong float, rowlat float) 
RETURNS float
BEGIN
DECLARE distance float;	
set distance :=(3958*3.1415926*sqrt((plat-rowlat)*(plat-rowlat)+cos(plat/57.29578)*cos(rowlat/57.29578)*(plong-rowlong)*(plong-rowlong))/180);
RETURN distance ;
END$$

likely a paste-in error, copying from editor to the codehandler on the page, posting a pretty tight code

also do not forget to set delimiter to $$

Dear All,
What do you mean by the code handler because when I copy then I press enter to make sure looks exactly like even then I still get error. On the other hand I saw this codes work well I have tried using php but I would like to know on how to further optimise the speed of it.

the [code] bla bla bla [/code] handler on this page
I wrote it was likely an error copying from the reply's editor, to the code handler on the page, I meant on THIS page, it broke lines of text where it wasnt required

Dear Bob,
Now I got one additional information I got new set of lat and long to match plus a radius value in metres. How can I modify the early sql to cope with this new requirement?

you have a functional calculator for distance, you have a max value for distance

SELECT a.*,get_poi_distance(100.295, 6.234,`long`,`lat`) distance FROM `poi` a WHERE get_poi_distance <= $input_max_distance

this code sample not guaranteed, you have to look up the syntax for the sql version in use, its an idea

Dear Bob,
I think you got me wrong. Now the table got lat,long and radius colum. So what I need to do is that to find out if the lat long is within the radius? How to include the radius? Is it $input_max_distance is the radius colum?

radius is the input_max_distance

why is there a radius in the table, that seems unneccesary
you still have to run the whole table using the already defined calculator to calculate the distance from each poi to the current point, and select those less than the radius from the current point
I do not understand what you have done, so do not understand the question

TRY http://poifriend.com/ and ask them through the contact link, they are closing up and may not have any issues with proprietary code, they may even give to you the source of how they plot points in their poi set closest to the user's location

Dear Bob,
This one is actually for geo fence purpose. Meaning that to find out are in the geo fence and within the said radius. Sorry for confusion caused. Thank you. The distance calculate here is in metres is it?

the calculator at poi friend is
in metres less than 1km,
in km > 1 km,
but that seems to be an output convenience to make it easier to read
/1000 or*1000 in the code if they are willing to give it would not be hard

Dear Bob,
The one mysql is generating is in meter or km? Thank you.

Dear Bob,
I have tried to ask the poi friend people but not answer yet. So just would like to ask you the distance calculated via the mysql method is it in km or metres?

from your database, select two location whose distance you know very well, then run your query.
I think after looking the result, you can identify whether its giving in meters or kms

Dear Urtrivedi,
Thank you for the confirmation. So if say I have a polygon geo fence what mysql method will do the faster determination if a lat long is in the polygon geo fence? I have use SELECT MBRContains is there anything better or optimise then this one?

I could not comment on that, I have no idea about it.

Dear Urtrivedi,
Thank you lets see if other you have used it and could comment? Thank you.

I think you close this thread and open new thread with your new problem subject line

Usually, it's a good idea to make a first selection like this

SELECT * FROM POI
WHERE ABS(rowLat - pLat)< 1 AND ABS(rowLong - pLong)<1

and apply the Distance function only on the small selection.
< 1 means difference must be lower than 1 degree; you must adjust the value to your needs 0.1 degree for example.

Dear Jjc,
Thank you for the idea this will help in making it not scan the whole table right. But the <1 I got to decide it can be even less thant that too right?

If you are dealing with polygons, you may use the MBR (minimal bounding rectangle) function to select the the points candidates to be in the polygon
The MBR is a rectangle so you probably have access to the MBR.left MBR.right, MBR.top and MBR.bottom properties to select your points:

SELECT * FROM POI
WHERE (pLong > MBR.Left) AND (pLong < MBR.right) AND (pLat < MBR.Top) AND (pLat > MBR.Bottom)

if this does not work (no access to the bounding values), you can generate them by iterating your polygon to find manually the box properties
Pseudo code:
(left = 360; right = -360; top = -90; bottom = 90;
Repeat for all points
if pLong < left then left = pLong;
if pLong > right then right = pLong;
if pLat > bottom then bottom = pLat
if pLat < top then top = pLat
next point

Dear JJC,
Currently I am doing like this. What is your comment on this is it efficient or should I change to your method. The current method but is a bit slow.

$polygon  =  "GeomFromText('".$geoFenceString."')";
$point    =  "GeomFromText('Point(".$long."  ".$lat.")')";
       
$queryPolygon = "SELECT MBRContains(".$polygon.",".$point.") As geoFenceStatus";

Your method tel you if a particular point is in the box; it does not select the points of the database within the box; you cant use the indices; you have to iterate each record in the table.
And remember, a point in the box is not necessarily in the polygon.
your first move should be an SQL SELECT lat and long Within the box and then, test the selected points within the polygon; see the Within(g1,g2) function
http://dev.mysql.com/doc/refman/4.1/en/functions-that-test-spatial-relationships-between-geometries.html#function_within

Dear Jjc,
I dont understand the difference in the effect as one is just the opposite of the other right? So how will the within help to achieve better results? Thank you.