mysql8でspatial indexを効かせるためにはSRIDを統一しておく必要があるかもしれない話 - Qiita の続きみたいな

近傍検索

｢ある地点から半径2km以内の場所を調べたい｣ぐらいの簡単な検索で作られるクエリが

SELECT count(*) FROM locations WHERE 
ST_Within(
	location,
	ST_GeomFromText(
		St_AsText(
			ST_Buffer(
				ST_GeomFromText(
					St_AsText(
						ST_GeomFromText('POINT(43.76747 142.297905)' , 4326)
					)
				),
				2000 * 180.0 / 3.141592653589793 / 6378137.0
			)
		)
	,4326)
);

こんなんになってて笑う。

st_geomFromText st_asText st_geomFromText st_asText st_geomFromText st_asText st_geomFromText

2ch全AAイラスト化計画

本来書きたいのはこっち。

SELECT count(*) FROM locations WHERE 
ST_Within(
	location,
	ST_Buffer(
		ST_GeomFromText('POINT(43.76747 142.297905)' , 4326),
		2000 * 180.0 / 3.141592653589793 / 6378137.0
	)
);

SQL   : #22S00st_buffer(POINT, ...) has not been implemented for geographic spatial reference systems.

くっそ。

｢st_bufferはgeographic SRS用には作られてねぇよばーかばーか｣とあるのでここは我慢する。

SELECT count(*) FROM locations WHERE 
ST_Within(
	location,
	ST_Buffer(
		ST_GeomFromText(
			St_AsText(
				ST_GeomFromText('POINT(43.76747 142.297905)' , 4326)
			)
		),
		2000 * 180.0 / 3.141592653589793 / 6378137.0
	)
);

SQL   : #HY000The SRID of the geometry does not match the SRID of the column 'location'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.

ぐぬぬ。

なんでこんなことになっているのか

空間リレーション関数と空間演算子関数の違い。

ST_Bufferの含まれる空間演算子関数はこうなっている。

12.16.8 Spatial Operator Functions
OpenGIS proposes a number of functions that can produce geometries. They are designed to implement spatial operators.
These functions support all argument type combinations except those that are inapplicable according to the Open Geospatial Consortium specification.
Unless otherwise specified, functions in this section handle their arguments as follows:

If any argument is NULL, the return value is NULL.

If any geometry argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.

If any geometry argument has an SRID value that refers to an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error occurs.

For functions that take multiple geometry arguments, if those arguments do not have the same SRID, an ER_GIS_DIFFERENT_SRIDS error occurs.

If any geometry argument has an SRID value for a geographic SRS, an ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS error occurs.

Otherwise, the return value is non-NULL.

MySQL :: MySQL 8.0 Reference Manual :: 12.16.8 Spatial Operator Functions

対してST_withinの空間リレーション関数はこう

12.16.9.1 Spatial Relation Functions That Use Object Shapes
The OpenGIS specification defines the following functions to test the relationship between two geometry values g1 and g2, using precise object shapes. The return values 1 and 0 indicate true and false, respectively, except for ST_Distance(), which returns distance values.
Functions in this section detect arguments in either Cartesian or geographic spatial reference systems (SRSs), and return results appropriate to the SRS.
Unless otherwise specified, functions in this section handle their arguments as follows:

If any argument is NULL or any geometry argument is an empty geometry, the return value is NULL.

If any geometry argument is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.

If any geometry argument refers to an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error occurs.

For functions that take multiple geometry arguments, if those arguments do not have the same SRID, an ER_GIS_DIFFERENT_SRIDS error occurs.

If any geometry argument is geometrically invalid, either the result is true or false (it is undefined which), or an error occurs.

For geographic SRS geometry arguments, if any argument has a longitude or latitude that is out of range, an error occurs:

If a longitude value is not in the range (−180, 180], an ER_LONGITUDE_OUT_OF_RANGE error occurs.

If a latitude value is not in the range [−90, 90], an ER_LATITUDE_OUT_OF_RANGE error occurs.

Ranges shown are in degrees. If an SRS uses another unit, the range uses the corresponding values in its unit. The exact range limits deviate slightly due to floating-point arithmetic.

Otherwise, the return value is non-NULL.

These object-shape functions are available for testing

あ、これgeography型とgeometry型じゃね?
ジオグラフィ型というものがある - Qiita

なんとなくの理解

空間系の関数の中には直行(デカルト)座標系を前提にしているものと地理座標系を前提にしてるもの、両方いけるものがある。

st_buffer は直交座標系の値を受けて直交座標系の結果を返す
st_within は2つの引数のSRIDが同じじゃないとダメ

なので、原点POINT(43.76747 142.297905)#srid:4326をst_bufferに食わせるためには

ST_GeomFromText(
	St_AsText(
		ST_GeomFromText('POINT(43.76747 142.297905)' , 4326)
	)
)

こうしてSRIDを除去してやる必要がある。

さらにこれをST_withinに食わせるためには第一引数のlocationのSRID 4326に合わせてやる必要があるので

ST_GeomFromText(
	St_AsText(ST_Buffer(<略>))
,4326)

こうしてSRID 4326に引き戻す必要があると。

なんか結局

直交座標系と地理座標系で同じgeometry型を使ってるのが面倒の原因な気がしてきた。