Gist And MBRContains
am 09.06.2011 18:25:14 von Thiago Risso--000e0cdff82ada17ac04a549e567
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Hello people!
I'm developing a system that needs to find places around a point informed b=
y
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 place=
s
( 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=3DMyISAM AUTO_INCREMENT=3D71 DEFAULT CHARSET=3Dutf8;
*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=E3o Paulo, 01333-010, Brasil
| POINT(-23.5664465 -46.6482093)
4 | Alameda Santos, 1307 - Jardim Paulista, S=E3o Paulo, 01419-001, Brasil
| POINT(-23.5644398 -46.6548018)
5 | R. Rocha, 167 - Bela Vista, S=E3o Paulo, 01330-000, Brasil
| POINT(-23.5576289 -46.6496816)
6 | R. Vergueiro, 1116 - Liberdade, S=E3o Paulo, 01504-000, Brasil
| POINT(-23.571483 -46.6398504)
7 | R. Tut=E3ia, 307 - Vila Mariana, S=E3o Paulo, 04007-001, Brasil
| POINT(-23.5736478 -46.6511604)
8 | R. S=E3o Carlos do Pinhal, 508 - Bela Vista, S=E3o Paulo, 01333-000, B=
rasil
| POINT(-23.5632843 -46.6518953)
9 | Pca. Da Se, 270 - S=E3, S=E3o Paulo, Brasil
| POINT(-23.550585 -46.634402)
10 | Av. Nove de Julho, 1456 - Bela Vista, S=E3o Paulo, 01312-001, Brasil
| POINT(-23.5562014 -46.6509723)
26 | Av. Paulista, 150 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5703368 -46.6453062)
27 | Av. Paulista, 200 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5704947 -46.645445)
28 | Av. Paulista, 250 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5697362 -46.6460903)
29 | Av. Paulista, 300 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5693677 -46.6465566)
24 | Av. Paulista, 10 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000, Bra=
zil
| POINT(-23.5711635 -46.644195)
25 | Av. Paulista, 100 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5706322 -46.6449095)
30 | Av. Paulista, 350 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5689387 -46.6470852)
31 | Av. Paulista, 400 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5686021 -46.6475147)
32 | Av. Paulista, 500 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5680302 -46.6482495)
33 | Av. Paulista, 600 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5674129 -46.6490349)
34 | Av. Paulista, 700 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5669202 -46.6496368)
35 | Av. Paulista, 800 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5661058 -46.6506313)
36 | Av. Paulista, 910 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5656036 -46.6512472)
37 | Av. Paulista, 1010 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5649647 -46.6520309)
38 | Av. Paulista, 1110 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5643529 -46.6527773)
39 | Av. Paulista, 1210 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5637452 -46.6535176)
40 | Av. Paulista, 1310 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5629718 -46.6544436)
41 | Av. Paulista, 1310 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5629718 -46.6544436)
42 | Av. Paulista, 1410 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5625867 -46.654898)
43 | Av. Paulista, 1510 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-100,
Brazil | POINT(-23.5618457 -46.6557136)
44 | Av. Paulista, 402 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5685907 -46.6475294)
45 | Av. Paulista, 602 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.567403 -46.6490469)
46 | Av. Paulista, 610 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5673637 -46.6490949)
47 | Av. Paulista, 410 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5685449 -46.6475882)
48 | Av. Paulista, 450 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5683161 -46.6478821)
49 | Av. Paulista, 650 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.567167 -46.6493352)
50 | Av. Paulista, 350 - Bela Vista, Sao Paulo - S=E3o Paulo, 01310-000,
Brazil | POINT(-23.5689387 -46.6470852)
51 | Guarulhos - S=E3o Paulo, 07061-032, Brazil
| POINT(-23.454063 -46.5499682)
52 | Guarulhos - S=E3o Paulo, 07061-032, Brazil
| POINT(-23.454063 -46.5499682)
53 | R. Cincinato Braga, 520 - Bela Vista, Sao Paulo - S=E3o Paulo, 01333-0=
10,
Brazil | POINT(-23.5677665 -46.6466218)
54 | R. Cincinato Braga, 520 - Bela Vista, Sao Paulo - S=E3o Paulo, 01333-0=
10,
Brazil | POINT(-23.5677665 -46.6466218)
55 | Av. Paulista, 287 - Bela Vista, Sao Paulo - S=E3o Paulo, 01311-000,
Brazil | POINT(-23.5696318 -46.6465093)
56 | Av. Paulista, 2500 - Consolaãão, Sao Paulo - S=E3o Paulo, 01310-30=
0,
Brazil | POINT(-23.5557395 -46.6625393)
57 | R. Mato Grosso, 309 - Anhanguera, Sao Paulo - S=E3o Paulo, 05268-110,
Brazil | POINT(-23.4378126 -46.7891636)
58 | R. Frei Caneca, 569 - Consolaãão, Sao Paulo - S=E3o Paulo, 01307-0=
01,
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=3D'';
SET i_y =3D y(origem) ;
SET i_x =3D x(origem) ;
SET d2r =3D PI()/180 ; -- degrees to radians
SET r2d =3D 180/PI() ; -- radians to degrees
SET Clat =3D (r/6378100) * r2d ; -- using 6378100 as earth's radius (I=
n
meters)
SET Clng =3D Clat/cos(i_x*d2r);
SET i=3D0;
SET @points=3Dpoints-1;
-- sET polyg =3D 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 =3D PI() * ( i / (@points / 2) ) ;
SET Cx =3D i_x + (Clat * sin(theta));
SET Cy =3D i_y + (Clng * cos(theta)) ;
-- SET polyg =3D CONCAT(polyg , "\n" , '| Theta :' , theta , '| Coord : ' )=
;
SET polyg =3D CONCAT(polyg , Cx , ' ' , Cy );
SET i =3D i+1;
IF coord_start IS NULL THEN
SET coord_start =3D polyg;
END IF;
IF i < @points THEN
SET polyg =3D CONCAT( polyg , ',');
END IF;
END WHILE;
IF polyg <> '' THEN
SET polyg =3D 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=3Dgeomfromtext('POINT(-23.5555595 -46.6627013)');
set @circle=3Dgeo_area_polygon(X(@center),Y(@center),1000,30);
set @square=3DGEOMFROMTEXT('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!!!
--=20
Att:
Thiago Risso
--000e0cdff82ada17ac04a549e567--