how do i create a circle Geometry with a radius and co-ordinates of center, using MySQL Spatial Extensions?

Question

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.

Fulltime

Answer 1

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.

Bhatt

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. – Fulltime

@user2052129: No, there is nothing similar in earlier versions - you'd have to manually build a polygon of sufficient resolution. – Bhatt

Thanks...what metric does do hold?..is it miles or meters...? where and how can we ever set that. – Fulltime

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 – Fulltime

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. – Sind

@DnfD you could set it up as geometry, then use MBRContains, check out this answer: https://mcmap.net/q/119183/-mysql-selecting-near-a-spatial-point – Exemplary

Answer 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.

Wearable