1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
###############################################################################
# #
# ST_VALIDATE checks whether a geometry byte string is valid, #
# if so return it, otherwise return NULL. #
# #
# Creation Date: 2014-12-01 #
# Author : Pavan Naik #
# #
###############################################################################
############################################################################################
# Creating the spatial objects #
############################################################################################
--echo # Creating the spatial Geometry object
USE test;
--echo # ST_VALIDATE must return null when its parameter is NULL
SELECT ST_ASTEXT(ST_VALIDATE( NULL ));
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT(NULL,0)));
SELECT ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT(NULL,4053)));
--echo # ST_VALIDATE raises an error if the data is malformed
--error ER_GIS_INVALID_DATA
DO ST_VALIDATE( x'00000000DEADBEEF');
--echo # ST_VALIDATE raises an error if the SRS is not known
--error ER_SRS_NOT_FOUND
DO ST_ASTEXT(ST_VALIDATE( ST_GEOMFROMTEXT('POINT(0 0)',10)));
--echo # ST_VALIDATE raises an error if longitude is out of range
--error ER_LONGITUDE_OUT_OF_RANGE
DO ST_VALIDATE(ST_GEOMFROMTEXT('POINT(0 270)', 4326));
--echo # ST_VALIDATE raises an error if latitude is out of range
--error ER_LATITUDE_OUT_OF_RANGE
DO ST_VALIDATE(ST_GEOMFROMTEXT('POINT(270 0)', 4326));
--echo # 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;
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;
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;
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;
--echo # 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;
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;
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;
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;
|
