// 맵 데이터
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?
_, 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.