Обсуждение: Geometric data type for an arc.
Hi,
Documentation at http://www.postgresql.org/docs/9.1/interactive/datatype-geometric.html#AEN6566 provides an explanation of the PostgreSQL geometric data type for a circle, defined by the two coordinates of a center point and a value that defines the radius of the circle.
What would be the easiest way to store in PostgreSQL an arc or partial circle, defined by the two coordinates of a center point, the two coordinates of the two end points of the arc and the radius of the circle of which the arc is a part?
Regards, Jk.
On 10 May 2012 16:24, John W. Kitz <John.Kitz@xs4all.nl> wrote: > What would be the easiest way to store in PostgreSQL an arc or partial > circle, defined by the two coordinates of a center point, the two > coordinates of the two end points of the arc and the radius of the circle of > which the arc is a part? This doesn't uniquely define the arc, you know. This defines two arcs (plus the radius is redundant, since it is also the distance between the centre and one of the points and can be computed from the given coordinates). I'd imagine the canonical way of storing an arc of a circle in a DB would be to store the circle to which the arc belongs to (see link you posted) along with the angles the two radii to the end points of the arc have with the positive X axis, and an indicator as to which of the two arcs you intended. The angles can be calculated as invsin(y/r) for each of the points where r is the radius. Take a look at this picture (MS Paint, sorry!): https://picasaweb.google.com/112929706764240025005/STUFF#5740866485548434802 You know the coordinates of C (c1, c2) and A & B ((a1, a2) and (b1, b2) respectively), as well as r, the radius of the circle. The first thing to do is to perform these sanity checks: r^2 = (a1-c1)^2 + (a2-c2)^2 r^2 = (b1-c1)^2 + (b2-c2)^2 Assuming they are fine, the idea is to store the circle itself (completely identified by coordinates of the centre and the radius), and the angles ACD and BCD. These angles can be calculated by: ACD = invsin((a2-c2)/r) BCD = invsin((b2-c2)/r) The last piece of information needed is to know which of the two arcs - the small one or the big one - you are interested in. You can do this by convention, by saying that you will traverse the circle in the counter-clockwise direction and the angle stored in the first column in the database is that of the first endpoint encountered while traversing. The reverse calculation is equally simple: For A: x = c1 + r . cos ACD y = c2 + r . sin ACD For B: x = c1 + r . cos BCD y = c2 + r . sin BCD Binand
Binand, First of all thanks for taking the time to reply. You are right of course that "two coordinates of a center point, two coordinates of two end points of an arc and the radius of the circle of which the arc is a part" as mentioned in my initial post defines two arcs (which I have seen referred to as the minor and major arc in some documentation) instead of one that collectively define the circumference of a circle. I realized that a couple of minutes after I sent the post. So storing an arc in a DB would require a data type consisting of the center point (C) coordinates XC,YC, both coordinates of the endpoints of the arc (A1 and A2) XA1,YA1 and XA2,YA2, the radius (R) of the circle and some value that indicates which of the two arcs the data defines, which may be e.g. numeric (e.g. the angle in degrees between the two radii connecting the center point to the endpoints of the arc), boolean (e.g. 1 for the major arc and 0 for the minor one), character (e.g. "b" for the major or big arc and "s" for the minor or small one) or text (e.g. "major" for the big arc and "minor" for the small one). Where Boolean seems to most obvious choice in order to use the least amount of space. The benefit that I imagine storing the arc in a DB once over calculating the arc from a stored circle and several other stored values as appropriate every time the arc is needed is reduced application processing and hence time. The down side I assume being that it would require the addition of a geometric data type for an arc. Correct? Thanks, Jk. -----Original Message----- From: binand@gmail.com [mailto:binand@gmail.com] On Behalf Of Binand Sethumadhavan Sent: donderdag 10 mei 2012 13:37 To: John.Kitz@xs4all.nl Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Geometric data type for an arc. On 10 May 2012 16:24, John W. Kitz <John.Kitz@xs4all.nl> wrote: > What would be the easiest way to store in PostgreSQL an arc or partial > circle, defined by the two coordinates of a center point, the two > coordinates of the two end points of the arc and the radius of the > circle of which the arc is a part? This doesn't uniquely define the arc, you know. This defines two arcs (plus the radius is redundant, since it is also the distance between the centre and one of the points and can be computed from the given coordinates). I'd imagine the canonical way of storing an arc of a circle in a DB would be to store the circle to which the arc belongs to (see link you posted) along with the angles the two radii to the end points of the arc have with the positive X axis, and an indicator as to which of the two arcs you intended. The angles can be calculated as invsin(y/r) for each of the points where r is the radius. Take a look at this picture (MS Paint, sorry!): https://picasaweb.google.com/112929706764240025005/STUFF#5740866485548434802 You know the coordinates of C (c1, c2) and A & B ((a1, a2) and (b1, b2) respectively), as well as r, the radius of the circle. The first thing to do is to perform these sanity checks: r^2 = (a1-c1)^2 + (a2-c2)^2 r^2 = (b1-c1)^2 + (b2-c2)^2 Assuming they are fine, the idea is to store the circle itself (completely identified by coordinates of the centre and the radius), and the angles ACD and BCD. These angles can be calculated by: ACD = invsin((a2-c2)/r) BCD = invsin((b2-c2)/r) The last piece of information needed is to know which of the two arcs - the small one or the big one - you are interested in. You can do this by convention, by saying that you will traverse the circle in the counter-clockwise direction and the angle stored in the first column in the database is that of the first endpoint encountered while traversing. The reverse calculation is equally simple: For A: x = c1 + r . cos ACD y = c2 + r . sin ACD For B: x = c1 + r . cos BCD y = c2 + r . sin BCD Binand
why don't you use a composite data type for define a arc type? you could add the result field in the type definition like CREATE TYPE arc AS ( center point, A point, B point, lenght double ); http://www.postgresql.org/docs/9.1/interactive/rowtypes.html 2012/5/10 John W. Kitz <John.Kitz@xs4all.nl>: > Binand, > > First of all thanks for taking the time to reply. > > You are right of course that "two coordinates of a center point, two > coordinates of two end points of an arc and the radius of the circle of > which the arc is a part" as mentioned in my initial post defines two arcs > (which I have seen referred to as the minor and major arc in some > documentation) instead of one that collectively define the circumference of > a circle. I realized that a couple of minutes after I sent the post. > > So storing an arc in a DB would require a data type consisting of the center > point (C) coordinates XC,YC, both coordinates of the endpoints of the arc > (A1 and A2) XA1,YA1 and XA2,YA2, the radius (R) of the circle and some value > that indicates which of the two arcs the data defines, which may be e.g. > numeric (e.g. the angle in degrees between the two radii connecting the > center point to the endpoints of the arc), boolean (e.g. 1 for the major arc > and 0 for the minor one), character (e.g. "b" for the major or big arc and > "s" for the minor or small one) or text (e.g. "major" for the big arc and > "minor" for the small one). > > Where Boolean seems to most obvious choice in order to use the least amount > of space. > > The benefit that I imagine storing the arc in a DB once over calculating the > arc from a stored circle and several other stored values as appropriate > every time the arc is needed is reduced application processing and hence > time. > > The down side I assume being that it would require the addition of a > geometric data type for an arc. Correct? > > Thanks, Jk. > > -----Original Message----- > From: binand@gmail.com [mailto:binand@gmail.com] On Behalf Of Binand > Sethumadhavan > Sent: donderdag 10 mei 2012 13:37 > To: John.Kitz@xs4all.nl > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Geometric data type for an arc. > > On 10 May 2012 16:24, John W. Kitz <John.Kitz@xs4all.nl> wrote: >> What would be the easiest way to store in PostgreSQL an arc or partial >> circle, defined by the two coordinates of a center point, the two >> coordinates of the two end points of the arc and the radius of the >> circle of which the arc is a part? > > This doesn't uniquely define the arc, you know. This defines two arcs (plus > the radius is redundant, since it is also the distance between the centre > and one of the points and can be computed from the given coordinates). > > I'd imagine the canonical way of storing an arc of a circle in a DB would be > to store the circle to which the arc belongs to (see link you > posted) along with the angles the two radii to the end points of the arc > have with the positive X axis, and an indicator as to which of the two arcs > you intended. The angles can be calculated as invsin(y/r) for each of the > points where r is the radius. > > Take a look at this picture (MS Paint, sorry!): > > https://picasaweb.google.com/112929706764240025005/STUFF#5740866485548434802 > > You know the coordinates of C (c1, c2) and A & B ((a1, a2) and (b1, > b2) respectively), as well as r, the radius of the circle. > > The first thing to do is to perform these sanity checks: > > r^2 = (a1-c1)^2 + (a2-c2)^2 > r^2 = (b1-c1)^2 + (b2-c2)^2 > > Assuming they are fine, the idea is to store the circle itself (completely > identified by coordinates of the centre and the radius), and the angles ACD > and BCD. These angles can be calculated by: > > ACD = invsin((a2-c2)/r) > BCD = invsin((b2-c2)/r) > > The last piece of information needed is to know which of the two arcs > - the small one or the big one - you are interested in. You can do this by > convention, by saying that you will traverse the circle in the > counter-clockwise direction and the angle stored in the first column in the > database is that of the first endpoint encountered while traversing. > > The reverse calculation is equally simple: > > For A: > > x = c1 + r . cos ACD > y = c2 + r . sin ACD > > For B: > > x = c1 + r . cos BCD > y = c2 + r . sin BCD > > Binand > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice -- Simone
Simone, Tnx. Does anybody know how to submit a feature request for consideration to the developers of PostgreSQL? Regards, Jk. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Simone Sanfratello Sent: donderdag 10 mei 2012 16:05 To: John.Kitz@xs4all.nl Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Geometric data type for an arc. why don't you use a composite data type for define a arc type? you could add the result field in the type definition like CREATE TYPE arc AS ( center point, A point, B point, lenght double ); http://www.postgresql.org/docs/9.1/interactive/rowtypes.html 2012/5/10 John W. Kitz <John.Kitz@xs4all.nl>: > Binand, > > First of all thanks for taking the time to reply. > > You are right of course that "two coordinates of a center point, two > coordinates of two end points of an arc and the radius of the circle > of which the arc is a part" as mentioned in my initial post defines > two arcs (which I have seen referred to as the minor and major arc in > some > documentation) instead of one that collectively define the > circumference of a circle. I realized that a couple of minutes after I sent the post. > > So storing an arc in a DB would require a data type consisting of the > center point (C) coordinates XC,YC, both coordinates of the endpoints > of the arc > (A1 and A2) XA1,YA1 and XA2,YA2, the radius (R) of the circle and some > value that indicates which of the two arcs the data defines, which may be e.g. > numeric (e.g. the angle in degrees between the two radii connecting > the center point to the endpoints of the arc), boolean (e.g. 1 for the > major arc and 0 for the minor one), character (e.g. "b" for the major > or big arc and "s" for the minor or small one) or text (e.g. "major" > for the big arc and "minor" for the small one). > > Where Boolean seems to most obvious choice in order to use the least > amount of space. > > The benefit that I imagine storing the arc in a DB once over > calculating the arc from a stored circle and several other stored > values as appropriate every time the arc is needed is reduced > application processing and hence time. > > The down side I assume being that it would require the addition of a > geometric data type for an arc. Correct? > > Thanks, Jk. > > -----Original Message----- > From: binand@gmail.com [mailto:binand@gmail.com] On Behalf Of Binand > Sethumadhavan > Sent: donderdag 10 mei 2012 13:37 > To: John.Kitz@xs4all.nl > Cc: pgsql-novice@postgresql.org > Subject: Re: [NOVICE] Geometric data type for an arc. > > On 10 May 2012 16:24, John W. Kitz <John.Kitz@xs4all.nl> wrote: >> What would be the easiest way to store in PostgreSQL an arc or >> partial circle, defined by the two coordinates of a center point, the >> two coordinates of the two end points of the arc and the radius of >> the circle of which the arc is a part? > > This doesn't uniquely define the arc, you know. This defines two arcs > (plus the radius is redundant, since it is also the distance between > the centre and one of the points and can be computed from the given coordinates). > > I'd imagine the canonical way of storing an arc of a circle in a DB > would be to store the circle to which the arc belongs to (see link you > posted) along with the angles the two radii to the end points of the > arc have with the positive X axis, and an indicator as to which of the > two arcs you intended. The angles can be calculated as invsin(y/r) for > each of the points where r is the radius. > > Take a look at this picture (MS Paint, sorry!): > > https://picasaweb.google.com/112929706764240025005/STUFF#5740866485548 > 434802 > > You know the coordinates of C (c1, c2) and A & B ((a1, a2) and (b1, > b2) respectively), as well as r, the radius of the circle. > > The first thing to do is to perform these sanity checks: > > r^2 = (a1-c1)^2 + (a2-c2)^2 > r^2 = (b1-c1)^2 + (b2-c2)^2 > > Assuming they are fine, the idea is to store the circle itself > (completely identified by coordinates of the centre and the radius), > and the angles ACD and BCD. These angles can be calculated by: > > ACD = invsin((a2-c2)/r) > BCD = invsin((b2-c2)/r) > > The last piece of information needed is to know which of the two arcs > - the small one or the big one - you are interested in. You can do > this by convention, by saying that you will traverse the circle in the > counter-clockwise direction and the angle stored in the first column > in the database is that of the first endpoint encountered while traversing. > > The reverse calculation is equally simple: > > For A: > > x = c1 + r . cos ACD > y = c2 + r . sin ACD > > For B: > > x = c1 + r . cos BCD > y = c2 + r . sin BCD > > Binand > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To > make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice -- Simone -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
On Thu, May 10, 2012 at 10:10 AM, John W. Kitz <John.Kitz@xs4all.nl> wrote: > Simone, > > Tnx. > > Does anybody know how to submit a feature request for consideration to the > developers of PostgreSQL? The support for geometric types as it stands is fairly weak -- there might be some reluctance to extend them (which means more operators, casts, etc) without a more general look at their issues (for example, why can't you extract points out of a box type)? Would a vanilla composite type work for you? If not, why? merlin
Merlin Moncure <mmoncure@gmail.com> writes: > On Thu, May 10, 2012 at 10:10 AM, John W. Kitz <John.Kitz@xs4all.nl> wrote: >> Does anybody know how to submit a feature request for consideration to the >> developers of PostgreSQL? > The support for geometric types as it stands is fairly weak -- there > might be some reluctance to extend them (which means more operators, > casts, etc) without a more general look at their issues (for example, > why can't you extract points out of a box type)? The first question that would be asked is whether PostGIS doesn't do what you want. There's not that much enthusiasm for improving the core geometric types because PostGIS has covered the territory. regards, tom lane
On Thu, May 10, 2012 at 11:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> On Thu, May 10, 2012 at 10:10 AM, John W. Kitz <John.Kitz@xs4all.nl> wrote: >>> Does anybody know how to submit a feature request for consideration to the >>> developers of PostgreSQL? > >> The support for geometric types as it stands is fairly weak -- there >> might be some reluctance to extend them (which means more operators, >> casts, etc) without a more general look at their issues (for example, >> why can't you extract points out of a box type)? > > The first question that would be asked is whether PostGIS doesn't do > what you want. There's not that much enthusiasm for improving the core > geometric types because PostGIS has covered the territory. It doesn't -- it's pretty much a polygon based system -- and PostGIS is a huge dependency if all you want to do is store an arc for later extraction. IMO, the threshold for advising PostGIS should be manipulation and precise searching, not necessarily working with geometric objects. I've often wondered if the built in geometric types could be approximated with composites and arrays -- trading an efficiency loss for flexibility and standardization. merlin