UTM standard per coordinate geografiche espresse in metri, invece che gradi

Insbruck - coordinate UTM (alto, destra): 681574.32 latitude 5237595.88 longitude

Rappresentazione di linee e poligoni supportata da SQL - OpenGIS for SQL
pubblicato da OGC - Open Geospatial Consortium
come registrare e gestire dati geografici in DB relazionali.

###################### 
# Well Known Text (WKT): standard for geometric objects 
#  representation   
# Well Known Binary (WKB): traduzione binaria del formato WKT usata da MySQL
# co-ordinate sono rappresentate come valori in virgola mobile doppia precisione
#  
# POINT(681574.32 5237595.88)    Co-ordinate di Insbruck in formato WKT
#
# POINT(1 1)
# LINESTRING(0 0, 1 1, 3 3)   due o piu' punti separati da , 
# POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))  doppa parentesi
# MULTIPOINT, MULTILINE, MULTIPOLYGON, 
# GEOMETRYCOLLECTION (POINT(100 100), ..., LINESTRING(...))

######################
# Poligono con un foro al centro
# 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:   punti, linee e poligoni possono essere memorizzati
# POINT                     in colonne di tipo Geometry deelle tabelle.
# LINESTRING
# POLYGON                      poligono e' un anello, con all'interno  
# GEOMETRYCOLLECTION:          altri anelli, tangenti ma non sovrapposti
# MULTIPOINT                   I fori non fanno parte della superficie dell'oggetto
# 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)   - tipo polygon
#...............

create database glacier;
source ../examples/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;

#funzioni per oggetti punto
set @pt = GEOMFROMTEXT('POINT(33.2 99.9)');
select X(@pt), Y(@pt);
select ASTEXT(@pt);

#funzioni per oggetti linestring
#GLENGTH(), STARTPOINT(), ENDPOINT(), ISCLOSED() ...



#funzioni per oggetti Polygon
#AREA(), EXTERIORRING(), NUMINTERIORRINGS(), ...
# nella funzione AREA, tutti i fori del poligono vengono sottratti


set @mp = GEOMFROMTEXT('MULTIPOINT(1 1, 10 99, 0 5)');
select ASTEXT(ENVELOP(@mp));
select GEOMETRYTYPE(@mp);


######################
# Spatial analysis - MBR (bounding box - minimum bounding rectangle)
#  
#  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)        true se MBR(G1) e MBR(G2) si toccano o si sovrappongono
#  MBROVERLAPS(G1, G2)    	true se MBR(G1) e MBR(G2) si  sovrappongono
#  MBRTOUCHES(G1, G2)    	true se MBR(G1) e MBR(G2) si  toccano
#  MBRDISJOINT(G1, G2)    	true se MBR(G1) e MBR(G2) non si  toccano e non si
                                                          sovrappongono


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
# R-tree  gestito con quadratic split 

create table mountain (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
pt POINT NOT NULL,
SPATIAL INDEX (pt));

# ALTER TABLE name ADD SPATIAL INDEX(col)

select name, ASTEXT(ref), ASTEXT(ela) from glacier;
select name, ASTEXT(border) from glacier;
select name, GLENGTH(ela), GLENGTH(border) from glacier;
# poligoni non hanno lunghezza ma solo area

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

select SUM(AREA(border)) from glacier;
# area totale del ghiacciaio

select TOUCHES(g1.border, g2.border)
from glacier AS g1, glacier AS g2 where g1.id=1 and g2.id=2;

######################
# EXPLAIN 
# 

show index from glacier;

explain select ASTEXT(border) from glacier;

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







