Polygon Data Type
A Polygon is a sequential set of points that form an area, optionally with an SRID.
Examples:
Without specifying SRID
CREATE TABLE lots (name VARCHAR(30), lot POLYGON);
With specifying SRID
CREATE TABLE lots (name VARCHAR(30), lot POLYGON SRID 26911);
Use polygons to represent the property owned:
CREATE TABLE properties (id INTEGER PRIMARY KEY, area POLYGON NOT NULL, owner_id INTEGER NOT NULL);
INSERT INTO properties (id, area, owner_id) VALUES (1, POLYFROMTEXT('POLYGON((0 10, 1 10, 1 1, 10 1, 10 0, 0 0, 0 10))'), 1);
INSERT INTO properties (id, area, owner_id) VALUES (2, POLYFROMTEXT('POLYGON((8 8, 10 8, 10 10, 8 10, 8 8))'), 1);
INSERT INTO properties (id, area, owner_id) VALUES (3, POLYFROMTEXT('POLYGON((1 10, 8 10, 8 8, 10 8, 10 4, 1 4, 1 10), (2 9, 7 9, 7 5, 2 5, 2 9))'), 2);
INSERT INTO properties (id, area, owner_id) VALUES (4, POLYFROMTEXT('POLYGON((1 1, 10 1, 10 4, 1 4, 1 1))'), 3);
Calculate how much fencing each owner must buy to cover their property (where neighboring properties are double-fenced):
SELECT SUM(PERIMETER(area)), owner_id FROM properties GROUP BY owner_id;
--------------------------
col1 owner_id
--------------------------
48.000 1
48.000 2
24.000 3
--------------------------
Last modified date: 08/28/2024