Home > Articles > Data

Handling Geometric Data with PostgreSQL

  • Print
  • + Share This
PostgreSQL is a sophisticated object relational database system that can also be used to handle geometric databases. In many applications, it is necessary to store points, lines, rectangles, or other geometric objects efficiently. For these applications, PostgreSQL is the open source database of choice. This article is derived from the authors' forthcoming book, PostgreSQL Developer's Handbook (Sams, 2001, ISBN: 0672322609).
This chapter is from the book

This chapter is from the book


PostgreSQL supports a pool of datatypes, which are optimized for storing geographic information in a table. In order to speed up complex operations, PostgreSQL supports R-trees for special searching. Before we get to the operators, we will take a closer look at PostgreSQL's geographic datatypes.

The easiest datatype is point, which is used to store information about points. It consists of two components—one value for the x-coordinate and one for the y-coordinate. The following example shows how to create a table and how to insert a record into it:

tuning=# CREATE TABLE temppoint(fieldname point);
tuning=# INSERT INTO temppoint(fieldname) VALUES ('1,2');
INSERT 387694 1

To retrieve the value, we can use a simple SELECT statement:

tuning=# SELECT * FROM temppoint;
(1 row)

line is a datatype consisting of two points. One point matches the beginning of the line, while the second point matches the end of the line. Therefore, the syntax for inserting lines into a table is very similar to that for inserting points:

tuning=# CREATE TABLE templine(fieldname line);

line is already implemented, but it is not ready yet. Some operations are not defined yet, so it is necessary to find alternative solutions. To get around the problem, path has to be used:

tuning=# CREATE TABLE temppath(fieldname path);
tuning=# INSERT INTO temppath(fieldname) VALUES ('(1,3), (4,12)');
INSERT 387730 1

A path consisting of two points is equal to a line. In other words, a line is a special case of a path.

Here is an example of a path consisting of three points:

INSERT INTO temppath(fieldname) VALUES ('(3,1), (2,8), (10,4)');

Polygons are very similar to paths, but they are treated differently by PostgreSQL. Here is an example of how polygons can be used.

tuning=# CREATE TABLE temppoly(fieldname polygon);
tuning=# INSERT INTO temppoly(fieldname) VALUES ('(1,3), (4,12), (2,4)');
INSERT 387756 1

For storing rectangles, PostgreSQL supports a datatype called box, which consists of two points. On point defines the left end of the box, while the second point defines the upper-right end of the rectangle:

tuning=# CREATE TABLE tempbox(fieldname box);
tuning=# INSERT INTO tempbox(fieldname) VALUES ('(8,9), (1,3)');
INSERT 387767 1

This example shows a simple box. If the first point passed to PostgreSQL is the upper-right end instead of the lower-left end of the rectangle, PostgreSQL won't display an error. This is important because the programmer doesn't have to take care of these things.

A circle consists of a point and a radius. Therefore, three values have to be passed to the database to define a circle completely.

tuning=# CREATE TABLE tempcircle(fieldname circle);
tuning=# INSERT INTO tempcircle(fieldname) VALUES ('10, 4, 10');
INSERT 387789 1
  • + Share This
  • 🔖 Save To Your Account