How to look up multipolygon data in mysql by coordinates

Issue

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

Solution

ALTER TABLE geo_datas
    ADD SPATIAL(multipolygon);

Answered By – Rick James

Answer Checked By – Marilyn (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.