英文:
How to look up multipolygon data in mysql by coordinates
问题
// 地图数据
db.Exec(CREATE TABLE IF NOT EXISTS geo_datas( id bigint unsigned primary key auto_increment, level tinyint unsigned NOT NULL, geom multipolygon NOT NULL, country_name varchar(30) NOT NULL, country_code varchar(10) NOT NULL, name varchar(50), minx REAL NOT NULL, miny REAL NOT NULL, maxx REAL NOT NULL, maxy REAL NOT NULL )
)
使用上述代码创建了一个表,并在其中插入了数据。geom列是多边形类型,以下是插入的数据。
MULTIPOLYGON (((5.347486495971623 45.98247528076172, 5.3532729148865315 45.991767883300895, 5.3617901802064125 45.99477386474615, 5.37621974945074 45.99368667602545, 5.380286693572998 45.98820495605469, 5.3969597816467285 45.981689453125, 5.396285533905029 45.97837066650396, 5.378274440765438 45.97423934936529, 5.347486495971623 45.98247528076172)))
之后,我想将坐标所属的多边形数据作为地图坐标点类型获取,尝试了以下操作。
SELECT id
FROM geo_datas
WHERE MBRContains(geom , ST_GeomFromText('Point(37.543238571036824 126.9867128133774)'));
当我执行上述SQL时,它运行了201秒。
并且返回了0个结果。
我拥有的数据是全球数据,结果为0是不可能的,我不知道为什么会返回0。
这个查询也非常慢,我不知道如何解决,请给我一些建议,可以给我一个简单的例子吗?
英文:
// 맵 데이터
db.Exec(`CREATE TABLE IF NOT EXISTS
geo_datas(
id bigint unsigned primary key auto_increment,
level tinyint unsigned NOT NULL,
geom multipolygon NOT NULL,
country_name varchar(30) NOT NULL,
country_code varchar(10) NOT NULL,
name varchar(50),
minx REAL NOT NULL,
miny REAL NOT NULL,
maxx REAL NOT NULL,
maxy REAL NOT NULL
)
`)
Data is created in the table created with the code above. The geom column is a multipolygon type, and the following data is inserted.
MULTIPOLYGON (((5.347486495971623 45.98247528076172, 5.3532729148865315 45.991767883300895, 5.3617901802064125 45.99477386474615, 5.37621974945074 45.99368667602545, 5.380286693572998 45.98820495605469, 5.3969597816467285 45.981689453125, 5.396285533905029 45.97837066650396, 5.378274440765438 45.97423934936529, 5.347486495971623 45.98247528076172)))
After that, I wanted to get the multipolygon data to which the coordinates belong as a map coordinate point type, and I tried the following.
SELECT id
FROM geo_datas
WHERE MBRContains(geom , ST_GeomFromText('Point(37.543238571036824 126.9867128133774)'));
When I executed the above sql, it ran for 201 seconds.
And it returned 0 results.
The data I have is world data, a result of 0 is impossible, I don't know why it returns 0.
The query is also very slow, I don't know how to solve it, please give me some advice, can I have a simple example?
答案1
得分: 1
将以下内容翻译为中文:
ALTER TABLE geo_datas
ADD SPATIAL(multipolygon);
英文:
ALTER TABLE geo_datas
ADD SPATIAL(multipolygon);
答案2
得分: 0
_, err = db.Exec(CREATE TABLE IF NOT EXISTS geo_datas( id bigint unsigned primary key auto_increment, level tinyint unsigned NOT NULL, geom multipolygon NOT NULL SRID 0, country_name varchar(30) NOT NULL, country_code varchar(10) NOT NULL, name varchar(50), minx REAL NOT NULL, miny REAL NOT NULL, maxx REAL NOT NULL, maxy REAL NOT NULL, index idx_level (level), index idx_country_code (country_code), index idx_country_name (country_name), index idx_name (name), spatial index (geom) )
)
我将表格更改为上述代码
query := "SELECT level, name, country_name, country_code, ST_AsGeoJSON(geom, 20) geom " +
"FROM geo_datas " +
"WHERE ST_Contains(geom, ST_GeomFromText('Point(%v %v)')) " +
"ORDER BY level asc "
将代码更改为上述内容后,一切正常运行。
英文:
_, err = db.Exec(`CREATE TABLE IF NOT EXISTS
geo_datas(
id bigint unsigned primary key auto_increment,
level tinyint unsigned NOT NULL,
geom multipolygon NOT NULL SRID 0,
country_name varchar(30) NOT NULL,
country_code varchar(10) NOT NULL,
name varchar(50),
minx REAL NOT NULL,
miny REAL NOT NULL,
maxx REAL NOT NULL,
maxy REAL NOT NULL,
index idx_level (level),
index idx_country_code (country_code),
index idx_country_name (country_name),
index idx_name (name),
spatial index (geom)
)
`)
i changed the table as above code
query := "SELECT level, name, country_name, country_code, ST_AsGeoJSON(geom, 20) geom " +
"FROM geo_datas " +
"WHERE ST_Contains(geom, ST_GeomFromText('Point(%v %v)')) " +
"ORDER BY level asc "
After changing the code as above, everyting working fine.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论