0 - Base de datos para las pruebas
postgres=# create database test;
CREATE DATABASE
postgres=# \c test;
Ahora está conectado a la base de datos «test».
1 - Puntos
test=# create table puntos(p point);
CREATE TABLE
test=# insert into puntos(p) values('(3,2)');
INSERT 0 1
test=# insert into puntos(p) values('(1,1)');
INSERT 0 1
test=# insert into puntos(p) values('(0,0)');
INSERT 0 1
test=# insert into puntos(p) values('(1,3)');
INSERT 0 1
test=# select * from puntos;
p
-------
(3,2)
(1,1)
(0,0)
(1,3)
(4 filas)
1.1 - Distancia entre puntos
test=# select p1.p as origen, p2.p as destino, p1.p <-> p2.p as distancia from puntos p1, puntos p2;
origen | destino | distancia
--------+---------+------------------
(3,2) | (3,2) | 0
(3,2) | (1,1) | 2.23606797749979
(3,2) | (0,0) | 3.60555127546399
(3,2) | (1,3) | 2.23606797749979
(1,1) | (3,2) | 2.23606797749979
(1,1) | (1,1) | 0
(1,1) | (0,0) | 1.4142135623731
(1,1) | (1,3) | 2
(0,0) | (3,2) | 3.60555127546399
(0,0) | (1,1) | 1.4142135623731
(0,0) | (0,0) | 0
(0,0) | (1,3) | 3.16227766016838
(1,3) | (3,2) | 2.23606797749979
(1,3) | (1,1) | 2
(1,3) | (0,0) | 3.16227766016838
(1,3) | (1,3) | 0
(16 filas)
1.2 - Comprobar si un punto está a la izquierda de otro
test=# select p1.p as origen, p2.p as destino, p1.p << p2.p as esta_a_la_izquierda from puntos p1, puntos p2;
origen | destino | esta_a_la_izquierda
--------+---------+---------------------
(3,2) | (3,2) | f
(3,2) | (1,1) | f
(3,2) | (0,0) | f
(3,2) | (1,3) | f
(1,1) | (3,2) | t
(1,1) | (1,1) | f
(1,1) | (0,0) | f
(1,1) | (1,3) | f
(0,0) | (3,2) | t
(0,0) | (1,1) | t
(0,0) | (0,0) | f
(0,0) | (1,3) | t
(1,3) | (3,2) | t
(1,3) | (1,1) | f
(1,3) | (0,0) | f
(1,3) | (1,3) | f
(16 filas)
1.3 - Comprobar si un punto está a la derecha de otro
test=# select p1.p as origen, p2.p as destino, p1.p >> p2.p as esta_a_la_derecha from puntos p1, puntos p2;
origen | destino | esta_a_la_derecha
--------+---------+-------------------
(3,2) | (3,2) | f
(3,2) | (1,1) | t
(3,2) | (0,0) | t
(3,2) | (1,3) | t
(1,1) | (3,2) | f
(1,1) | (1,1) | f
(1,1) | (0,0) | t
(1,1) | (1,3) | f
(0,0) | (3,2) | f
(0,0) | (1,1) | f
(0,0) | (0,0) | f
(0,0) | (1,3) | f
(1,3) | (3,2) | f
(1,3) | (1,1) | f
(1,3) | (0,0) | t
(1,3) | (1,3) | f
(16 filas)
1.4 - Comprobar si un punto está por debajo de otro
test=# select p1.p as origen, p2.p as destino, p1.p <^ p2.p as esta_por_debajo from puntos p1, puntos p2;
origen | destino | esta_por_debajo
--------+---------+-----------------
(3,2) | (3,2) | f
(3,2) | (1,1) | f
(3,2) | (0,0) | f
(3,2) | (1,3) | t
(1,1) | (3,2) | t
(1,1) | (1,1) | f
(1,1) | (0,0) | f
(1,1) | (1,3) | t
(0,0) | (3,2) | t
(0,0) | (1,1) | t
(0,0) | (0,0) | f
(0,0) | (1,3) | t
(1,3) | (3,2) | f
(1,3) | (1,1) | f
(1,3) | (0,0) | f
(1,3) | (1,3) | f
(16 filas)
1.5 - Comprobar si un punto está por encima de otro
test=# select p1.p as origen, p2.p as destino, p1.p >^ p2.p as esta_por_encima from puntos p1, puntos p2;
origen | destino | esta_por_encima
--------+---------+-----------------
(3,2) | (3,2) | f
(3,2) | (1,1) | t
(3,2) | (0,0) | t
(3,2) | (1,3) | f
(1,1) | (3,2) | f
(1,1) | (1,1) | f
(1,1) | (0,0) | t
(1,1) | (1,3) | f
(0,0) | (3,2) | f
(0,0) | (1,1) | f
(0,0) | (0,0) | f
(0,0) | (1,3) | f
(1,3) | (3,2) | t
(1,3) | (1,1) | t
(1,3) | (0,0) | t
(1,3) | (1,3) | f
(16 filas)
1.6 - Comprobar si dos puntos están alineados horizontalmente
test=# select p1.p as origen, p2.p as destino, p1.p ?- p2.p as horizonalmente_alineados from puntos p1, puntos p2;
origen | destino | horizonalmente_alineados
--------+---------+--------------------------
(3,2) | (3,2) | t
(3,2) | (1,1) | f
(3,2) | (0,0) | f
(3,2) | (1,3) | f
(1,1) | (3,2) | f
(1,1) | (1,1) | t
(1,1) | (0,0) | f
(1,1) | (1,3) | f
(0,0) | (3,2) | f
(0,0) | (1,1) | f
(0,0) | (0,0) | t
(0,0) | (1,3) | f
(1,3) | (3,2) | f
(1,3) | (1,1) | f
(1,3) | (0,0) | f
(1,3) | (1,3) | t
(16 filas)
1.7 - Comprobar si dos puntos están alineados verticalmente
test=# select p1.p as origen, p2.p as destino, p1.p ?| p2.p as verticalmente_alineados from puntos p1, puntos p2;
origen | destino | verticalmente_alineados
--------+---------+-------------------------
(3,2) | (3,2) | t
(3,2) | (1,1) | f
(3,2) | (0,0) | f
(3,2) | (1,3) | f
(1,1) | (3,2) | f
(1,1) | (1,1) | t
(1,1) | (0,0) | f
(1,1) | (1,3) | t
(0,0) | (3,2) | f
(0,0) | (1,1) | f
(0,0) | (0,0) | t
(0,0) | (1,3) | f
(1,3) | (3,2) | f
(1,3) | (1,1) | t
(1,3) | (0,0) | f
(1,3) | (1,3) | t
(16 filas)
1.8 - Comprobar si dos puntos son iguales
test=# select p1.p as origen, p2.p as destino, p1.p ~= p2.p as son_el_mismo from puntos p1, puntos p2;
origen | destino | son_el_mismo
--------+---------+--------------
(3,2) | (3,2) | t
(3,2) | (1,1) | f
(3,2) | (0,0) | f
(3,2) | (1,3) | f
(1,1) | (3,2) | f
(1,1) | (1,1) | t
(1,1) | (0,0) | f
(1,1) | (1,3) | f
(0,0) | (3,2) | f
(0,0) | (1,1) | f
(0,0) | (0,0) | t
(0,0) | (1,3) | f
(1,3) | (3,2) | f
(1,3) | (1,1) | f
(1,3) | (0,0) | f
(1,3) | (1,3) | t
(16 filas)
2 - Segmentos
test=# create table segmentos(s lseg);
CREATE TABLE
test=# insert into segmentos(s) values('((0,0),(2,0))');
INSERT 0 1
test=# insert into segmentos(s) values('((0,2),(2,2))');
INSERT 0 1
test=# insert into segmentos(s) values('((1,3),(1,-1))');
INSERT 0 1
test=# select * from segmentos;
s
----------------
[(0,0),(2,0)]
[(0,2),(2,2)]
[(1,3),(1,-1)]
(3 filas)
2.1 - Comprobar si dos segmentos son perpendiculares
test=# select s1.s as segmento_1, s2.s as segmento_2, s1.s ?-| s2.s as son_perpendiculares from segmentos s1, segmentos s2;
segmento_1 | segmento_2 | son_perpendiculares
----------------+----------------+---------------------
[(0,0),(2,0)] | [(0,0),(2,0)] | f
[(0,0),(2,0)] | [(0,2),(2,2)] | f
[(0,0),(2,0)] | [(1,3),(1,-1)] | t
[(0,2),(2,2)] | [(0,0),(2,0)] | f
[(0,2),(2,2)] | [(0,2),(2,2)] | f
[(0,2),(2,2)] | [(1,3),(1,-1)] | t
[(1,3),(1,-1)] | [(0,0),(2,0)] | t
[(1,3),(1,-1)] | [(0,2),(2,2)] | t
[(1,3),(1,-1)] | [(1,3),(1,-1)] | f
(9 filas)
2.2 - Comprobar si dos segmentos son paralelos
test=# select s1.s as segmento_1, s2.s as segmento_2, s1.s ?|| s2.s as son_paralelos from segmentos s1, segmentos s2;
segmento_1 | segmento_2 | son_paralelos
----------------+----------------+---------------
[(0,0),(2,0)] | [(0,0),(2,0)] | t
[(0,0),(2,0)] | [(0,2),(2,2)] | t
[(0,0),(2,0)] | [(1,3),(1,-1)] | f
[(0,2),(2,2)] | [(0,0),(2,0)] | t
[(0,2),(2,2)] | [(0,2),(2,2)] | t
[(0,2),(2,2)] | [(1,3),(1,-1)] | f
[(1,3),(1,-1)] | [(0,0),(2,0)] | f
[(1,3),(1,-1)] | [(0,2),(2,2)] | f
[(1,3),(1,-1)] | [(1,3),(1,-1)] | t
(9 filas)
2.3 - Comprobar si dos segmentos se cortan
test=# select s1.s as segmento_1, s2.s as segmento_2, s1.s ?# s2.s as se_cortan from segmentos s1, segmentos s2;
segmento_1 | segmento_2 | se_cortan
----------------+----------------+-----------
[(0,0),(2,0)] | [(0,0),(2,0)] | f
[(0,0),(2,0)] | [(0,2),(2,2)] | f
[(0,0),(2,0)] | [(1,3),(1,-1)] | t
[(0,2),(2,2)] | [(0,0),(2,0)] | f
[(0,2),(2,2)] | [(0,2),(2,2)] | f
[(0,2),(2,2)] | [(1,3),(1,-1)] | t
[(1,3),(1,-1)] | [(0,0),(2,0)] | t
[(1,3),(1,-1)] | [(0,2),(2,2)] | t
[(1,3),(1,-1)] | [(1,3),(1,-1)] | f
(9 filas)
2.4 - Hallar el punto del segmento más cercano a un punto dado
test=# select s as segmento, p as punto, p ## s as punto_mas_cercano from segmentos, puntos;
segmento | punto | punto_mas_cercano
----------------+-------+-------------------
[(0,0),(2,0)] | (3,2) | (2,0)
[(0,2),(2,2)] | (3,2) | (2,2)
[(1,3),(1,-1)] | (3,2) | (1,2)
[(0,0),(2,0)] | (1,1) | (1,0)
[(0,2),(2,2)] | (1,1) | (1,2)
[(1,3),(1,-1)] | (1,1) | (1,1)
[(0,0),(2,0)] | (0,0) | (0,0)
[(0,2),(2,2)] | (0,0) | (0,2)
[(1,3),(1,-1)] | (0,0) | (1,0)
[(0,0),(2,0)] | (1,3) | (1,0)
[(0,2),(2,2)] | (1,3) | (1,2)
[(1,3),(1,-1)] | (1,3) | (1,3)
(12 filas)
2.5 - Comprobar si el segmento es horizontal
test=# select s, ?- s as es_horizontal from segmentos;
s | es_horizontal
----------------+---------------
[(0,0),(2,0)] | t
[(0,2),(2,2)] | t
[(1,3),(1,-1)] | f
(3 filas)
2.6 - Comprobar si el segmento es vertical
test=# select s, ?| s as es_vertical from segmentos;
s | es_vertical
----------------+-------------
[(0,0),(2,0)] | f
[(0,2),(2,2)] | f
[(1,3),(1,-1)] | t
(3 filas)
2.7 - Calcular la longitud de un segmento
test=# select s as segmento,length(s) as longitud from segmentos;
segmento | longitud
----------------+----------
[(0,0),(2,0)] | 2
[(0,2),(2,2)] | 2
[(1,3),(1,-1)] | 4
(3 filas)
3 - Cajas
test=# create table cajas(b box);
CREATE TABLE
test=# insert into cajas(b) values('((0,0),(3,3))');
INSERT 0 1
test=# insert into cajas(b) values('((2,2),(4,4))');
INSERT 0 1
test=# select * from cajas;
b
-------------
(3,3),(0,0)
(4,4),(2,2)
(2 filas)
3.1 - Comprobar si dos cajas se solapan y obtener la caja que se forma con el solapamiento
test=# select c1.b as caja_1, c2.b as caja_2, c1.b # c2.b as caja_solapada from cajas c1, cajas c2;
caja_1 | caja_2 | caja_solapada
-------------+-------------+---------------
(3,3),(0,0) | (3,3),(0,0) | (3,3),(0,0)
(3,3),(0,0) | (4,4),(2,2) | (3,3),(2,2)
(4,4),(2,2) | (3,3),(0,0) | (3,3),(2,2)
(4,4),(2,2) | (4,4),(2,2) | (4,4),(2,2)
(4 filas)
3.2 - Comprobar si dos cajas tienen algún punto en común
test=# select c1.b as caja_1, c2.b as caja_2, c1.b && c2.b as tienen_punto_en_comun from cajas c1, cajas c2;
caja_1 | caja_2 | tienen_punto_en_comun
-------------+-------------+-----------------------
(3,3),(0,0) | (3,3),(0,0) | t
(3,3),(0,0) | (4,4),(2,2) | t
(4,4),(2,2) | (3,3),(0,0) | t
(4,4),(2,2) | (4,4),(2,2) | t
(4 filas)
3.3 - Comprobar si una caja contiene a un punto dado
test=# select b as caja, p as punto, b @> p as caja_contiene_al_punto from cajas, puntos;
caja | punto | caja_contiene_al_punto
-------------+-------+------------------------
(3,3),(0,0) | (3,2) | t
(4,4),(2,2) | (3,2) | t
(3,3),(0,0) | (1,1) | t
(4,4),(2,2) | (1,1) | f
(3,3),(0,0) | (0,0) | t
(4,4),(2,2) | (0,0) | f
(3,3),(0,0) | (1,3) | t
(4,4),(2,2) | (1,3) | f
(8 filas)
3.4 - Calcular anchura, altura, centro y area de una caja
test=# select b as caja,width(b) as anchura,height(b) as altura,center(b) as centro,area(b) as area from cajas;
caja | anchura | altura | centro | area
-------------+---------+--------+-----------+------
(3,3),(0,0) | 3 | 3 | (1.5,1.5) | 9
(4,4),(2,2) | 2 | 2 | (3,3) | 4
(2 filas)
4 - Caminos
test=# create table caminos(p path);
CREATE TABLE
test=# insert into caminos(p) values('[(0,0),(0,1),(2,1),(2,3)]');
INSERT 0 1
test=# insert into caminos(p) values('[(0,0),(2,2),(2,3)]');
INSERT 0 1
4.1 - Calcular la longitud de un camino (dos maneras distintas)
test=# select p as camino, @-@ p as longitud from caminos;
camino | longitud
---------------------------+------------------
[(0,0),(0,1),(2,1),(2,3)] | 5
[(0,0),(2,2),(2,3)] | 3.82842712474619
(2 filas)
test=# select p as camino, length(p) as longitud from caminos;
camino | longitud
---------------------------+------------------
[(0,0),(0,1),(2,1),(2,3)] | 5
[(0,0),(2,2),(2,3)] | 3.82842712474619
(2 filas)
4.2 - Comprobar si un punto dado pertenece a un camino
test=# select p.p as punto, c.p as camino, p.p <@ c.p as punto_pertenece_al_camino from caminos c, puntos p;
punto | camino | punto_pertenece_al_camino
-------+---------------------------+---------------------------
(3,2) | [(0,0),(0,1),(2,1),(2,3)] | f
(3,2) | [(0,0),(2,2),(2,3)] | f
(1,1) | [(0,0),(0,1),(2,1),(2,3)] | t
(1,1) | [(0,0),(2,2),(2,3)] | t
(0,0) | [(0,0),(0,1),(2,1),(2,3)] | t
(0,0) | [(0,0),(2,2),(2,3)] | t
(1,3) | [(0,0),(0,1),(2,1),(2,3)] | f
(1,3) | [(0,0),(2,2),(2,3)] | f
(8 filas)
4.3 - Contar el número de puntos de un camino
test=# select p as camino, npoints(p) as numero_de_puntos from caminos;
camino | numero_de_puntos
---------------------------+------------------
[(0,0),(0,1),(2,1),(2,3)] | 4
[(0,0),(2,2),(2,3)] | 3
(2 filas)
4.4 - Comprobar si un camino es abierto (el punto final no coincide con el origen)
test=# select p as camino, isopen(p) as es_abierto from caminos;
camino | es_abierto
---------------------------+------------
[(0,0),(0,1),(2,1),(2,3)] | t
[(0,0),(2,2),(2,3)] | t
(2 filas)
4.5 - Comprobar si un camino es cerrado (el punto final coincide con el origen)
test=# select p as camino, isclosed(p) as es_cerrado from caminos;
camino | es_cerrado
---------------------------+------------
[(0,0),(0,1),(2,1),(2,3)] | f
[(0,0),(2,2),(2,3)] | f
(2 filas)
4.6 - Veamos la diferencia con un camino cerrado
test=# delete from caminos;
DELETE 2
test=# insert into caminos(p) values('((0,0),(2,2),(2,3))');
INSERT 0 1
test=# select p as camino, isclosed(p) as es_cerrado from caminos;
camino | es_cerrado
---------------------+------------
((0,0),(2,2),(2,3)) | t
(1 fila)
test=# select p as camino, length(p) as longitud from caminos;
camino | longitud
---------------------+------------------
((0,0),(2,2),(2,3)) | 7.43397840021018
(1 fila)
5 - Polígonos
test=# create table poligonos(p polygon);
CREATE TABLE
test=# insert into poligonos(p) values('((0,0),(2,2),(4,0))');
INSERT 0 1
test=# insert into poligonos(p) values('((0,0),(2,0),(2,2),(0,2))');
INSERT 0 1
test=# select * from poligonos;
p
---------------------------
((0,0),(2,2),(4,0))
((0,0),(2,0),(2,2),(0,2))
(2 filas)
5.1 - Comprobar si un punto dado está contenido en el polígono
test=# select pu.p as punto, po.p as poligono, pu.p <@ po.p as esta_contenido from puntos pu,poligonos po;
punto | poligono | esta_contenido
-------+---------------------------+----------------
(3,2) | ((0,0),(2,2),(4,0)) | f
(3,2) | ((0,0),(2,0),(2,2),(0,2)) | f
(1,1) | ((0,0),(2,2),(4,0)) | t
(1,1) | ((0,0),(2,0),(2,2),(0,2)) | t
(0,0) | ((0,0),(2,2),(4,0)) | t
(0,0) | ((0,0),(2,0),(2,2),(0,2)) | t
(1,3) | ((0,0),(2,2),(4,0)) | f
(1,3) | ((0,0),(2,0),(2,2),(0,2)) | f
(8 filas)
6 - Círculos
test=# create table circulos(c circle);
CREATE TABLE
test=# insert into circulos(c) values('<((0,0),1>');
INSERT 0 1
test=# insert into circulos(c) values('<((0,0),5>');
INSERT 0 1
test=# insert into circulos(c) values('<((10,0),5>');
INSERT 0 1
6.1 - Calcular el centro, diametro, radio y area de un círculo
test=# select c as circulo, center(c) as centro, diameter(c) as diametro, radius(c) as radio, area(c) as area from circulos;
circulo | centro | diametro | radio | area
------------+--------+----------+-------+------------------
<((0,0),1> | (0,0) | 2 | 1 | 3.14159265358979
<((0,0),5> | (0,0) | 10 | 5 | 78.5398163397448
<((10,0),5> | (10,0) | 10 | 5 | 78.5398163397448
(3 filas)
6.2 - Comprobar si un punto dado está contenido en un círculo
test=# select p.p as punto, c.c as circulo, p.p <@ c.c as esta_contenido from puntos p, circulos c;
punto | circulo | esta_contenido
-------+------------+----------------
(3,2) | <((0,0),1> | f
(3,2) | <((0,0),5> | t
(3,2) | <((10,0),5> | f
(1,1) | <((0,0),1> | f
(1,1) | <((0,0),5> | t
(1,1) | <((10,0),5> | f
(0,0) | <((0,0),1> | t
(0,0) | <((0,0),5> | t
(0,0) | <((10,0),5> | f
(1,3) | <((0,0),1> | f
(1,3) | <((0,0),5> | t
(1,3) | <((10,0),5> | f
(12 filas)
6.3 - Calcular la distancia entre dos círculos
test=# select c1.c as circulo_1, c2.c as circulo_2, c1.c <-> c2.c as distancia from circulos c1, circulos c2;
circulo_1 | circulo_2 | distancia
------------+------------+-----------
<((0,0),1> | <((0,0),1> | 0
<((0,0),1> | <((0,0),5> | 0
<((0,0),1> | <((10,0),5> | 4
<((0,0),5> | <((0,0),1> | 0
<((0,0),5> | <((0,0),5> | 0
<((0,0),5> | <((10,0),5> | 0
<((10,0),5> | <((0,0),1> | 4
<((10,0),5> | <((0,0),5> | 0
<((10,0),5> | <((10,0),5> | 0
(9 filas)
6.4 - Comprobar si dos círculos tienen solapamiento
test=# select c1.c as circulo_1, c2.c as circulo_2, c1.c && c2.c as solapados from circulos c1, circulos c2;
circulo_1 | circulo_2 | solapados
------------+------------+-----------
<((0,0),1> | <((0,0),1> | t
<((0,0),1> | <((0,0),5> | t
<((0,0),1> | <((10,0),5> | f
<((0,0),5> | <((0,0),1> | t
<((0,0),5> | <((0,0),5> | t
<((0,0),5> | <((10,0),5> | t
<((10,0),5> | <((0,0),1> | f
<((10,0),5> | <((0,0),5> | t
<((10,0),5> | <((10,0),5> | t
(9 filas)