MySQL allows you to convert spatial data from one coordinate system, known as a Spatial Reference System (SRS), to another. This function is particularly useful when dealing with data from different sources or when you need to visualise data in a specific projection. In this blog post we will explain in detail how you can use MySQL to accurately display cities on a map of hiking trails when the cities and hiking trails data come from different sources i.e. GPS coordinates and flat map coordinates respectively.
SRS support was introduced in MySQL 8.0 and it is now available in MySQL Community and Enterprise Editions and HeatWave MySQL. An SRS can be either geographic (coordinates define points on a sphere-like model of the Earth) or projected (coordinates define points on a flat surface or map). MySQL supports a catalog of more than 5000 SRS definitions with the user also able to define their own. MySQL 9.2.0 has introduced a new dynamic privilege: CREATE_SPATIAL_REFERENCE_SYSTEM that can be used to create, replace, and drop a custom SRS.
Why are transformations important?
Imagine you have a dataset of points representing cities across the globe, and each point is defined by GPS coordinates, in particular using the WGS 84 geographic coordinate system (represented by SRID 4326). You also have a flat map displaying hiking trails in a local area, and this map uses a projected SRS like the British National Grid (represented by SRID 27700). If you want to accurately display the cities on your map of hiking trails, you’ll encounter a problem. This is because the WGS 84 coordinates are geographic i.e. define points on a sphere-like model of the Earth, while the British National Grid projects those points onto a flat surface, resulting in different coordinate values for the same location.
This is where MySQL’s spatial function ST_Transform becomes essential. It allows you to convert the cities’ coordinates from WGS 84 to the British National Grid, ensuring they are displayed correctly on your map.
How does ST_Transform work?
ST_Transform takes two main arguments:
- The Geometry: This is the spatial object (like a point, line, or polygon) that you want to convert.
- The Target SRID: This is the SRID of the coordinate system you want to convert to.
The function returns a new geometry of the same type as the input geometry but with all coordinates transformed to the target SRS.
Example
Let’s say you have a table named cities with a geometry column named location storing points in WGS 84 (SRID 4326).
CREATE TABLE cities ( name VARCHAR(45) PRIMARY KEY, location GEOMETRY NOT NULL SRID 4326 )ENGINE=InnoDB;
You can insert some cities in your table.
INSERT INTO cities (name, location)
VALUES ('London', ST_Geomfromtext('POINT(51.509865 -0.118092)', 4326)),
('Edinburgh', ST_Geomfromtext('POINT(55.953251 -3.188267)', 4326));
Then you can convert these points to British National Grid (SRID 27700). The following query demonstrates this:
SELECT name, ST_AsText(ST_Transform(location, 27700)) AS location_british_national_grid FROM cities;
The query selects the city name and uses ST_Transform to convert the location geometry to SRID 27700. The ST_AsText function is used here to represent the transformed geometry in a readable text format.
+--------------+-----------------------------------------------+ | name | location_british_national_grid | +--------------+-----------------------------------------------+ | Edinburgh | POINT(325899.1849114155 673995.7129374838) | | London | POINT(530695.3868317431 180671.48838623578) | +--------------+-----------------------------------------------+
Now say that you want to import the hiking trails whose coordinates are in a British National Grid SRS to build a European hiking map that will use for example the ETRS89-extended (SRID 3035). MySQL can seamlessly transform from one projected SRS to another again by using the ST_Transform function.
The following query transforms the coordinates of London from the British National Grid SRS to an ETRS89-extended SRS.
SELECT ST_AsText(ST_Transform(ST_Geomfromtext('POINT(530695.3868317431 180671.48838623578)', 27700), 3035));
+------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Transform(ST_Geomfromtext('POINT(530695.3868317431 180671.48838623578)', 27700), 3035)) |
+------------------------------------------------------------------------------------------------------+
| POINT(3621142.4074678584 3204082.1556793484) |
+------------------------------------------------------------------------------------------------------+
Practical Applications
Let’s consider some real-world scenarios where ST_Transform could be beneficial:
- Displaying Data on Web Maps: Web maps often use the Web Mercator projection (SRID 3857) as their default SRS. If your data is in a different SRS, such as WGS 84, you can use ST_Transform to convert the coordinates to Web Mercator, ensuring accurate visualisation.
- Working with a Local SRS: When dealing with data specific to a smaller region, like a state or country, it’s often more accurate to use a local SRS designed for that area. For instance, if you have data in WGS 84 representing locations in California, you might want to convert them to a UTM zone covering California (e.g., UTM zone 10N, SRID 32610) using ST_Transform for more precise spatial analysis and visualisation within that region.
- Combining Data from Different Sources: When you have data layers from multiple sources using different SRSs, you can use ST_Transform to bring them into a common SRS, allowing for accurate overlay and analysis.
A note on ST_Transform and ST_SRID
It is crucial to understand the distinction between ST_Transform and the ST_SRID functions. The latter only changes the SRID of the geometry without altering its coordinates as ST_Transform does. It doesn’t perform any transformation or projection on the input data.
Summary
Since MySQL 8.0, the ST_Transform function enables conversion of spatial data from one coordinate system (SRS) to another, essential for accurately displaying or analyzing data from different sources. This function is particularly useful when working with geographic coordinates (like WGS 84, used globally) and projected coordinates (such as the British National Grid, used locally in the UK), as it allows you to align diverse datasets for seamless visualization on a single map. For example, you can transform city locations in WGS 84 to match hiking trails in the British National Grid, ensuring correct placement on a map. This feature is invaluable for applications such as web mapping, regional data analysis, and combining data layers from multiple sources, providing flexibility and precision in spatial data handling. This functionality is available in MySQL Community and Enterprise Editions and HeatWave MySQL.