Hello people! I'm developing a system that needs to find places around a point informed by the user! I have a lot of place in a db and all of them has the coordinates information ( gotten by google maps api geocoding ); When I use mbrcontains to find all places into a polygon (circle with the informed radius) , its returns a lot of false positives and negatives places ( To try if the circle was fine , I got the points that I used to make the polygon and draw its in the Google Maps Api, and it is seems ok); But when I use mbrcontains and a polygon with points to a square, its seems to works fine!
Anybody would know what's happening ??? *My table :* CREATE TABLE `end_test` ( `id` int(11) NOT NULL auto_increment, `endereco` varchar(500) default NULL, `coord` point NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=71 DEFAULT CHARSET=utf8; *My data:* * * id | endereco | astext(coord) ---+--------------------------------------------------------------------------------+-------------------------------- 1 | Paulista 3500 | POINT(-23.5555595 -46.6627013) 2 | Paulista 80 | POINT(-23.5707504 -46.6447508) 3 | R. Cincinato Braga, 388 - Bela Vista, São Paulo, 01333-010, Brasil | POINT(-23.5664465 -46.6482093) 4 | Alameda Santos, 1307 - Jardim Paulista, São Paulo, 01419-001, Brasil | POINT(-23.5644398 -46.6548018) 5 | R. Rocha, 167 - Bela Vista, São Paulo, 01330-000, Brasil | POINT(-23.5576289 -46.6496816) 6 | R. Vergueiro, 1116 - Liberdade, São Paulo, 01504-000, Brasil | POINT(-23.571483 -46.6398504) 7 | R. Tutãia, 307 - Vila Mariana, São Paulo, 04007-001, Brasil | POINT(-23.5736478 -46.6511604) 8 | R. São Carlos do Pinhal, 508 - Bela Vista, São Paulo, 01333-000, Brasil | POINT(-23.5632843 -46.6518953) 9 | Pca. Da Se, 270 - Sã, São Paulo, Brasil | POINT(-23.550585 -46.634402) 10 | Av. Nove de Julho, 1456 - Bela Vista, São Paulo, 01312-001, Brasil | POINT(-23.5562014 -46.6509723) 26 | Av. Paulista, 150 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5703368 -46.6453062) 27 | Av. Paulista, 200 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5704947 -46.645445) 28 | Av. Paulista, 250 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5697362 -46.6460903) 29 | Av. Paulista, 300 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5693677 -46.6465566) 24 | Av. Paulista, 10 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5711635 -46.644195) 25 | Av. Paulista, 100 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5706322 -46.6449095) 30 | Av. Paulista, 350 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5689387 -46.6470852) 31 | Av. Paulista, 400 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5686021 -46.6475147) 32 | Av. Paulista, 500 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5680302 -46.6482495) 33 | Av. Paulista, 600 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5674129 -46.6490349) 34 | Av. Paulista, 700 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5669202 -46.6496368) 35 | Av. Paulista, 800 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5661058 -46.6506313) 36 | Av. Paulista, 910 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5656036 -46.6512472) 37 | Av. Paulista, 1010 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5649647 -46.6520309) 38 | Av. Paulista, 1110 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5643529 -46.6527773) 39 | Av. Paulista, 1210 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5637452 -46.6535176) 40 | Av. Paulista, 1310 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5629718 -46.6544436) 41 | Av. Paulista, 1310 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5629718 -46.6544436) 42 | Av. Paulista, 1410 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5625867 -46.654898) 43 | Av. Paulista, 1510 - Bela Vista, Sao Paulo - São Paulo, 01310-100, Brazil | POINT(-23.5618457 -46.6557136) 44 | Av. Paulista, 402 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5685907 -46.6475294) 45 | Av. Paulista, 602 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.567403 -46.6490469) 46 | Av. Paulista, 610 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5673637 -46.6490949) 47 | Av. Paulista, 410 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5685449 -46.6475882) 48 | Av. Paulista, 450 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5683161 -46.6478821) 49 | Av. Paulista, 650 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.567167 -46.6493352) 50 | Av. Paulista, 350 - Bela Vista, Sao Paulo - São Paulo, 01310-000, Brazil | POINT(-23.5689387 -46.6470852) 51 | Guarulhos - São Paulo, 07061-032, Brazil | POINT(-23.454063 -46.5499682) 52 | Guarulhos - São Paulo, 07061-032, Brazil | POINT(-23.454063 -46.5499682) 53 | R. Cincinato Braga, 520 - Bela Vista, Sao Paulo - São Paulo, 01333-010, Brazil | POINT(-23.5677665 -46.6466218) 54 | R. Cincinato Braga, 520 - Bela Vista, Sao Paulo - São Paulo, 01333-010, Brazil | POINT(-23.5677665 -46.6466218) 55 | Av. Paulista, 287 - Bela Vista, Sao Paulo - São Paulo, 01311-000, Brazil | POINT(-23.5696318 -46.6465093) 56 | Av. Paulista, 2500 - Consolaãão, Sao Paulo - São Paulo, 01310-300, Brazil | POINT(-23.5557395 -46.6625393) 57 | R. Mato Grosso, 309 - Anhanguera, Sao Paulo - São Paulo, 05268-110, Brazil | POINT(-23.4378126 -46.7891636) 58 | R. Frei Caneca, 569 - Consolaãão, Sao Paulo - São Paulo, 01307-001, Brazil | POINT(-23.553483 -46.6527548) *My Functions *: DELIMITER // DROP FUNCTION IF EXISTS geo_area// CREATE FUNCTION geo_area(origem point,r int,points int) RETURNS text DETERMINISTIC BEGIN -- Cria um poligono como area de busca a partir de um ponto de origem com o raio informado (em metros) -- Creates a text representation of the polygon with the area from the source point (in meters) -- Initialize Vars DECLARE p1,i INT; DECLARE i_x, i_y,p_x,p_y,coord_radius,a,d2r,r2d,Cx,Cy,Clat,Clng,theta float; DECLARE polyg,coord_start text; SET polyg=''; SET i_y = y(origem) ; SET i_x = x(origem) ; SET d2r = PI()/180 ; -- degrees to radians SET r2d = 180/PI() ; -- radians to degrees SET Clat = (r/6378100) * r2d ; -- using 6378100 as earth's radius (In meters) SET Clng = Clat/cos(i_x*d2r); SET i=0; SET @points=points-1; -- sET polyg = concat( "\nlat : " , i_y , ' | lng : ' , i_x, '| d2r : ',d2r , '|r2d : ', r2d , '| Clat: ',Clat , '| Clng : ', Clng ,"\n" ); -- Add each point in the circle WHILE i < @points DO SET theta = PI() * ( i / (@points / 2) ) ; SET Cx = i_x + (Clat * sin(theta)); SET Cy = i_y + (Clng * cos(theta)) ; -- SET polyg = CONCAT(polyg , "\n" , '| Theta :' , theta , '| Coord : ' ); SET polyg = CONCAT(polyg , Cx , ' ' , Cy ); SET i = i+1; IF coord_start IS NULL THEN SET coord_start = polyg; END IF; IF i < @points THEN SET polyg = CONCAT( polyg , ','); END IF; END WHILE; IF polyg <> '' THEN SET polyg = CONCAT("POLYGON( (",polyg,' , ',coord_start," ) )"); END IF; RETURN polyg; END; // DELIMITER ; DELIMITER // DROP FUNCTION IF EXISTS geo_area_polygon// CREATE FUNCTION geo_area_polygon( lat float, lng float , r int, points int) RETURNS polygon DETERMINISTIC -- Retorna o poligono referente ao raio de pesquisa -- Returns the binary representation of a polygon BEGIN RETURN GeomFromText(geo_area(GeomFromText(CONCAT('POINT(',lat,lng,')')),r,points)); END; // DELIMITER ; *My Queries :* set @center=geomfromtext('POINT(-23.5555595 -46.6627013)'); set @circle=geo_area_polygon(X(@center),Y(@center),1000,30); set @square=GEOMFROMTEXT('POLYGON(( -23.564499999999978 -46.65290048262034, -23.5466 -46.65290048262034, -23.5466 -46.67249994032346, -23.564499999999978 -46.67249994032346, -23.564499999999978-46.65290048262034))'); select endereco, MBRContains( @circle, coord ) in_area_circle, MBRContains( @square, coord ) in_area_square from end_test; Ps: Sorry for my english!!! -- Att: Thiago Risso