###############################################################################
#                                                                             #
# 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;