How to look up multipolygon data in mysql by coordinates-CodePudding

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

CodePudding user response：

ALTER TABLE geo_datas
    ADD SPATIAL(multipolygon);

CodePudding user response：

_, 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.