空间索引-点在线上

1、建表，同时建索引

SRID 取 0，代表平面坐标系。

create table spatial_point_in_line (
	`id` int unsigned not null auto_increment primary key ,
	`min` int unsigned not null default 0,
	`max` int unsigned not null default 0,
	`line_geo` geometry  not null srid 0,
	SPATIAL INDEX index_spatial_line_geo(`line_geo`)
)

 2、插入数据

insert into spatial_point_in_line (min, max, line_geo)
values 
-- (80, 90,  ST_GeomFromText(CONCAT('LINESTRING(', min, ' 0,', max, ' 0)'), 0)),
-- (80, 100, ST_GeomFromText(CONCAT('LINESTRING(', min, ' 0,', max, ' 0)'), 0)),
-- (90, 100, ST_GeomFromText(CONCAT('LINESTRING(', min, ' 0,', max, ' 0)'), 0)),
-- (100, 110, ST_GeomFromText(CONCAT('LINESTRING(', min, ' 0,', max, ' 0)'), 0)),
-- (100, 120, ST_GeomFromText(CONCAT('LINESTRING(', min, ' 0,', max, ' 0)'), 0))
(80, 90,  LineString(Point(min, 0), Point(max, 0))),
(80, 100, LineString(Point(min, 0), Point(max, 0))),
(90, 100, LineString(Point(min, 0), Point(max, 0))),
(100, 110, LineString(Point(min, 0), Point(max, 0))),
(100, 120, LineString(Point(min, 0), Point(max, 0)))

3、点在线上，不在两端

ST_Contains 同 MBRContains，其他类似。

EXPLAIN
select *
from spatial_point_in_line
-- where ST_Contains(line_geo, ST_GeomFromText(CONCAT('POINT(', 81, ' 0)'), 0))
where ST_Contains(line_geo, Point(81, 0))

 

 

4、点在线上，可在两端

EXPLAIN
select *
from spatial_point_in_line
-- where ST_Intersects(line_geo, ST_GeomFromText(CONCAT('POINT(', 80, ' 0)'), 0))
where ST_Intersects(line_geo, Point(80, 0))

 

​​​​​​​