Thursday, April 4, 2013

Circle polyline in Oracle?

Did you ever want to create a circle polyline to Oracle spatial datatype SDO_GEOMETRY? I did! But I had a little problem - I had only to vertices. The problem is this:
If you want to create polyline circle in Oracle, you need to create two opposite arcs. And if you want to create two arcs you need to know three points, because each circular arc is in Oracle described using three coordinates: First is the arc's start point, second is any point on the arc, and the third is the arc's end point.
This SQL-script should provide you the way to create a polyline circle from two vertices laying on the circle:

select mdsys.sdo_geometry(2002,YOUR_SRID,null,mdsys.sdo_elem_info_array(1,2,2),mdsys.sdo_ordinate_array(
X1,Y1, --Vertex number 1
(X2 - X1) / 2 + X1 + (sqrt(power(X2-X1,2) + power(Y2-Y1,2))) / 2 * cos(atan2(Y2-Y1,X2-X1) + 3.141592653589793/2),
(Y2 - Y1) / 2 + Y1 + (sqrt(power(X2-X1,2) + power(Y2-Y1,2))) / 2 * sin(atan2(Y2-Y1,X2-X1) + 3.141592653589793/2), --Vertex number 2
X2,Y2, --Vertex number 3
(X1 - X2) / 2 + X2 + (sqrt(power(X1-X2,2) + power(Y1-Y2,2))) / 2 * cos(atan2(Y1-Y2,X1-X2) + 3.141592653589793/2),
(Y1 - Y2) / 2 + Y2 + (sqrt(power(X1-X2,2) + power(Y1-Y2,2))) / 2 * sin(atan2(Y1-Y2,X1-X2) + 3.141592653589793/2), --Vertex number 4
X1,Y1  --Vertex number 5
)) from TABLE_WITH_VERTICES




That's it! Keep drawing... :)

No comments:

Post a Comment