MySQL 8.0.35 doesn't select addresses when coordinates whithin polygon

51 views Asked by At

I created a minimum SQL code to reproduce an issue that I have

CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4;
use test;
create table addresses (
    id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    addr VARCHAR(255),
    lng decimal(11,8),
    lat decimal(10,8),
    address_point POINT SRID 4326 NOT NULL,
    SPATIAL KEY sp_index (address_point)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

insert into addresses(addr, lng, lat, address_point) 
values('test', 15.0791673, 37.5071435, ST_SRID(POINT(15.0791673, 37.5071435), 4326));

SET @point_to_check = ST_GeomFromText('POINT(15.0791673 37.5071435)', 4326);
SELECT * FROM addresses;

SET @polygons = ST_GeomFromText('MULTIPOLYGON(((8.8 36.0, 17.8 36.0, 17.8 47.1, 8.8 47.1, 8.8 36.0)),((12.4 41.9, 12.6 41.9, 12.6 42.1, 12.4 42.1, 12.4 41.9)))', 4326);
SELECT ST_Within(@point_to_check, @polygons) AS is_within_poligons; -- returns 1

-- empty result
select id, address_point from addresses 
where ST_WITHIN(address_point, @polygons);

SELECT address_point, @point_to_check,
    ST_Within(address_point, @polygons) AS table_point,
    ST_Within(@point_to_check, @polygons) AS var_point,
    ST_Equals(address_point, @point_to_check) AS compare
FROM addresses;

Why last 2 SELECTS return an empty result while SELECT ST_Within(@point_to_check, @polygons) AS is_within_poligons; returns 1?

UPD 1. I checked ST_AsText()(syggested bellow) and got this: POINT(37.5071435 15.0791673), POINT(15.0791673 37.5071435). Looks like ST_SRID(POINT(15.0791673, 37.5071435), 4326) switch coordinates, because ST_GeomFromText('POINT(15.0791673 37.5071435)', 4326) works correct

0

There are 0 answers