How the SRID() function works in Mariadb?

The SRID() function in MariaDB is used to retrieve the spatial reference system identifier (SRID) for a spatial column.

Posted on

The SRID() function in MariaDB is used to retrieve the spatial reference system identifier (SRID) for a spatial column. SRID is a unique identifier associated with a spatial reference system, which defines how spatial data is stored, accessed, and manipulated within a database.

Syntax

The syntax for the MariaDB SRID() function is as follows:

SRID(spatial_column)

The SRID() function takes a single argument, spatial_column, which is the spatial column from which you want to retrieve the SRID. The function returns an integer representing the SRID of the spatial data.

Examples

Example 1: Retrieving SRID of a Geometry

This example demonstrates how to retrieve the SRID of a geometry value.

SELECT SRID(GeomFromText('POINT(1 1)'));

The output for this statement is:

+----------------------------------+
| SRID(GeomFromText('POINT(1 1)')) |
+----------------------------------+
|                                0 |
+----------------------------------+

This indicates that the default SRID for the geometry is 0.

Example 2: Setting and Retrieving SRID

In this example, we will set the SRID for a geometry and then retrieve it.

SELECT SRID(ST_GeomFromText('POINT(1 1)', 4326));

The output for this statement is:

+-------------------------------------------+
| SRID(ST_GeomFromText('POINT(1 1)', 4326)) |
+-------------------------------------------+
|                                      4326 |
+-------------------------------------------+

This shows that the SRID for the geometry has been set to 4326.

Example 3: Using SRID in a Table

This example shows how to use the SRID() function with spatial data in a table.

DROP TABLE IF EXISTS places;
CREATE TABLE places (name VARCHAR(100), location GEOMETRY);
INSERT INTO places VALUES ('Eiffel Tower', ST_GeomFromText('POINT(2.2945 48.8584)', 4326));

SELECT name, SRID(location) AS srid FROM places;

The output for this statement is:

+--------------+------+
| name         | srid |
+--------------+------+
| Eiffel Tower | 4326 |
+--------------+------+

This demonstrates retrieving the SRID for spatial data stored in a table.

Example 4: SRID and Spatial Calculations

Here, we use the SRID() function to ensure that spatial calculations are performed using the correct spatial reference system.

SELECT ST_Distance(
  ST_GeomFromText('POINT(2.2945 48.8584)', 4326),
  ST_GeomFromText('POINT(2.3499 48.8647)', 4326)
) AS distance;

The output for this statement is:

+---------------------+
| distance            |
+---------------------+
| 0.05575706233294499 |
+---------------------+

This calculates the distance between two points using the SRID 4326.

Below are a few functions related to the MariaDB SRID() function:

  • MariaDB ST_GeomFromText() function is used to create a geometry from its Well-Known Text (WKT) representation and optionally assign an SRID.

Conclusion

The SRID() function is an essential tool in MariaDB for working with spatial data. It allows users to manage and query spatial information effectively by ensuring that the data is interpreted using the correct spatial reference system. Understanding and utilizing SRID is crucial for accurate spatial analysis and operations.