mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
8c2a207d58
Reserve sufficient space in the result for q_append'ed data, as q_append does not itself reserve space like it's append counterpart.
139 lines
6.8 KiB
Text
139 lines
6.8 KiB
Text
# Creating the spatial Geometry object
|
|
USE test;
|
|
# ST_VALIDATE must return null when its parameter is NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE( NULL ));
|
|
ST_ASTEXT(ST_VALIDATE( NULL ))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT(NULL,0)));
|
|
ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT(NULL,0)))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT(NULL,4053)));
|
|
ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT(NULL,4053)))
|
|
NULL
|
|
# ST_VALIDATE raises an error if the data is malformed
|
|
SELECT ST_VALIDATE( x'00000000DEADBEEF');
|
|
ERROR 22023: Invalid GIS data provided to function st_validate.
|
|
# ST_VALIDATE return the input if it is valid
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POINT(15 25)')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POINT(15 25)')))
|
|
POINT(15 25)
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOINT(5 0,25 0,15 10,15 25)'))) c;
|
|
c
|
|
MULTIPOINT(5 0,25 0,15 10,15 25)
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('LINESTRING(10 15,20 15)'))) c;
|
|
c
|
|
LINESTRING(10 15,20 15)
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTILINESTRING((25 0,0 15,15 30,0 5))'))) c;
|
|
c
|
|
MULTILINESTRING((25 0,0 15,15 30,0 5))
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))'))) c;
|
|
c
|
|
POLYGON((5 0,7 10,0 15,10 15,15 25,20 15,30 15,22 10,25 0,15 5,5 0))
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,0 0)),((10 10,10 15,15 15,10 10)))'))) c;
|
|
c
|
|
MULTIPOLYGON(((0 0,0 10,10 10,0 0)),((10 10,10 15,15 15,10 10)))
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(10 10),'
|
|
'MULTIPOINT(0 0,10 10),'
|
|
'LINESTRING(1 1,2 2,3 3),'
|
|
'MULTILINESTRING((0 0,0 10,10 10,10 0),(10 10,10 15,15 15,10 10)))'))) c;
|
|
c
|
|
GEOMETRYCOLLECTION(POINT(10 10),MULTIPOINT(0 0,10 10),LINESTRING(1 1,2 2,3 3),MULTILINESTRING((0 0,0 10,10 10,10 0),(10 10,10 15,15 15,10 10)))
|
|
# The only valid empty geometry is the empty geometrycollection
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POINT()')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POINT()')))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOINT()')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOINT()')))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('LINESTRING()')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('LINESTRING()')))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTILINESTRING(())')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTILINESTRING(())')))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POLYGON(())')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POLYGON(())')))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOLYGON((()),(()))'))) c;
|
|
c
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION()')))
|
|
GEOMETRYCOLLECTION EMPTY
|
|
# Invalid geometries return null
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('LINESTRING(0 0,-0.00 0,0.0 0)'))) c;
|
|
c
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTILINESTRING((0 0))'))) c;
|
|
c
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POLYGON((0 0))')));
|
|
ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POLYGON((0 0))')))
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 1 1, 1 1, 1 1, 1 1)))'))) c;
|
|
c
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(LINESTRING(0 0))'))) c;
|
|
c
|
|
NULL
|
|
# If a polygon or multipolygon has counterclockwise internal rings, the rings are returned counterclockwise
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 2 1, 2 2, 1 2, 1 1))'))) c;
|
|
c
|
|
POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1))
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 2 1, 2 2, 1 2, 1 1)))'))) c;
|
|
c
|
|
MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1)))
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 2 1, 2 2, 1 2, 1 1)))'))) c;
|
|
c
|
|
GEOMETRYCOLLECTION(POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1)))
|
|
# ST_VALIDATE raises an error if longitude is out of range
|
|
SELECT ST_VALIDATE(ST_GEOMFROMTEXT('POINT(0 270)', 4326));
|
|
ERROR HY000: Longitude 270.000000 is out of range in function st_validate. It must be within (-180.000000, 180.000000].
|
|
# ST_VALIDATE raises an error if latitude is out of range
|
|
SELECT ST_VALIDATE(ST_GEOMFROMTEXT('POINT(270 0)', 4326));
|
|
ERROR HY000: Latitude 270.000000 is out of range in function st_validate. It must be within [-90.000000, 90.000000].
|
|
# ST_VALIDATE returns the same geometry as it was given when it is valid
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 0.25 0.75, 0.75 0.75, 0.75 0.25, 0.25 0.25))'))) AS
|
|
valid_polygon;
|
|
valid_polygon
|
|
POLYGON((0 0,1 0,1 1,0 1,0 0),(0.25 0.25,0.25 0.75,0.75 0.75,0.75 0.25,0.25 0.25))
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 0.25 0.75, 0.75 0.75, 0.75 0.25, 0.25 0.25))',4053))) AS
|
|
valid_polygon;
|
|
valid_polygon
|
|
POLYGON((0 0,1 0,1 1,0 1,0 0),(0.25 0.25,0.25 0.75,0.75 0.75,0.75 0.25,0.25 0.25))
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 0.25 0.75, 0.75 0.75, 0.75 0.25, 0.25 0.25))',2000))) AS
|
|
valid_polygon;
|
|
valid_polygon
|
|
POLYGON((0 0,1 0,1 1,0 1,0 0),(0.25 0.25,0.25 0.75,0.75 0.75,0.75 0.25,0.25 0.25))
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 0.25 0.75, 0.75 0.75, 0.75 0.25, 0.25 0.25))',4326))) AS
|
|
valid_polygon;
|
|
valid_polygon
|
|
POLYGON((0 0,1 0,1 1,0 1,0 0),(0.25 0.25,0.25 0.75,0.75 0.75,0.75 0.25,0.25 0.25))
|
|
# ST_VALIDATE returns NULL if the geometry is invalid
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 1.75 0.25, 0.75 0.75, 0.25 0.75, 0.25 0.25))'))) AS
|
|
should_be_null;
|
|
should_be_null
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 1.75 0.25, 0.75 0.75, 0.25 0.75, 0.25 0.25))',4053))) AS
|
|
should_be_null;
|
|
should_be_null
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 1.75 0.25, 0.75 0.75, 0.25 0.75, 0.25 0.25))',2000))) AS
|
|
should_be_null;
|
|
should_be_null
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0
|
|
0),( 0.25 0.25, 1.75 0.25, 0.75 0.75, 0.25 0.75, 0.25 0.25))',4326))) AS
|
|
should_be_null;
|
|
should_be_null
|
|
NULL
|
|
SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMCOLLFROMTEXT(' GEOMETRYCOLLECTION( POLYGON( ( 5 2, 4 3, 9 8, 2 9, 1 6, 3 1, 3 1, 5 2 ) ), LINESTRING( 8 5, 0 3, 4 9, 1 8, 0 8, 5 2 ), POINT( 7 7 ), LINESTRING( 0 1, 1 6, 7 2, 3 4 ), LINESTRING( 5 2, 9 9, 4 4, 7 5, 4 6 ) ) '))) a;
|
|
a
|
|
GEOMETRYCOLLECTION(POLYGON((5 2,4 3,9 8,2 9,1 6,3 1,3 1,5 2)),LINESTRING(8 5,0 3,4 9,1 8,0 8,5 2),POINT(7 7),LINESTRING(0 1,1 6,7 2,3 4),LINESTRING(5 2,9 9,4 4,7 5,4 6))
|