Geometric Data Types

PostgreSQL includes a number of built-in data types for holding geometric-based data. There are also many built-in functions and operators that aid in the manipulation and calculation of geometric data.

BOX

Description

Holds the outer-corner coordinates that define a rectangle.

Inputs

((x1,y1),(x2,y2)) 

x1—X-axis begin point.

y1—Y-axis begin point.

x2—X-axis end point.

y2—Y-axis end point.

Storage Size

32 bytes

Example Data

((1,1),(50,50)) 

Notes

On input, the data is reordered to store the lower-right corner first and the upper-left corner second.Therefore, the preceding example, when stored, would be represented as (50,50),(1,1).

CIRCLE

Description

Holds the coordinates that represent a center point and radius of a circle.

Inputs

<(x,y),r> 

x—X-axis of center point.

y—Y-axis of center point.

r—Radius

Storage Size

24 bytes

Example Data

<(10,10),5> 

LINE

Description

Holds the coordinates for an infinite line.

Inputs

((x1,y1),(x2,y2)) 

x1, y1—X- andY-axis of starting point.

x2, y2—X- andY-axis of ending point.

Storage Size

32 bytes

Example Data

((1,1),(100,100)) 

Notes

The LINE data type was implemented starting in PostgreSQL 7.1.

LSEG

Description

Holds the coordinates that represent a finite line segment.

Inputs

((x1,y1),(x2,y2)) 

x1, y1—X- andY-axis of starting point.

x2, y2—X- andY-axis of ending point.

Storage Size

32 bytes

Example Data

((1,1),(100,100)) 

Notes

The LSEG data type is similar to LINE, except that the latter represents an infinite line as opposed to a specified segment. Essentially, once a line is defined with the LINE data type, it is assumed that it will continue along the same plane in perpetuity.

PATH

Description

Paths represent variable line segments, in which there can be numerous points that create either an open or closed path.

Inputs

((x1,y1),…,(xn,yn)) 

x1, y1—Represents the starting point of a closed path.

xn, yn—Represents the end point of a closed path.

[(x1,y1),…,(xn,yn)] 

x1, y1—Represents the starting point of an open path.

xn, yn—Represents the end point of an open path.

Storage Size

4 + 32n bytes

Example Data

[(1,1),(3,3),(5,10) ]—An open path.

((1,1),(3,3),(5,10),(1,1) )—A closed path.

Notes

Closed paths begin with an open parenthesis and open paths begin with an open bracket.

The functions isopen, isclosed, popen, and pclose can be used to test and manipulate paths.

POINT

Description

Holds the coordinates that represent a single point in space.

Inputs

(x,y) 

x—X-axis of point.

y—Y-axis of point.

Storage Size

16 bytes

Example Data

(1,5) 

POLYGON

Description

Holds a set of coordinates that represents a polygon.

Inputs

((x1,y1),…,(xn,yn)) 

x1, y1—The starting point of the polygon.

xn, yn—The ending point of the polygon (will usually be the same as the starting point).

Storage Size

4+32n bytes

Example Data

((1,1),(5,1),(5,5),(1,5),(1,1) )—A square polygon.

Notes

A polygon is very similar to a closed path. However, there are some additional functions that only act on polygons (that is, poly_center, poly_contain, poly_left, poly_right, and so on).