mysql - MySQL多边形搜索过程
问题描述
我在 MySQL 5.7 中,我一直试图深入了解一个对我不起作用的过程,我发现我得到了不应为 null 的 null 值。该过程用于查找由名为 csatest1 的多边形表描述的点落在哪个区域内,点在名为 gstest1 的表内。我只需要找到第一个 csatest1 多边形,而不是包含一个作为用户输入的点。
创建和测试与我匹配的测试数据集的说明:
create table gsatest1 (element_id integer, y double, xpanel1 double, xpanel2 double, xpanel3 double, xpanel4 double);
Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(11,-4, -5,-6,-7,-8);
Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(13,-2, -5,-6,-7,-8);
Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(14,-15, -5,-6,-7,-8);
Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(15,-28, -5,-6,-7,-8);
Insert into gsatest1 (element_id, y, xpanel1,xpanel2,xpanel3,xpanel4)values(16,-1500, -5,-6,-7,-8);
Create table csatest1 (area_id integer, Poly polygon,testtext varchar(300));
set@testpoly1=ST_GeomFromText('POLYGON (( -1000 -1000, -1000 10, 10 10, 10 -1000, -1000 -1000 ))');
set@testpoly2=ST_GeomFromText('POLYGON (( -1000 -2000, -1000 10, 10 10, 10 -1000, -1000 -2000 ))');
set@testpoly3=ST_GeomFromText('POLYGON (( -2000 -2000, -2000 20, 20 20, 20 -2000, -2000 -2000 ))');
INSERT INTO csatest1 SET Poly = @testpoly1, area_id = 1, testtext = ST_astext(@testpoly1);
INSERT INTO csatest1 SET Poly = @testpoly2, area_id = 2,testtext = ST_astext(@testpoly2);
INSERT INTO csatest1 SET Poly = @testpoly3, area_id = 3,testtext = ST_astext(@testpoly3);
#test that table of polygons csatest1 is working as planned
Set @ele_no = 2;
Set @poly = (select poly from csatest1 where area_id=@ele_no);
set @texttest = (select testtext from csatest1 where area_id = @ele_no);
Select @ele_no,@poly,@texttest;
下面是我的过程,它运行时没有错误但返回空值。它将点值作为文本返回没有问题,但由于某种原因我无法弄清楚多边形值作为空值出现。
CREATE DEFINER=`root`@`localhost` PROCEDURE `polygon_matcher`(IN gsa_ele_id integer, IN panel_no integer)
BEGIN
Declare done boolean default 0;
Declare xval double;
Declare yval double;
declare i integer default 1;
declare text1 varchar(300);
declare gsa_pointxy point;
Declare textgsa_pointxy varchar(300);
declare csaele_no integer;
declare poly polygon;
declare textcsa_polygon varchar(500);
declare ysno integer default 0;
#declare the cursor, cursor goes through row by row the table with polygons
Declare rows cursor for Select area_id from csatest1;
#declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
# create a table for results
CREATE table datadump (CSA_element_ID integer, textcheck_point varchar (300) , textcheck_polygon varchar (300) , container double);
#find y value only depends on gsa element ID
Set yval = (select y from gsatest1 where element_id=gsa_ele_id order by element_id limit 1);
# extract x value for particular panel number specified by user
set xval=(
SELECT
CASE panel_no
WHEN 1 THEN xpanel1
WHEN 2 THEN xpanel2
WHEN 3 THEN xpanel3
WHEN 4 THEN xpanel4
ELSE NULL
END
FROM
gsatest1 order by element_id asc limit 1);
# make gsa data into a point
set text1 = concat('POINT (',xval,' ',yval,')');
set gsa_pointxy = ST_geomfromtext(text1);
#convert to text for testing
set textgsa_pointxy = ST_astext(gsa_pointxy);
#OPEN THE CURSOR
OPEN rows;
# tell it to loop through all rows
repeat
#get element number from polygons table into cursor
FETCH rows into csaele_no;
set poly = (select poly from csatest1 where area_id=csaele_no order by area_id asc limit 1);
set textcsa_polygon = ST_astext(poly);
# return 1 or zero depending on if statment is true
set ysno = (Select MBRContains(poly , gsa_pointxy));
# put data into results table
Insert into datadump (CSA_element_ID, textcheck_point, textcheck_polygon, container) Values (csaele_no, textgsa_pointxy, textcsa_polygon, ysno);
UNTIL done END REPEAT;
#close the cursor
CLOSE rows;
END
然后我按如下方式调用了这个程序: call polygon_matcher (14,2);
结果:
+----------------+-----------------+-------------------+-----------+
| CSA_element_ID | textcheck_point | textcheck_polygon | container |
+----------------+-----------------+-------------------+-----------+
| 1 | POINT(-6 -15) | NULL | NULL |
| 2 | POINT(-6 -15) | NULL | NULL |
| 3 | POINT(-6 -15) | NULL | NULL |
| 3 | POINT(-6 -15) | NULL | NULL |
+----------------+-----------------+-------------------+-----------+
解决方案
推荐阅读
- azure - 每次 Azure 应用服务重新启动时都会删除 OpenSSH 私钥
- javascript - 延迟重试承诺,直到在 NodeJS 中解决
- javascript - 在我对问题进行分类后切换不起作用
- javascript - 如何以角度触发window.onerror?
- python - 如何在 python 中将单个字符串和多个字符串转换为有效的 set()?
- mitmproxy - mitmproxy:数据中不存在前导 0 的 HTTP 请求
- raspberry-pi - java.lang.UnsatisfiedLinkError: com.slytechs.library.NativeLibrary.dlopen(Ljava/lang/String;)J
- ruby - Ruby在ubuntu上作为服务(systemd)运行时执行系统命令
- python - Python selenium webdriver click() 无法正常工作
- django - django rest框架中的用户外键