Projected Spatial Reference Systems in MySQL 8.0

MySQL 8.0.11 comes with a catalog of 5108 spatial reference system (SRS) definitions. In a previous post, we covered the definitions of geographic SRSs. In this post we’ll go into the details of projected SRSs. (If you haven’t done so already, I suggest you read the previous post first.)

Projected SRSs are flat, Cartesian coordinate systems created by projecting points on (an oblate spheroidal model of) Earth onto a flat surface. They are flat maps. Unlike geographic systems, which deal with latitude and longitude, projected SRSs have X and Y axes in meters, survey feet or some other measure of length. Since Earth is round(ish) and maps are flat, some distortion is expected.

To get a feel for how projection may distort data, try peeling an orange (one big piece of orange peel) and flatten the orange peel on the table. Apart from the color, does it resemble an orange? I’d be amazed if it did.

But if we zoom in, some part of the orange peel has a close resemblance to some part of the orange. In the same way, a map projection may be accurate in a limited area. That’s why we have so many of them — 4628 different projected SRSs in MySQL 8.0.11. Some cover the globe (very inaccurate in some places), but most are meant to be used in a much smaller area, in which they are pretty accurate.

But if MySQL 8.0 has geography support, why do we need flat maps? There are several reasons:

  • We want to display the result on a flat screen, in a map. Therefore, we also need a map definition.
  • Computing in a Cartesian SRS is much faster than on an ellipsoid. If we’re operating in a small area, the accuracy may be good enough.
  • Maps are easy for humans to use. Most of us understand coordinates on a flat, Cartesian plane much better than we understand the surface of an ellipsoid, which requires 3d thinking.

Projected SRS Definitions

One projected SRS we see almost daily is SRID 3857, named “WGS 84 / Pseudo-Mercator”. What is that? It’s the map we see on our screens if we look at Google Maps, Bing Maps, OpenStreetMap, or almost any web world map out there. Let’s look at the definition:

Like all WKT strings, the definition looks better in pretty-print:

Projected SRS definitions start with the string “PROJCS” (line 1) and a name (line 2), but MySQL doesn’t care about the names, only about the numbers. Then follows a geographic SRS definition (lines 3-34) for the SRS that this SRS is a projection of. To rephrase: The geographic SRS we define here is the SRS we’re projecting from when we create our projected SRS. The geographic SRS definition was covered in a previous post, so we won’t go into details here. Just note that the authority clause (line 33) of the geographic SRS is EPSG 4326, the World Geodetic System 1984. This means that our projected SRS (SRID 3857) is a projection of WGS 84 data onto a flat surface.

After the geographic SRS comes the declaration of which projection method is used to produce the map (lines 35-38). The name of the projection (line 36) is for humans. MySQL cares only about the authority code (line 37). This code is what makes MySQL recognize this as a popular visualization pseudo Mercator projection (see the table of supported projection methods below). The matching of the authority name (“EPSG”) is case-insensitive. If there is no authority clause, or the authority name or code is unknown, MySQL regards this as an unknown projection. Even if the details about the SRS aren’t known, MySQL knows that it’s a Cartesian system and some other parameters. MySQL can still do computations within the SRS, but data can’t be converted to or from the SRS.

Having an AUTHORITY subclause to the PROJECTION clause is a MySQL extension of the WKT format. The standard doesn’t allow it. But the standard also fails to specify canonical names for all the projections, so string matching is not a viable option either. The PROJECTION clause is also the only clause (where it matters) that doesn’t have an AUTHORITY clause in the standard, so it looks like an oversight by the standards committee. PostGIS has solved this by recording the actual projection type outside the definition (in their proj4 parameter string). We decided that adding an AUTHORITY clause was a better solution for us. It’s not according to the standard, but it is in the spirit of the standard.

After the projection method comes the projection parameters (lines 39-58). All parameters have a mandatory name, a mandatory value, and an optional authority clause. The authority clause refers to the parameter name/type, not the value. E.g., the latitude of natural origin (lines 39-43) would be EPSG 8801 also if the value was something else.

MySQL will verify that an SRS definition has all the required parameters for that specific projection method, and these, too, are recognized based on the authority code. However, if there is no authority code, or the authority is not “EPSG” (case insensitive matching), MySQL will try to match the parameter name with a list of known parameter names (case insensitive matching). There are different parameters for different projection methods, and it would take too long to go through all of them here. See below for summary tables of supported projection methods and mandatory parameters.

The map units are length units, not latitude and longitude. The unit specification (lines 59-63) specify exactly which unit, and the conversion factor to meters (line 61). In this case it’s 1, since 1 meter is 1 meter. But there are other SRSs that use other units, e.g., US survey foot, with a conversion factor of 0.30480060960121924.

The first axis (lines 64-67) has a name (line 65) and a direction (line 66). In this case the name is “X”, but even if it were “Y”, this would still be what MySQL understands as the X axis. Remember: Names have no meaning. Numbers do. MySQL considers the X axis to be the first axis, which is the axis that appears first in the SRS definition. Since the axis direction is east, the positive numbers on the axis are east of the origin.

The second axis (lines 68-71) has the name “Y” (line 69), and it is also what MySQL considers the Y axis. The direction (line 70) indicates that positive numbers are north of the origin.

Finally, there is the authority code of the SRS (line 72). MySQL uses the same SRIDs as the EPSG Dataset, so EPSG SRS number 3857 is MySQL SRID 3857.

Supported Projections

MySQL supports a number of projection methods. SRSs using unknown projection methods are allowed, but MySQL will not be able to check for mandatory parameters (it doesn’t know what they are), and will not be able to convert data to or from an unknown projection.

EPSG Guidance Note 7-2 has detailed explanations of how each projection works, including formulas.

EPSG codeNameMandatory parameters (EPSG codes)
1024Popular Visualisation Pseudo Mercator8801, 8802, 8806, 8807
1027Lambert Azimuthal Equal Area (Spherical)8801, 8802, 8806, 8807
1028Equidistant Cylindrical8823, 8802, 8806, 8807
1029Equidistant Cylindrical (Spherical)8823, 8802, 8806, 8807
1041Krovak (North Orientated)8811, 8833, 1036, 8818, 8819, 8806, 8807
1042Krovak Modified8811, 8833, 1036, 8818, 8819, 8806, 8807, 8617, 8618, 1026, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035
1043Krovak Modified (North Orientated)8811, 8833, 1036, 8818, 8819, 8806, 8807, 8617, 8618, 1026, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035
1051Lambert Conic Conformal (2SP Michigan)8821, 8822, 8823, 8824, 8826, 8827, 1038
1052Colombia Urban8801, 8802, 8806, 8807, 1039
9801Lambert Conic Conformal (1SP)8801, 8802, 8805, 8806, 8807
9802Lambert Conic Conformal (2SP)8821, 8822, 8823, 8824, 8826, 8827
9803Lambert Conic Conformal (2SP Belgium)8821, 8822, 8823, 8824, 8826, 8827
9804Mercator (variant A)8801, 8802, 8805, 8806, 8807
9805Mercator (variant B)8823, 8802, 8806, 8807
9806Cassini-Soldner8801, 8802, 8806, 8807
9807Transverse Mercator8801, 8802, 8805, 8806, 8807
9808Transverse Mercator (South Orientated)8801, 8802, 8805, 8806, 8807
9809Oblique Stereographic8801, 8802, 8805, 8806, 8807
9810Polar Stereographic (variant A)8801, 8802, 8805, 8806, 8807
9811New Zealand Map Grid8801, 8802, 8806, 8807
9812Hotine Oblique Mercator (variant A)8811, 8812, 8813, 8814, 8815, 8806, 8807
9813Laborde Oblique Mercator8811, 8812, 8813, 8815, 8806, 8807
9815Hotine Oblique Mercator (variant B)8811, 8812, 8813, 8814, 8815, 8816, 8817
9816Tunisia Mining Grid8821, 8822, 8826, 8827
9817Lambert Conic Near-Conformal8801, 8802, 8805, 8806, 8807
9818American Polyconic8801, 8802, 8806, 8807
9819Krovak8811, 8833, 1036, 8818, 8819, 8806, 8807
9820Lambert Azimuthal Equal Area8801, 8802, 8806, 8807
9822Albers Equal Area8821, 8822, 8823, 8824, 8826, 8827
9824Transverse Mercator Zoned Grid System8801, 8830, 8831, 8805, 8806, 8807
9826Lambert Conic Conformal (West Orientated)8801, 8802, 8805, 8806, 8807
9828Bonne (South Orientated)8801, 8802, 8806, 8807
9829Polar Stereographic (variant B)8832, 8833, 8806, 8807
9830Polar Stereographic (variant C)8832, 8833, 8826, 8827
9831Guam Projection8801, 8802, 8806, 8807
9832Modified Azimuthal Equidistant8801, 8802, 8806, 8807
9833Hyperbolic Cassini-Soldner8801, 8802, 8806, 8807
9834Lambert Cylindrical Equal Area (Spherical)8823, 8802, 8806, 8807
9835Lambert Cylindrical Equal Area8823, 8802, 8806, 8807

Projection Parameters

MySQL recognizes projection parameters primarily by authority code. However, if there is no authority code, MySQL reverts to case-insensitive string matching on the parameter name.

For details about each parameter, look it up by code in the EPSG Online Registry.

EPSG CodeFallback Name (recognized by MySQL)EPSG Name
1026c1C1
1027c2C2
1028c3C3
1029c4C4
1030c5C5
1031c6C6
1032c7C7
1033c8C8
1034c9C9
1035c10C10
1036azimuthCo-latitude of cone axis
1038ellipsoid_scale_factorEllipsoid scaling factor
1039projection_plane_height_at_originProjection plane origin height
8617evaluation_point_ordinate_1Ordinate 1 of evaluation point
8618evaluation_point_ordinate_2Ordinate 2 of evaluation point
8801latitude_of_originLatitude of natural origin
8802central_meridianLongitude of natural origin
8805scale_factorScale factor at natural origin
8806false_eastingFalse easting
8807false_northingFalse northing
8811latitude_of_centerLatitude of projection centre
8812longitude_of_centerLongitude of projection centre
8813azimuthAzimuth of initial line
8814rectified_grid_angleAngle from Rectified to Skew Grid
8815scale_factorScale factor on initial line
8816false_eastingEasting at projection centre
8817false_northingNorthing at projection centre
8818pseudo_standard_parallel_1Latitude of pseudo standard parallel
8819scale_factorScale factor on pseudo standard parallel
8821latitude_of_originLatitude of false origin
8822central_meridianLongitude of false origin
8823standard_parallel_1, standard_parallel1Latitude of 1st standard parallel
8824standard_parallel_2, standard_parallel2Latitude of 2nd standard parallel
8826false_eastingEasting at false origin
8827false_northingNorthing at false origin
8830initial_longitudeInitial longitude
8831zone_widthZone width
8832standard_parallelLatitude of standard parallel
8833longitude_of_centerLongitude of origin

Thank you for using MySQL !

 

About Norvald H. Ryeng

Norvald has been working as a software engineer on the MySQL Optimizer Team since 2011, where he mostly works on GIS. He is also the point of contact for package maintainers in Linux distributions. He holds a PhD in Computer and Information Science from the Norwegian University of Science and Technology.

One thought on “Projected Spatial Reference Systems in MySQL 8.0

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.