# WL#8034: More user friendly GIS functions

Status: Complete

We need some more GIS functions which are not defined by OGC, to make MySQL GIS more user friendly by helping out potential MySQL GIS users with the most frequent/common problem or issues they can have when using MySQL GIS. Currently in the list are: ST_Distance_Sphere(geom1, geom2, earth-radius) — Returns the minimum distance on the earth in meters between two lon/lat geometries. Uses a spherical earth and a configurable radius, which is 6370986 meters by default. For now we only support point/multipoint arguments. ST_MakeEnvelope(bottom-left-point, top-right-point) --- Makes a rectangle using bottom left and top right points. This is done on an abstract plane rather than on a sphere, spheroid or on earth. ST_IsValid: check whether a geometry is valid, the validity is defined by OGC standard documents. ST_Validate: check whether a geometry byte string is valid (validity defined as above), if so return it, otherwise return NULL. this can be used to filter out invalid geometry data at an noticeable cost. we don't do so by default in geometry calculations because of the cost, but some users may be willing to take the cost to get more precise results and completely eliminate invalid geometry data from the result.

F-1: All functions should return NULL if any of the geometry arguments is NULL. F-2: ST_IsValid should return NULL if the geometry argument is NULL, and should not return NULL otherwise. It should return false if the argument isn't a valid GEOMETRY string, or it's not geometrically valid. It should return true if the argument is a valid geometry byte string and it is geometrically valid. F-3: ST_Validate should return NULL if the argument is NULL, or if the geometry byte string isn't a valid GEOMETRY(SRID+WKB) byte string, or if the geometry is not geometrically valid as defined by OGC. If the geometry argument is valid, it should be returned as is. F-4: Given an empty geometry, and any empty geometry collection, ST_IsValid should return true and ST_Validate should simply return it directly without further checks, that is, they should be considered valid by the two functions; Given a non-empty geometry collection, it's valid if and only if all its components are valid; Given any non-empty geometries that are not geometry collections , the validity is defined by OGC standard, against which ST_IsValid and ST_Validate should check the geometry argument. F-5: ST_MakeEnvelope should return NULL if the point argument is NULL; it should return ER_GIS_INVALID_DATA if any of the point arguments isn't valid GEOMETRY byte string; It should report ER_WRONG_ARGUMENTS if the geometry argument isn't a point; And it should not return NULL if neither arguments is NULL and both arguments are valid point data. ST_MakeEnvelope SHOULD report ER_WRONG_ARGUMENTS if p1.x > p2.x or p1.y > p2.y ,it should report ER_GIS_DIFFERENT_SRIDS if the two points have different SRIDs. F-6: ST_MakeEnvelope should create the result rectangle on an abstract plane like this: given the bottom left point p1 and top right point p2, the result should be: polygon((p1.x p1.y, p2.x p1.y, p2.x p2.y, p1.x p2.y, p1.x p1.y)) The result polygon should be given the same srid as the point argument. F-7: ST_Distance_Sphere should return NULL if any of the two arguments is NULL or empty; it should return ER_GIS_INVALID_DATA if any of the two arguments isn't valid GEOMETRY byte string. It should return ER_GIS_UNSUPPORTED_ARGUMENT if any of the two arguments is neither a point nor a multipoint. F-8: The points in geometry arguments of ST_Distance_Sphere should be (longitude, latitude) points, it's 2D and the longitude and latitude are valid (validity defined below). Longtitude is the 1st coordinate of the point, and latitude is the 2nd coordinate of the point, both coordinate are degree values. F-9: Valid longitude values MUST be in the range (-180,180]. Positive values are east of the prime meridian. F-10: Valid latitude values MUST be in the range [-90,90]. Positive values are north of the equator. F-11: ST_Distance_Sphere should use a spherical earth and radius of 6370986 meters by default, its returned value should be the minimum distance on the earth in meters between the two geometries. If user specifies the optional earth radius argument, it's used to compute the distance. If this radius argument isn't positive, this function reports ER_WRONG_ARGUMENTS.

Changes to the interface specification: I-1: No new files. I-2: New syntax: Four new functions: <double> = ST_Distance_Sphere(<geometry>, <geometry>, <earth-radius>) <polygon> = ST_MakeEnvelope(<point>, <point>) <bool> = ST_IsValid(<geometry>) <geometry> = ST_Validate(<geometry>) I-3: No new commands. I-4: No new tools. I-5: No impact on existing functionality.

Copyright (c) 2000, 2023, Oracle Corporation and/or its affiliates. All rights reserved.