How the PointN() function works in Mariadb?

The PointN() function is a built-in function in Mariadb that returns the N-th point of a linestring geometry.

Posted on

The PointN() function is a built-in function in Mariadb that returns the N-th point of a linestring geometry. The function is useful for accessing and manipulating the individual points of a linestring, such as finding the start and end points, or iterating over the points. The function is also known as ST_PointN().

Syntax

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

PointN(linestring, n)

Where linestring is a linestring geometry that represents a sequence of connected points, and n is an integer value that specifies the position of the point to return. The first point has the position 1, the second point has the position 2, and so on. If linestring or n is NULL, or if n is out of range, the function returns NULL.

Examples

Example 1: Getting the first point of a linestring with the PointN() function

The following example shows how to use the PointN() function to get the first point of a linestring with the coordinates (10, 20), (30, 40), and (50, 60):

SELECT PointN(LineString(Point(10, 20), Point(30, 40), Point(50, 60)), 1) AS FirstPoint;

The function returns a point geometry that is encoded in a binary format. The function uses the Well-Known Binary (WKB) format to represent the point geometry. The point has the coordinates (10, 20).

Example 2: Getting the last point of a linestring with the PointN() function

The following example shows how to use the PointN() function to get the last point of a linestring with the coordinates (10, 20), (30, 40), and (50, 60):

SELECT PointN(LineString(Point(10, 20), Point(30, 40), Point(50, 60)), 3) AS LastPoint;

The function returns a point geometry that is encoded in a binary format. The function uses the Well-Known Binary (WKB) format to represent the point geometry. The point has the coordinates (50, 60).

Example 3: Iterating over the points of a linestring with the PointN() function

The following example shows how to use the PointN() function to iterate over the points of a linestring with the coordinates (10, 20), (30, 40), and (50, 60):

SELECT PointN(LineString(Point(10, 20), Point(30, 40), Point(50, 60)), n) AS Point
FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS t;

The function returns a table of point geometries that are encoded in a binary format. The function uses the Well-Known Binary (WKB) format to represent the point geometries. The points have the coordinates (10, 20), (30, 40), and (50, 60), respectively.

There are some other functions in Mariadb that are related to the PointN() function. They are:

  • NumPoints(): This function returns the number of points in a linestring geometry. The function is useful for finding the length or size of a linestring, or for validating the input of the PointN() function. The function is also known as ST_NumPoints().
  • StartPoint(): This function returns the first point of a linestring geometry. The function is equivalent to the PointN() function with the position 1, but it does not require the position argument. The function is also known as ST_StartPoint().
  • EndPoint(): This function returns the last point of a linestring geometry. The function is equivalent to the PointN() function with the position equal to the number of points, but it does not require the position argument. The function is also known as ST_EndPoint().

Conclusion

The PointN() function is a useful function in Mariadb that allows you to return the N-th point of a linestring geometry. The function is helpful for accessing and manipulating the individual points of a linestring, such as finding the start and end points, or iterating over the points. The function uses the Well-Known Binary (WKB) format to represent the point geometry. You can also use other functions like NumPoints(), StartPoint(), and EndPoint() to perform other operations on the linestring geometry. I hope this article helped you understand how the PointN() function works in Mariadb.