How the NumPoints() function works in Mariadb?

The NumPoints() function is a spatial function in Mariadb that returns the number of points in a linestring, or 0 if the argument is not a linestring. This function can be used to count the number of vertices or coordinates in a linestring.

Syntax

The syntax of the NumPoints() function is as follows:

NumPoints(l)

The function takes one argument, l, which is a linestring value. The function returns an integer value that represents the number of points in l, or 0 if l is not a linestring.

Examples

Let’s look at some examples of how to use the NumPoints() function in Mariadb.

Example 1: Counting the number of points in a linestring

One common use case of the NumPoints() function is to count the number of points in a linestring. For example, suppose we have a table called roads that stores the spatial data of some roads, as shown below:

If we want to count the number of points in each road, we can use the following query:

SELECT name, NumPoints(path) AS num_points
FROM roads;

This query will return the following result:

| name  | num_points |
| ----- | ---------- |
| Road1 | 4          |
| Road2 | 4          |
| Road3 | 4          |
| Road4 | 0          |

As you can see, the NumPoints() function returns 4 for the roads that are linestrings, and 0 for the road that is not a linestring.

Example 2: Filtering the linestrings by the number of points

Another use case of the NumPoints() function is to filter the linestrings by the number of points. For example, suppose we have a table called rivers that stores the spatial data of some rivers, as shown below:

If we want to find the rivers that have more than three points in their shape, we can use the following query:

SELECT name, shape
FROM rivers
WHERE NumPoints(shape) > 3;

This query will return the following result:

| name   | shape                            |
| ------ | -------------------------------- |
| River1 | LINESTRING(0 0, 1 1, 2 2, 3 3)   |
| River2 | LINESTRING(0 0, 0 1, 1 1, 1 0)   |
| River3 | LINESTRING(0 0, 1 0, 1 1, 0 1)   |

As you can see, the NumPoints() function returns 4 for the rivers that have more than three points in their shape, and filters out the river that has only three points.

Example 3: Using the NumPoints() function with other spatial functions

The NumPoints() function can also be used with other spatial functions to perform more complex operations on spatial data. For example, suppose we have a table called trails that stores the spatial data of some trails, as shown below:

If we want to calculate the average distance between the points for each trail, we can use the following query:

SELECT name,
       CASE WHEN NumPoints(route) <= 1 THEN 0
            ELSE ST_Length(route) / (NumPoints(route) - 1)
       END AS avg_distance
FROM trails;

This query will return the following result:

| name   | avg_distance |
| ------ | ------------ |
| Trail1 | 1.414213562  |
| Trail2 | 1            |
| Trail3 | 1            |
| Trail4 | 0.707106781  |

As you can see, the NumPoints() function is used to check the number of points in each trail, and then divide the length of the trail by the number of segments, which is one less than the number of points. The ST_Length() function is used to calculate the length of the trail. Finally, the CASE function is used to handle the cases where the trail has only one or zero points, and return zero as the average distance.

Related Functions

There are some other functions in Mariadb that are related to the NumPoints() function, such as:

• The PointN() function, which returns the N-th point in a linestring, or NULL if the argument is not a linestring or the N-th point does not exist. For example, PointN(LINESTRING(0 0, 1 1, 2 2, 3 3), 2) returns POINT(1 1).
• The ST_Length() function, which returns the length of a linestring, or 0 if the argument is not a linestring. For example, ST_Length(LINESTRING(0 0, 1 1, 2 2, 3 3)) returns 4.242640687.
• The ST_Distance() function, which returns the minimum distance between two geometries, or NULL if any of the arguments is NULL. For example, ST_Distance(POINT(0 0), LINESTRING(1 1, 2 2, 3 3)) returns 1.414213562.

Conclusion

In this article, we have learned how the NumPoints() function works in Mariadb, and how to use it to count the number of points in a linestring, or 0 if the argument is not a linestring. We have also seen some examples and related functions that can help us work with spatial data in Mariadb.