Handling Geometric Data with PostgreSQL
This chapter is from the book
This chapter is from the book
This chapter is from the book
Datatypes
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);
CREATE
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; fieldname ----------- (1,2) (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); CREATE
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);
CREATE
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);
CREATE
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);
CREATE
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);
CREATE
tuning=# INSERT INTO tempcircle(fieldname) VALUES ('10, 4, 10');
INSERT 387789 1