8

I am trying to create a Circle Geometry in MySQL using the co-ordinates of the center and a radius. I searched everywhere...all i could find in the MySQL doc on the site were for polygons. May be i am looking in the wrong place. can anybody help me with an appropriate SQL that can help me create a table that stores this Circle geometry as one of the columns in the table? Also, i am not even sure if there is a way to do so in MySQL?..The version i am using is MySQL 5.6.

Thanks in advance.

user2052129
  • 231
  • 4
  • 11

2 Answers2

9

As of MySQL v5.6.1, you can use Buffer(g, d):

Returns a geometry that represents all points whose distance from the geometry value g is less than or equal to a distance of d.

Obviously, in your case g should be the point at the centre of the circle and d should be its radius.

AlexMax
  • 1,204
  • 14
  • 36
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Great. Thanks for helping me see this. I somehow overlooked this one. one other things. is this only available as of 5.6.1...is there anything similar in previous versions. A circle geometry seems to be a basic case and its seems odd that this kind of geometry has not been supported. – user2052129 Jun 11 '13 at 13:59
  • @user2052129: No, there is nothing similar in earlier versions - you'd have to manually build a polygon of sufficient resolution. – eggyal Jun 11 '13 at 14:04
  • Thanks...what metric does do hold?..is it miles or meters...? where and how can we ever set that. – user2052129 Jun 11 '13 at 14:36
  • Sorry for the typo. i meant the Distance D in the Buffer function Buffer (g, d).. I am trying to get the metric on that. I am using the Latitude and Longitude of the earth. so is there a way to know the metric on that one. Thanks – user2052129 Jun 11 '13 at 15:50
  • 1
    What is the proper way to set up this sql statement if you have `lat` and `long` as two columns? I am struggling with this. – Nubtacular Jul 25 '13 at 20:28
  • @DnfD you could set it up as geometry, then use MBRContains, check out this answer: http://stackoverflow.com/a/9401898/2302437 – electronix384128 Aug 05 '14 at 21:54
2

There are two Parts: A.For given tested points you have to check their relation with given circle. B.You want to generate points on circumference of given circle.

A.Yes, First of all take the distance between your given point(test Point) and the centre of circle point. Both of these points are defined in Latitude and longitude. Distance formula between two points(x1,y1) and (x2,y2) is distance d= sqrt[ (x2-x1)^2 + (y2-y1)^2 ]. Now,

  1. If this distance is less than radius of circle then your tested point is inside your circle.
  2. If this distance is Greater than radius then tested point is outside the circle.
  3. If this calculated distance is equal to radius of circle then this tested point is on your circle i.e. on the circumference of your circle.

B. In a circle the total angle theta is 360 degree or 2*Pi in radians. For given Circle whose centre is (x1, y1) and radius is r.

x = x1 + r * cos(theta)

y = y1 + r * sin(theta)

where, theta is running from Zero to 2*Pi and Pi is 3.1415.

Depending upon how you do it. Example: if you wants 10 points on circle, then increment=(2*Pi-Zero)/10.

fist theta is zero, then theta is Zero+increment, then theta is Zero +increment+increment i.e. 2* increment and then zero + 3*increment and then so on. unless you get theta equal to 2*Pi.

For all above thetas calculate x and y. These all x and y coordinate points are on the circumference of the circle.

Azeem Rehman
  • 21
  • 1