On this blog I frequently write about features of relational database systems and their SQL dialects. One feature many developers do not know about is support for geometric shapes, although a lot of RDBMs support them in one form or the other, each with its own syntax, of course. In this article I’m going to demonstrate this feature with PostgreSQL.
PostgreSQL has data types for geometric shapes like point, box, line segment, line, path, polygon, and circle. These data types are only defined for two dimensions with Euclidean (x, y) coordinates. Here are some literals for these types:
point '(3.2,4)' box '((1,2),(6,4))' lseg '((-4,0),(3,2))' path '((0,0),(2,1),(5,3))' polygon '((0,0),(1,1),(2,0),(3,1))' circle '((5,2),1.5)'
You can create tables with columns of these types and insert shapes:
CREATE TABLE shapes (p point, c circle); INSERT INTO shapes (p, c) VALUES (point '(1,0)', circle '(0,0),3'), (point '(10,20)', circle '(2,3),4'), (point '(0.5,1.5)', circle '(1,2),1');
Now you can query shapes and filter them with special operators:
SELECT * FROM shapes WHERE c @> p;
This query uses the contains operator
@> in the
WHERE clause. It selects all rows where the circle
c contains the point
Here’s another operator:
<-> determines the Euclidean distance between two points.
SELECT point '(0,0)' <-> point '(1,1)'; => 2.23606797749979
?|| operator tests if two lines are parallel:
SELECT line '((1,2),(1,3))' ?|| line '((2,3),(2,4))'; => true
You can translate a shape with the
SELECT box '((0,0),(1,1))' + point '(1,2)'; => box '(2,3),(1,2)'
Or you can test with
&& if two shapes overlap:
SELECT box '((1,2),(4,3))' && box '(2,3),(1,2)'; => true
This is only a small selection of geometric operators. See the full list in the official documentation. There you can also find a list of geometric functions like
SELECT area(box '((4,6),(10,12))'); => 36
As mentioned in the beginning, other database systems support similar functionality. Check out MySQL’s spatial data types, Oracle Spatial, and MS SQL’s spatial data types.