Movatterモバイル変換


[0]ホーム

URL:



Facebook
Postgres Pro
Facebook
Downloads
9.11. Geometric Functions and Operators
Prev UpChapter 9. Functions and OperatorsHome Next

9.11. Geometric Functions and Operators#

The geometric typespoint,box,lseg,line,path,polygon, andcircle have a large set of native support functions and operators, shown inTable 9.36,Table 9.37, andTable 9.38.

Table 9.36. Geometric Operators

Operator

Description

Example(s)

geometric_type+pointgeometric_type

Adds the coordinates of the secondpoint to those of each point of the first argument, thus performing translation. Available forpoint,box,path,circle.

box '(1,1),(0,0)' + point '(2,0)'(3,1),(2,0)

path+pathpath

Concatenates two open paths (returns NULL if either path is closed).

path '[(0,0),(1,1)]' + path '[(2,2),(3,3),(4,4)]'[(0,0),(1,1),(2,2),(3,3),(4,4)]

geometric_type-pointgeometric_type

Subtracts the coordinates of the secondpoint from those of each point of the first argument, thus performing translation. Available forpoint,box,path,circle.

box '(1,1),(0,0)' - point '(2,0)'(-1,1),(-2,0)

geometric_type*pointgeometric_type

Multiplies each point of the first argument by the secondpoint (treating a point as being a complex number represented by real and imaginary parts, and performing standard complex multiplication). If one interprets the secondpoint as a vector, this is equivalent to scaling the object's size and distance from the origin by the length of the vector, and rotating it counterclockwise around the origin by the vector's angle from thex axis. Available forpoint,box,[a]path,circle.

path '((0,0),(1,0),(1,1))' * point '(3.0,0)'((0,0),(3,0),(3,3))

path '((0,0),(1,0),(1,1))' * point(cosd(45), sind(45))((0,0),​(0.7071067811865475,0.7071067811865475),​(0,1.414213562373095))

geometric_type/pointgeometric_type

Divides each point of the first argument by the secondpoint (treating a point as being a complex number represented by real and imaginary parts, and performing standard complex division). If one interprets the secondpoint as a vector, this is equivalent to scaling the object's size and distance from the origin down by the length of the vector, and rotating it clockwise around the origin by the vector's angle from thex axis. Available forpoint,box,[a]path,circle.

path '((0,0),(1,0),(1,1))' / point '(2.0,0)'((0,0),(0.5,0),(0.5,0.5))

path '((0,0),(1,0),(1,1))' / point(cosd(45), sind(45))((0,0),​(0.7071067811865476,-0.7071067811865476),​(1.4142135623730951,0))

@-@geometric_typedouble precision

Computes the total length. Available forlseg,path.

@-@ path '[(0,0),(1,0),(1,1)]'2

@@geometric_typepoint

Computes the center point. Available forbox,lseg,polygon,circle.

@@ box '(2,2),(0,0)'(1,1)

#geometric_typeinteger

Returns the number of points. Available forpath,polygon.

# path '((1,0),(0,1),(-1,0))'3

geometric_type#geometric_typepoint

Computes the point of intersection, or NULL if there is none. Available forlseg,line.

lseg '[(0,0),(1,1)]' # lseg '[(1,0),(0,1)]'(0.5,0.5)

box#boxbox

Computes the intersection of two boxes, or NULL if there is none.

box '(2,2),(-1,-1)' # box '(1,1),(-2,-2)'(1,1),(-1,-1)

geometric_type##geometric_typepoint

Computes the closest point to the first object on the second object. Available for these pairs of types: (point,box), (point,lseg), (point,line), (lseg,box), (lseg,lseg), (line,lseg).

point '(0,0)' ## lseg '[(2,0),(0,2)]'(1,1)

geometric_type<->geometric_typedouble precision

Computes the distance between the objects. Available for all seven geometric types, for all combinations ofpoint with another geometric type, and for these additional pairs of types: (box,lseg), (lseg,line), (polygon,circle) (and the commutator cases).

circle '<(0,0),1>' <-> circle '<(5,0),1>'3

geometric_type@>geometric_typeboolean

Does first object contain second? Available for these pairs of types: (box,point), (box,box), (path,point), (polygon,point), (polygon,polygon), (circle,point), (circle,circle).

circle '<(0,0),2>' @> point '(1,1)'t

geometric_type<@geometric_typeboolean

Is first object contained in or on second? Available for these pairs of types: (point,box), (point,lseg), (point,line), (point,path), (point,polygon), (point,circle), (box,box), (lseg,box), (lseg,line), (polygon,polygon), (circle,circle).

point '(1,1)' <@ circle '<(0,0),2>'t

geometric_type&&geometric_typeboolean

Do these objects overlap? (One point in common makes this true.) Available forbox,polygon,circle.

box '(1,1),(0,0)' && box '(2,2),(0,0)'t

geometric_type<<geometric_typeboolean

Is first object strictly left of second? Available forpoint,box,polygon,circle.

circle '<(0,0),1>' << circle '<(5,0),1>'t

geometric_type>>geometric_typeboolean

Is first object strictly right of second? Available forpoint,box,polygon,circle.

circle '<(5,0),1>' >> circle '<(0,0),1>'t

geometric_type&<geometric_typeboolean

Does first object not extend to the right of second? Available forbox,polygon,circle.

box '(1,1),(0,0)' &< box '(2,2),(0,0)'t

geometric_type&>geometric_typeboolean

Does first object not extend to the left of second? Available forbox,polygon,circle.

box '(3,3),(0,0)' &> box '(2,2),(0,0)'t

geometric_type<<|geometric_typeboolean

Is first object strictly below second? Available forpoint,box,polygon,circle.

box '(3,3),(0,0)' <<| box '(5,5),(3,4)'t

geometric_type|>>geometric_typeboolean

Is first object strictly above second? Available forpoint,box,polygon,circle.

box '(5,5),(3,4)' |>> box '(3,3),(0,0)'t

geometric_type&<|geometric_typeboolean

Does first object not extend above second? Available forbox,polygon,circle.

box '(1,1),(0,0)' &<| box '(2,2),(0,0)'t

geometric_type|&>geometric_typeboolean

Does first object not extend below second? Available forbox,polygon,circle.

box '(3,3),(0,0)' |&> box '(2,2),(0,0)'t

box<^boxboolean

Is first object below second (allows edges to touch)?

box '((1,1),(0,0))' <^ box '((2,2),(1,1))'t

box>^boxboolean

Is first object above second (allows edges to touch)?

box '((2,2),(1,1))' >^ box '((1,1),(0,0))'t

geometric_type?#geometric_typeboolean

Do these objects intersect? Available for these pairs of types: (box,box), (lseg,box), (lseg,lseg), (lseg,line), (line,box), (line,line), (path,path).

lseg '[(-1,0),(1,0)]' ?# box '(2,2),(-2,-2)'t

?-lineboolean

?-lsegboolean

Is line horizontal?

?- lseg '[(-1,0),(1,0)]'t

point?-pointboolean

Are points horizontally aligned (that is, have same y coordinate)?

point '(1,0)' ?- point '(0,0)'t

?|lineboolean

?|lsegboolean

Is line vertical?

?| lseg '[(-1,0),(1,0)]'f

point?|pointboolean

Are points vertically aligned (that is, have same x coordinate)?

point '(0,1)' ?| point '(0,0)'t

line?-|lineboolean

lseg?-|lsegboolean

Are lines perpendicular?

lseg '[(0,0),(0,1)]' ?-| lseg '[(0,0),(1,0)]'t

line?||lineboolean

lseg?||lsegboolean

Are lines parallel?

lseg '[(-1,0),(1,0)]' ?|| lseg '[(-1,2),(1,2)]'t

geometric_type~=geometric_typeboolean

Are these objects the same? Available forpoint,box,polygon,circle.

polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))'t

[a]Rotating a box with these operators only moves its corner points: the box is still considered to have sides parallel to the axes. Hence the box's size is not preserved, as a true rotation would do.


Caution

Note that thesame as operator,~=, represents the usual notion of equality for thepoint,box,polygon, andcircle types. Some of the geometric types also have an= operator, but= compares for equalareas only. The other scalar comparison operators (<= and so on), where available for these types, likewise compare areas.

Note

BeforePostgres Pro 14, the point is strictly below/above comparison operatorspoint<<|point andpoint|>>point were respectively called<^ and>^. These names are still available, but are deprecated and will eventually be removed.

Table 9.37. Geometric Functions

Function

Description

Example(s)

area (geometric_type ) →double precision

Computes area. Available forbox,path,circle. Apath input must be closed, else NULL is returned. Also, if thepath is self-intersecting, the result may be meaningless.

area(box '(2,2),(0,0)')4

center (geometric_type ) →point

Computes center point. Available forbox,circle.

center(box '(1,2),(0,0)')(0.5,1)

diagonal (box ) →lseg

Extracts box's diagonal as a line segment (same aslseg(box)).

diagonal(box '(1,2),(0,0)')[(1,2),(0,0)]

diameter (circle ) →double precision

Computes diameter of circle.

diameter(circle '<(0,0),2>')4

height (box ) →double precision

Computes vertical size of box.

height(box '(1,2),(0,0)')2

isclosed (path ) →boolean

Is path closed?

isclosed(path '((0,0),(1,1),(2,0))')t

isopen (path ) →boolean

Is path open?

isopen(path '[(0,0),(1,1),(2,0)]')t

length (geometric_type ) →double precision

Computes the total length. Available forlseg,path.

length(path '((-1,0),(1,0))')4

npoints (geometric_type ) →integer

Returns the number of points. Available forpath,polygon.

npoints(path '[(0,0),(1,1),(2,0)]')3

pclose (path ) →path

Converts path to closed form.

pclose(path '[(0,0),(1,1),(2,0)]')((0,0),(1,1),(2,0))

popen (path ) →path

Converts path to open form.

popen(path '((0,0),(1,1),(2,0))')[(0,0),(1,1),(2,0)]

radius (circle ) →double precision

Computes radius of circle.

radius(circle '<(0,0),2>')2

slope (point,point ) →double precision

Computes slope of a line drawn through the two points.

slope(point '(0,0)', point '(2,1)')0.5

width (box ) →double precision

Computes horizontal size of box.

width(box '(1,2),(0,0)')1


Table 9.38. Geometric Type Conversion Functions

Function

Description

Example(s)

box (circle ) →box

Computes box inscribed within the circle.

box(circle '<(0,0),2>')(1.414213562373095,1.414213562373095),​(-1.414213562373095,-1.414213562373095)

box (point ) →box

Converts point to empty box.

box(point '(1,0)')(1,0),(1,0)

box (point,point ) →box

Converts any two corner points to box.

box(point '(0,1)', point '(1,0)')(1,1),(0,0)

box (polygon ) →box

Computes bounding box of polygon.

box(polygon '((0,0),(1,1),(2,0))')(2,1),(0,0)

bound_box (box,box ) →box

Computes bounding box of two boxes.

bound_box(box '(1,1),(0,0)', box '(4,4),(3,3)')(4,4),(0,0)

circle (box ) →circle

Computes smallest circle enclosing box.

circle(box '(1,1),(0,0)')<(0.5,0.5),0.7071067811865476>

circle (point,double precision ) →circle

Constructs circle from center and radius.

circle(point '(0,0)', 2.0)<(0,0),2>

circle (polygon ) →circle

Converts polygon to circle. The circle's center is the mean of the positions of the polygon's points, and the radius is the average distance of the polygon's points from that center.

circle(polygon '((0,0),(1,3),(2,0))')<(1,1),1.6094757082487299>

line (point,point ) →line

Converts two points to the line through them.

line(point '(-1,0)', point '(1,0)'){0,-1,0}

lseg (box ) →lseg

Extracts box's diagonal as a line segment.

lseg(box '(1,0),(-1,0)')[(1,0),(-1,0)]

lseg (point,point ) →lseg

Constructs line segment from two endpoints.

lseg(point '(-1,0)', point '(1,0)')[(-1,0),(1,0)]

path (polygon ) →path

Converts polygon to a closed path with the same list of points.

path(polygon '((0,0),(1,1),(2,0))')((0,0),(1,1),(2,0))

point (double precision,double precision ) →point

Constructs point from its coordinates.

point(23.4, -44.5)(23.4,-44.5)

point (box ) →point

Computes center of box.

point(box '(1,0),(-1,0)')(0,0)

point (circle ) →point

Computes center of circle.

point(circle '<(0,0),2>')(0,0)

point (lseg ) →point

Computes center of line segment.

point(lseg '[(-1,0),(1,0)]')(0,0)

point (polygon ) →point

Computes center of polygon (the mean of the positions of the polygon's points).

point(polygon '((0,0),(1,1),(2,0))')(1,0.3333333333333333)

polygon (box ) →polygon

Converts box to a 4-point polygon.

polygon(box '(1,1),(0,0)')((0,0),(0,1),(1,1),(1,0))

polygon (circle ) →polygon

Converts circle to a 12-point polygon.

polygon(circle '<(0,0),2>')((-2,0),​(-1.7320508075688774,0.9999999999999999),​(-1.0000000000000002,1.7320508075688772),​(-1.2246063538223773e-16,2),​(0.9999999999999996,1.7320508075688774),​(1.732050807568877,1.0000000000000007),​(2,2.4492127076447545e-16),​(1.7320508075688776,-0.9999999999999994),​(1.0000000000000009,-1.7320508075688767),​(3.673819061467132e-16,-2),​(-0.9999999999999987,-1.732050807568878),​(-1.7320508075688767,-1.0000000000000009))

polygon (integer,circle ) →polygon

Converts circle to ann-point polygon.

polygon(4, circle '<(3,0),1>')((2,0),​(3,1),​(4,1.2246063538223773e-16),​(3,-1))

polygon (path ) →polygon

Converts closed path to a polygon with the same list of points.

polygon(path '((0,0),(1,1),(2,0))')((0,0),(1,1),(2,0))


It is possible to access the two component numbers of apoint as though the point were an array with indexes 0 and 1. For example, ift.p is apoint column thenSELECT p[0] FROM t retrieves the X coordinate andUPDATE t SET p[1] = ... changes the Y coordinate. In the same way, a value of typebox orlseg can be treated as an array of twopoint values.


Prev Up Next
9.10. Enum Support Functions Home 9.12. Network Address Functions and Operators
pdfepub
Go to Postgres Pro Standard 17
By continuing to browse this website, you agree to the use of cookies. Go toPrivacy Policy.

[8]ページ先頭

©2009-2025 Movatter.jp