Use test; # Test all 3 functions within stored procedure 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 || CALL geom_lineinterpolate(); ST_ASTEXT(ST_PointAtDistance(@g1,0.0)) POINT(10 10) ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,0.0)) POINT(10 10) ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,0.0)) MULTIPOINT((10 10)) ST_ASTEXT(ST_LINEINTERPOLATEPOINT(@g1,NULL)) NULL ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(@g1,NULL)) NULL ST_ASTEXT(ST_PointAtDistance(@g1,NULL)) NULL DROP PROCEDURE geom_lineinterpolate; # 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); SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 0; c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINT(c2,0.0),@pt) = 1; c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINT(c2,0.0)) 1 POINT(10 10) SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 0; c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) SELECT c1, ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_LINEINTERPOLATEPOINTS(c2,0.0),@pt) = 1; c1 ST_ASTEXT(ST_LINEINTERPOLATEPOINTS(c2,0.0)) 1 MULTIPOINT((10 10)) SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 1; c1 ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) 1 POINT(10 10) SELECT c1, ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) FROM view1 WHERE ST_INTERSECTS(ST_POINTATDISTANCE(c2,0.0),@pt) = 0; c1 ST_ASTEXT(ST_POINTATDISTANCE(c2,0.0)) UPDATE tab SET c3 = ST_LINEINTERPOLATEPOINT(@ls,0.0) WHERE c1 = 1; SELECT ST_ASTEXT(c3) FROM tab; ST_ASTEXT(c3) POINT(10 10) UPDATE tab SET c3 = ST_POINTATDISTANCE(@ls,0.1) WHERE c1 = 1; SELECT ST_ASTEXT(c3) FROM tab; ST_ASTEXT(c3) POINT(10.070710678118655 10.070710678118655) UPDATE tab SET c4 = ST_LINEINTERPOLATEPOINTS(@ls,0.0) WHERE c1 = 1; SELECT ST_ASTEXT(c4) FROM tab; ST_ASTEXT(c4) MULTIPOINT((10 10)) DROP VIEW view1; DROP TABLE tab; # The geometry must be a linestring. # Test ST_LINEINTERPOLATEPOINT with Cartesian SRID 0 DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoint. # Test ST_LINEINTERPOLATEPOINT with Geographic DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoint. # Test ST_LINEINTERPOLATEPOINTS with Cartesian SRID 0 DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINT(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoint. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 0), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoints. # Test ST_LINEINTERPOLATEPOINTS with Geographic DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POINT(0 0)', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POINT in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('POLYGON((0 0, 0 1, 1 1, 0 0))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type POLYGON in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOINT((0 0), (1 1))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOINT in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTILINESTRING((0 0, 1 1))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTILINESTRING in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 0 1, 1 1, 0 0)))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type MULTIPOLYGON in st_lineinterpolatepoints. DO ST_LINEINTERPOLATEPOINTS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))', 4326), 1); ERROR 22S01: LINESTRING value is a geometry of unexpected type GEOMCOLLECTION in st_lineinterpolatepoints. # 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); ST_LINEINTERPOLATEPOINTS(NULL, NULL) NULL SELECT ST_LINEINTERPOLATEPOINTS(@g1, NULL); ST_LINEINTERPOLATEPOINTS(@g1, NULL) NULL # 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); ST_LINEINTERPOLATEPOINT(NULL, NULL) NULL SELECT ST_LINEINTERPOLATEPOINT(@g1, NULL); ST_LINEINTERPOLATEPOINT(@g1, NULL) NULL # Test ST_LINEINTERPOLATEPOINT function with Latitude values must be within range [-90, 90]. SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1); ERROR 22S03: A parameter of function st_lineinterpolatepoint contains a geometry with latitude -90.001000, which is out of range. It must be within [-90.000000, 90.000000]. SELECT ST_LINEINTERPOLATEPOINT(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1); ERROR 22S03: A parameter of function st_lineinterpolatepoint contains a geometry with latitude 90.001000, which is out of range. It must be within [-90.000000, 90.000000]. # Longitude values must be within range [-180, 180]. SELECT ST_LINEINTERPOLATEPOINT(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1); ERROR 22S02: A parameter of function st_lineinterpolatepoint contains a geometry with longitude -180.001000, which is out of range. It must be within (-180.000000, 180.000000]. SELECT ST_LINEINTERPOLATEPOINT(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1); ERROR 22S02: A parameter of function st_lineinterpolatepoint contains a geometry with longitude 180.001000, which is out of range. It must be within (-180.000000, 180.000000]. # Test ST_LINEINTERPOLATEPOINTS function with Latitude values must be within range [-90, 90]. SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D62108056C0, 1); ERROR 22S03: A parameter of function st_lineinterpolatepoints contains a geometry with latitude -90.001000, which is out of range. It must be within [-90.000000, 90.000000]. SELECT ST_LINEINTERPOLATEPOINTS(0xE6100000010200000002000000000000000000000000000000000000000000000000000000F2D24D6210805640, 1); ERROR 22S03: A parameter of function st_lineinterpolatepoints contains a geometry with latitude 90.001000, which is out of range. It must be within [-90.000000, 90.000000]. # Longitude values must be within range [-180, 180]. SELECT ST_LINEINTERPOLATEPOINTS(0xE610000001020000000200000079E92631088066C0000000000000000000000000000000000000000000000000, 1); ERROR 22S02: A parameter of function st_lineinterpolatepoints contains a geometry with longitude -180.001000, which is out of range. It must be within (-180.000000, 180.000000]. SELECT ST_LINEINTERPOLATEPOINTS(0xE61000000102000000020000000000000000000000000000000000000079E92631088066400000000000000000, 1); ERROR 22S02: A parameter of function st_lineinterpolatepoints contains a geometry with longitude 180.001000, which is out of range. It must be within (-180.000000, 180.000000].