Programster's Blog

Tutorials focusing on Linux, programming, and open-source

MySQL GIS Cheatsheet

MySQL GIS

Point

Definition

CREATE TABLE `pointsTable` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `location` Point NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insertion

Below is an example of me inserting 3 points:

INSERT INTO pointsTable (location) VALUES 
(ST_GeomFromText('Point(0 0)')),
(ST_GeomFromText('Point(1 1)')),
(ST_GeomFromText('Point(2 2)'));

Selection

If you want to be able to select the points and be able to read them, use the AsText function:

SELECT AsText(location) 
FROM pointsTable;

+------------------+
| AsText(location) |
+------------------+
| POINT(0 0)       |
| POINT(1 1)       |
| POINT(2 2)       |
+------------------+

Linestring

A LineString consists of Point values. You can extract particular points of a LineString, count the number of points that it contains, or obtain its length.

Definition

CREATE TABLE `linesTable` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `my_line` linestring NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insertion

Below is an example of me inserting 3 lines consisting of the 4 points (0,0), (1,1), (2,2), and (3,3)

INSERT INTO linesTable (my_line) VALUES 
(ST_GeomFromText('LINESTRING(0 0,1 1)')),
(ST_GeomFromText('LINESTRING(0 0,1 1,2 2)')),
(ST_GeomFromText('LINESTRING(0 0,1 1,2 2, 3 3)'));

Selection

Selecting the data from the table will output unreadable output to a human. If you want to be able to read it you may wish to use the AsText function:

SELECT AsText(my_line) from linesTable;

Example output:

+-----------------------------+
| AsText(my_line)             |
+-----------------------------+
| LINESTRING(0 0,1 1)         |
| LINESTRING(0 0,1 1,2 2)     |
| LINESTRING(0 0,1 1,2 2,3 3) |
+-----------------------------+

Number of Points

You can get the number of points in a Linestring with NumPoints()

SELECT `id`, NumPoints(my_line) from linesTable;

Example output:

+----+--------------------+
| id | NumPoints(my_line) |
+----+--------------------+
|  1 |                  2 |
|  2 |                  3 |
|  3 |                  4 |
+----+--------------------+

Getting Start, End, and Nth Point

You can get the start and end points with StartPoint and EndPoint.

SELECT `id`, StartPoint(my_line), EndPoint(my_line)  from linesTable;

That will return Point objects, but if you wish to be able to read the points as a human...

SELECT `id`, AsText(StartPoint(my_line)), AsText(EndPoint(my_line))  from linesTable;

+----+-----------------------------+---------------------------+
| id | AsText(StartPoint(my_line)) | AsText(EndPoint(my_line)) |
+----+-----------------------------+---------------------------+
|  1 | POINT(0 0)                  | POINT(1 1)                |
|  2 | POINT(0 0)                  | POINT(2 2)                |
|  3 | POINT(0 0)                  | POINT(3 3)                |
+----+-----------------------------+---------------------------+

To select the nth point, you would use the PointN function. For example, to get the second point in the lines, you would use:

SELECT id, AsText(PointN((my_line), 2)) from linesTable;

+----+------------------------------+
| id | AsText(PointN((my_line), 2)) |
+----+------------------------------+
|  1 | POINT(1 1)                   |
|  2 | POINT(1 1)                   |
|  3 | POINT(1 1)                   |
+----+------------------------------+

Getting Length

You can use the GLength function to get the length of the line in its spatial coordinate system. Please bear in mind that if you are using points based on latitude and longitude this could be a bad way to judge the distance between the two points on earth, but may be close enough for whatever your use-case is.

SELECT 
id, GLength(my_line) 
FROM linesTable;

+----+--------------------+
| id | GLength(my_line)   |
+----+--------------------+
|  1 | 1.4142135623730951 |
|  2 | 2.8284271247461903 |
|  3 |  4.242640687119286 |
+----+--------------------+

Is Closed?

You can find out if a line forms a closed loop with the IsClosed function. For this I am going to add an extra lines. One of which is obviously closed and the other dubious.

INSERT INTO linesTable (my_line) VALUES 
(ST_GeomFromText('LINESTRING(0 0,1 1, 2 2, 0 0)')),
(ST_GeomFromText('LINESTRING(0 0,1 1, 0 0, 3 3)'));

Here is an example select query

SELECT 
id, IsClosed(my_line)
FROM linesTable;

...which now outputs:

+----+-------------------+
| id | IsClosed(my_line) |
+----+-------------------+
|  1 |                 0 |
|  2 |                 0 |
|  3 |                 0 |
|  4 |                 1 |
|  5 |                 0 |
+----+-------------------+

Geometry Type

Definition

CREATE TABLE `geometryTable` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `shape` Geometry NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insertion

You can insert almost anything in the Geometry type, including points, linestrings, and polygons. Here we will insert a point, and a buffered version of that point (a circle).

INSERT INTO `geometryTable` (`shape`) VALUES
(ST_GeomFromText('Point(0 0)')),
(ST_BUFFER(GeomFromText('POINT(0 0)'), 2));

ST_INTERSECTS

To find out if lines in your table intsersect another, you can use ST_INTERSECTS.

SELECT 
id, 
ASTEXT(my_line) AS `my_line`,
ST_Intersects(`my_line`, ST_GeomFromText('LINESTRING(0 2, 2 0)')) AS `intersects`
FROM `linesTable`;

+----+-----------------------------+------------+
| id | my_line                     | intersects |
+----+-----------------------------+------------+
|  1 | LINESTRING(0 0,1 1)         |          0 |
|  2 | LINESTRING(0 0,1 1,2 2)     |          1 |
|  3 | LINESTRING(0 0,1 1,2 2,3 3) |          1 |
|  4 | LINESTRING(0 0,1 1,2 2,0 0) |          1 |
|  5 | LINESTRING(0 0,1 1,0 0,3 3) |          1 |
+----+-----------------------------+------------+

Note that if just the end points are the same, it won' be considered intersecting as shown below:

SET @ls1 = ST_GeomFromText('LINESTRING(0 1, 1 2     )');
SET @ls2 = ST_GeomFromText('LINESTRING(     1 2, -5 5)');
SELECT ST_Intersects(@ls1, @ls2);

+---------------------------+
| ST_Intersects(@ls1, @ls2) |
+---------------------------+
|                         0 |
+---------------------------+

... but they would be considered touching with ST_Touches

Lines lying within other lines can have mixed results as demonstrated below:

// lines with gradient 1
SET @ls1 = ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0,      2 2)');
SELECT ST_Intersects(@ls1, @ls2);

+---------------------------+
| ST_Intersects(@ls1, @ls2) |
+---------------------------+
|                         0 |
+---------------------------+

// lines along x axis
SET @ls1 = ST_GeomFromText('LINESTRING(0 0, 1 0, 2 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0,      2 0)');
SELECT ST_Intersects(@ls1, @ls2);

+---------------------------+
| ST_Intersects(@ls1, @ls2) |
+---------------------------+
|                         1 |
+---------------------------+

ST_Touches

To find out if lines in your table touch another, you can use ST_TOUCHES which is like ST_INTERSECTS but will include cases where endpoints meet, or lines overlap.

SET @ls1 = ST_GeomFromText('LINESTRING(0 1, 1 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(1 0, 0 1)');
SELECT ST_TOUCHES(@ls1, @ls2);

+------------------------+
| ST_TOUCHES(@ls1, @ls2) |
+------------------------+
|                      1 |
+------------------------+

SET @ls1 = ST_GeomFromText('LINESTRING(0 1, 0 2     )');
SET @ls2 = ST_GeomFromText('LINESTRING(     0 2, 0 3)');
SELECT ST_TOUCHES(@ls1, @ls2);

+------------------------+
| ST_TOUCHES(@ls1, @ls2) |
+------------------------+
|                      1 |
+------------------------+

ST_BUFFER And ST_CONTAINS

You can use ST_BUFFER to create a "buffer" around a geometry object, such as a point or a line. Obviously a buffer around a point creates a circle. Below, we will find all the points in our pointsTable that is within a distance of 2 units from the point (0 0);

SELECT 
ASTEXT(location),
ST_CONTAINS(
    ST_BUFFER(GeomFromText('POINT(0 0)'), 2), 
    location
) as `contained`
FROM `pointsTable`;

+------------------+-----------+
| ASTEXT(location) | contained |
+------------------+-----------+
| POINT(0 0)       |         1 |
| POINT(1 1)       |         1 |
| POINT(2 2)       |         0 |
+------------------+-----------+

Using ST_CONTAINS To Find Points Within Polygon

The example below finds the number of points that I have within Qatar. It took me a while to realize I needed to use Polygon with double ( for it to work as pointed out here.

SELECT count(*) 
FROM `locations` 
WHERE
ST_CONTAINS(
    GeomFromText('POLYGON((26.12092 50.73486, 24.3521 50.60852, 24.36211 51.95435, 26.49516 52.00928, 26.12092 50.73486))'),
    gpsPoint
) = 1;

I used the "polygon-show-points" tool within my mapbox examples in order to get the coordinates for a polygon that wraps Qatar.

Alternatively, if you just want to list all of the points and whether they are within the polygon:

SELECT 
id,
ASTEXT(gpsPoint),
ST_CONTAINS(
    GeomFromText('POLYGON((26.12092 50.73486, 24.3521 50.60852, 24.36211 51.95435, 26.49516 52.00928, 26.12092 50.73486))'),
    gpsPoint
) as `is_contained`

References

Last updated: 25th January 2021
First published: 16th August 2018
Programster's Blog Ⓒ 2024