如何通过坐标在MySQL中查找多边形数据

huangapple go评论68阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2022年2月3日 01:39:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/70960246.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定