MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Geohash is a system for encoding latitude and longitude
coordinates of arbitrary precision into a text string. Geohash
values are strings that contain only characters chosen from
"0123456789bcdefghjkmnpqrstuvwxyz"
.
The functions in this section enable manipulation of geohash values, which provides applications the capabilities of importing and exporting geohash data, and of indexing and searching geohash values.
ST_GeoHash(
,
longitude
,
latitude
,
max_length
)ST_GeoHash(
point
,
max_length
)
Returns a geohash string in the connection character set and collation.
If any argument is NULL
, the return value
is NULL
. If any argument is invalid, an
error occurs.
For the first syntax, the longitude
must be a number in the range [−180, 180], and the
latitude
must be a number in the
range [−90, 90]. For the second syntax, a
POINT
value is required, where the X and Y
coordinates are in the valid ranges for longitude and
latitude, respectively.
The resulting string is no longer than
max_length
characters, which has an
upper limit of 100. The string might be shorter than
max_length
characters because the
algorithm that creates the geohash value continues until it
has created a string that is either an exact representation of
the location or max_length
characters, whichever comes first.
mysql> SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
+----------------------+-------------------------+
| ST_GeoHash(180,0,10) | ST_GeoHash(-180,-90,15) |
+----------------------+-------------------------+
| xbpbpbpbpb | 000000000000000 |
+----------------------+-------------------------+
ST_LatFromGeoHash(
geohash_str
)
Returns the latitude from a geohash string value, as a
DOUBLE
value in the range
[−90, 90].
If the argument is NULL
, the return value
is NULL
. If the argument is invalid, an
error occurs.
The ST_LatFromGeoHash()
decoding function reads no more than 433 characters from the
geohash_str
argument. That
represents the upper limit on information in the internal
representation of coordinate values. Characters past the 433rd
are ignored, even if they are otherwise illegal and produce an
error.
mysql> SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
+------------------------------------------+
| ST_LatFromGeoHash(ST_GeoHash(45,-20,10)) |
+------------------------------------------+
| -20 |
+------------------------------------------+
ST_LongFromGeoHash(
geohash_str
)
Returns the longitude from a geohash string value, as a
DOUBLE
value in the range
[−180, 180].
If the argument is NULL
, the return value
is NULL
. If the argument is invalid, an
error occurs.
The remarks in the description of
ST_LatFromGeoHash()
regarding
the maximum number of characters processed from the
geohash_str
argument also apply to
ST_LongFromGeoHash()
.
mysql> SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));
+-------------------------------------------+
| ST_LongFromGeoHash(ST_GeoHash(45,-20,10)) |
+-------------------------------------------+
| 45 |
+-------------------------------------------+
ST_PointFromGeoHash(
geohash_str
,
srid
)
Returns a POINT
value containing the
decoded geohash value, given a geohash string value.
The X and Y coordinates of the point are the longitude in the range [−180, 180] and the latitude in the range [−90, 90], respectively.
If any argument is NULL
, the return value
is NULL
. If any argument is invalid, an
error occurs.
The srid
argument is an unsigned
32-bit integer.
The remarks in the description of
ST_LatFromGeoHash()
regarding
the maximum number of characters processed from the
geohash_str
argument also apply to
ST_PointFromGeoHash()
.
mysql>SET @gh = ST_GeoHash(45,-20,10);
mysql>SELECT ST_AsText(ST_PointFromGeoHash(@gh,0));
+---------------------------------------+ | ST_AsText(ST_PointFromGeoHash(@gh,0)) | +---------------------------------------+ | POINT(45 -20) | +---------------------------------------+