

###################### 
# Well Known Text: standard for geometric objects 
#  representation
#  
# POINT(681574.32 5237595.88)
#
# POINT(1 1)
# LINESTRING(0 0, 1 1, 3 3) 
# POLYGON((0 0, 5 0, 5 5, 0 5, 0 0)) 
# MULTIPOINT, MULTILINE, MULTIPOLYGON, 
# GEOMETRYCOLLECTION (POINT(100 100), ..., LINESTRING(...))

######################
# POLYGON((1 1, 9 1, 9 9, 1 9, 1 1) (3 3, 3 6, 6 6, 6 3, 3 3)) 
######################

######################
# MySQL: 
# hierarchy of types
#
# GEOMETRY:
# POINT
# LINESTRING
# POLYGON
# GEOMETRYCOLLECTION
# MULTIPOINT
# MULTILINESTRING
# MULTIPOLYGON
######################

######################
# Coversion functions
#  ASTEXT() from Geometry type to WKT 
#  GEOMFROMTEXT() from WKT to Geometry type

######################
# Functions on Geometry types
#  DIMENSION   -1  obj vuoto
#                0  punto
#                1 linea
#                2 poligono
#  ENVELOP      return the MBR (Minimum bounding Rectangle)   
#...............

create database glacier;
source ../example/glacier.sql;
show tables;
describe glacier;


select name, ASTEXT(ref), ASTEXT(ela)  from glacier;

select name, GLENGTH(ela), GLENGTH(border)  from glacier;

select name, GLENGTH(ela), GLENGTH(EXTERIORRING(border))  from glacier;


#functions
#GLENGTH(), STARTPOINT(), ENDPOINT()...
#AREA(), EXTERIORRING(), NUMINTERIORRINGS(), ...


set @pt = GEOMFROMTEXT('POINT(33.2 99.9)');
select X(@pt), Y(@pt);
select ASTEXT(@pt);



######################
# Spatial analysis - MBR
#  
#  MBRCONTAINS(G1, G2)  	MBR(G1) contains MBR(G2) 
#  MBRWITHIN(G1, G2)    	MBR(G2) contains MBR(G1) 
#  MBREQUAL(G1, G2)    	MBR(G2) = MBR(G1) 
#  MBRINTERSECTS(G1, G2)    ....
#  MBROVERLAPS(G1, G2)    	....
#  MBRTOUCHES(G1, G2)    	....
#  MBRDISJOINT(G1, G2)    	....



set @r1 = GEOMFROMTEXT('POLYGON((1 1, 5 1, 5 5, 1 5, 1 1))');
set @r2 = GEOMFROMTEXT('POLYGON((4 4, 6 4, 6 6, 4 6, 4 4))');
set @r3 = GEOMFROMTEXT('POLYGON((2 2, 4 2, 4 4, 2 4, 2 2))');
select ASTEXT(@r1);
select ASTEXT(@r2);
select ASTEXT(@r3);

select MBROVERLAPS(@r1,@r2);
select MBRTOUCHES(@r2,@r3);
select MBROVERLAPS(@r2,@r3);

  
######################
# INDEX
# ALTER TABLE name ADD SPATIAL INDEX(col)
#
# explain select ... from.... where MBRCONTAINS(....)


select ASTEXT(border) from glacier;
explain select ASTEXT(border) from glacier;

select name from glacier where MBRCONTAINS(border, GEOMFROMTEXT('POINT(2850 2350)'));




