# Test the functionality of the LINEINTERPOLATEPOINT and ST_LINEINTERPOLATEPOINTS functions Use test; --echo # Test all 3 functions within stored procedure DELIMITER ||; CREATE PROCEDURE geom_lineinterpolate() BEGIN SET @g1 = ST_GeomFromText('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)', 4326); SELECT ST_ASTEXT(ST_PointAtDistance(@g1,0.0)); SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,0.0)); SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,0.0)); SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,NULL)); SELECT ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,NULL)); SELECT ST_ASTEXT(ST_PointAtDistance(@g1,NULL)); END || DELIMITER ;|| CALL geom_lineinterpolate(); # clean up DROP PROCEDURE geom_lineinterpolate; --echo # Test all 3 functions in UPDATE and WHERE and SELECT Clauses CREATE TABLE tab(c1 INT,c2 LINESTRING,c3 POINT,c4 GEOMETRY); CREATE VIEW view1 AS SELECT * FROM tab; SET @ls = ST_GEOMFROMTEXT('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)'); INSERT INTO tab(c1,c2) VALUES(1,@g1); SET @pt = ST_GEOMFROMTEXT('POINT(10 10)',4326); # should be 0 records are displayed SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 0; SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 1; # should be 0 records are displayed SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 0; SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 1; SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 1; # should be 0 records are displayed SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 0; UPDATE tab SET c3 = ST_LINEINTERPOLATEPOINT(@ls,0.0) WHERE c1 = 1; SELECT ST_ASTEXT(c3) FROM tab; UPDATE tab SET c3 = ST_POINTATDISTANCE(@ls,0.1) WHERE c1 = 1; SELECT ST_ASTEXT(c3) FROM tab; UPDATE tab SET c4 = ST_LINEINTERPOLATEPOINTS(@ls,0.0) WHERE c1 = 1; SELECT ST_ASTEXT(c4) FROM tab; # clean up DROP VIEW view1; DROP TABLE tab; --echo # The geometry must be a linestring. --echo # Test ST_LINEINTERPOLATEPOINT with Cartesian SRID 0 --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1); --echo # Test ST_LINEINTERPOLATEPOINT with Geographic --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1); --echo # Test ST_LINEINTERPOLATEPOINTS with Cartesian SRID 0 --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1); --echo # Test ST_LINEINTERPOLATEPOINTS with Geographic --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1); --error ER_UNEXPECTED_GEOMETRY_TYPE DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1); --echo # Test ST_LINEINTERPOLATEPOINTS with NULL SET @g1 = ST_GeomFromText('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)', 4326); SELECT ST_LINEINTERPOLATEPOINTS(NULL, NULL); SELECT ST_LINEINTERPOLATEPOINTS(@g1, NULL); --echo # Test ST_LINEINTERPOLATEPOINT with NULL SET @g1 = ST_GeomFromText('LINESTRING(10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90)', 4326); SELECT ST_LINEINTERPOLATEPOINT(NULL, NULL); SELECT ST_LINEINTERPOLATEPOINT(@g1, NULL); --echo # Test ST_LINEINTERPOLATEPOINT function with Latitude values must be within range [-90, 90]. --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1); --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1); --echo # Longitude values must be within range [-180, 180]. --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINT(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1); --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINT(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1); --echo # Test ST_LINEINTERPOLATEPOINTS function with Latitude values must be within range [-90, 90]. --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1); --error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1); --echo # Longitude values must be within range [-180, 180]. --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINTS(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1); --error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE SELECT ST_LINEINTERPOLATEPOINTS(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1);