我有一张这样的table:
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的结果。我做错什么了?
1条答案
按热度按时间pbossiut1#
找到了!如果该点在其边界内,则内的stu将返回false。
此查询有效: