mysql st_within 不使用虚拟列

Question

我有这样的表格:

CREATE TABLE `jovan_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `properties__name` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.name'))) VIRTUAL,
  `properties__age` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.age'))) VIRTUAL,
  `properties__height` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.height'))) VIRTUAL,
  `properties__address__city` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.city'))) VIRTUAL,
  `properties__address__zip_code` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.zip_code'))) VIRTUAL,
  `properties__address__state` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.state'))) VIRTUAL,
  `properties__address__country` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.country'))) VIRTUAL,
  `properties__address__home_coordinate` point GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.home_coordinate')))) VIRTUAL,
  `properties__address__routes` geometry GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.routes')))) VIRTUAL,
  `__docs` json NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jovan_test_properties__name_index` (`properties__name`),
  KEY `jovan_test_properties__age_index` (`properties__age`),
  KEY `jovan_test_properties__height_index` (`properties__height`),
  KEY `jovan_test_properties__address__city_index` (`properties__address__city`),
  KEY `jovan_test_properties__address__zip_code_index` (`properties__address__zip_code`),
  KEY `jovan_test_properties__address__state_index` (`properties__address__state`),
  KEY `jovan_test_properties__address__country_index` (`properties__address__country`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

插入新值，如下所示:

insert into jovan_test set __docs = '{"properties": {"age": 29, "name": "Rahmat Awaludin", "height": 160, "address": {"city": "Bandung", "state": "Jawa Barat", "routes": {"type": "LineString", "coordinates": [[102, 0], [103, 1], [104, 0], [105, 1]]}, "country": "Indonesia", "zip_code": "43193", "home_coordinate": {"type": "Point", "coordinates": [30, 10]}}}}'

结果如下:

mysql root@localhost:intelligence> select id, st_astext(properties__address__home_coordinate) from jovan_test;
+----+-------------------------------------------------+
| id | st_astext(properties__address__home_coordinate) |
+----+-------------------------------------------------+
| 2  | POINT(30 10)                                    |
+----+-------------------------------------------------+

我想使用 st_within 查询该记录。我这样做是这样的:

select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText('POLYGON((30 0,0 0,0 30,30 30,30 0))',4326))
+----+------------------+-----------------+--------------------+---------------------------+-------------------------------+----------------------------+------------------------------+--------------------------------------+-----------------------------+--------+------------+------------+
| id | properties__name | properties__age | properties__height | properties__address__city | properties__address__zip_code | properties__address__state | properties__address__country | properties__address__home_coordinate | properties__address__routes | __docs | created_at | updated_at |
+----+------------------+-----------------+--------------------+---------------------------+-------------------------------+----------------------------+------------------------------+--------------------------------------+-----------------------------+--------+------------+------------+
0 rows in set

正如你所看到的，我得到了 0 个结果。我做错了什么？

Answer 1

找到了!如果该点位于其边界内，st_within 将返回 false。

此查询有效:

select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText('POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))',4326))