Querys - BD

query-1.sql

/*
Referência: -16.78024 -49.15648
*/

WITH referencia as (
	SELECT ST_Geomfromtext('POINT(-16.78024 -49.15648)', 4326)::geography as point
)


SELECT l.nome,  l.lat, l.lon,  ST_Distance(
	ST_Geomfromtext('POINT (' || lat ||' ' || lon || ')'),
	(SELECT point from referencia)
) as distancia
  
FROM 
  esporte_tem_modalidade em

INNER JOIN modalidade_usa_tipo_obstaculo mto ON mto.nome_modalidade = em.nome_modalidade 
INNER JOIN tipo_obstaculo_em_local tol ON mto.nome_tipo_obstaculo = tol.nome_tipo_obstaculo
INNER JOIN local l ON tol.id_local = l.id
WHERE em.nome_esporte = 'Skateboard'
GROUP by l.id, l.nome,  l.lat, l.lon, distancia
ORDER BY distancia ASC
LIMIT 5

query-2.sql

/* Quais dez tipos de obstáculos mais comuns posso encontrar próximo a minha localização atual? */
 
WITH referencia as (
	SELECT ST_Geomfromtext('POINT(-16.68014 -49.25638)')::geography as ponto
), 
 
pontos_proximos as (
	SELECT  tol.nome_tipo_obstaculo, ST_Distance( 
		ST_Geomfromtext('POINT (' || l.lat ||' ' || l.lon || ')')::geography,
		(SELECT ponto from referencia)
	      ) as distancia
	      
	FROM tipo_obstaculo_em_local tol
	INNER JOIN local l ON l.id = tol.id_local
)

SELECT nome_tipo_obstaculo, count(nome_tipo_obstaculo) as n
FROM pontos_proximos
WHERE distancia < 10000
GROUP by nome_tipo_obstaculo
ORDER BY n DESC
LIMIT 10

query-3.sql

WITH referencia as (
	SELECT ST_Geomfromtext('POINT(-16.68024 -49.25648)')::geography as ponto
), locais as (
SELECT em.nome_esporte, l.nome, count(mo.nome_tipo_obstaculo) as n FROM local l
 
INNER JOIN tipo_obstaculo_em_local tol on tol.id_local = l.id
INNER JOIN modalidade_usa_tipo_obstaculo mo on mo.nome_tipo_obstaculo = tol.nome_tipo_obstaculo
INNER JOIN esporte_tem_modalidade em on mo.nome_modalidade = em.nome_modalidade

WHERE em.nome_esporte = 'Skateboard'
AND ST_Distance(
	ST_Geomfromtext('POINT (' || l.lat ||' ' || l.lon || ')'),
	(SELECT ponto from referencia)
) <= 5000

GROUP by nome_esporte, nome
ORDER BY n DESC
)

SELECT * FROM locais WHERE n >= 10

query-4.sql

/*
Referência: -16.78024 -49.15648
*/
 
WITH referencia as (
	SELECT ST_Geomfromtext('POINT(-16.78024 -49.15648)', 4326)::geography as point
),
 
locais_bike as (
	SELECT l.nome,  l.lat, l.lon,  ST_Distance(
		ST_Geomfromtext('POINT (' || lat ||' ' || lon || ')'),
		(SELECT point from referencia)
	) as distancia
	  
	FROM 
	  esporte_tem_modalidade em
	 
	INNER JOIN modalidade_usa_tipo_obstaculo mto ON mto.nome_modalidade = em.nome_modalidade 
	INNER JOIN tipo_obstaculo_em_local tol ON mto.nome_tipo_obstaculo = tol.nome_tipo_obstaculo
	INNER JOIN local l ON tol.id_local = l.id
	WHERE em.nome_esporte = 'Bike'
	GROUP by l.id, l.nome,  l.lat, l.lon, distancia
	ORDER BY distancia ASC
)
SELECT avg(distancia) from locais_bike

query-5.sql

WITH eventos_local as 
(
	SELECT l.nome, count(e.id) as n_eventos from local as l 
	INNER join evento as e on l.id = e.id_local

	WHERE e.tipo in (12,13) /** Usar tipos de evento desejados */
	GROUP BY l.nome
	ORDER BY n_eventos
)

SELECT * from eventos_local where n_eventos > 1 /** usar mínimo aplicável à consulta */

query-6.sql

WITH frequente_estou_aqui as (
	SELECT login, count(e.id) n from usuario as u
	INNER JOIN evento as e on u.login = e.login_usuario
	WHERE e.tipo = 12 /** usar inteiro que representa o tipo de evento "estou aqui" */
	GROUP BY login
	ORDER BY n DESC
	LIMIT 5
	OFFSET 0
)
SELECT  l.nome from evento e
INNER JOIN local l on l.id = e.id_local
 
WHERE tipo = 12
AND e.login_usuario IN (SELECT login from frequente_estou_aqui)
GROUP BY l.id
LIMIT 5
OFFSET 0

query-7.sql

WITH frequente_estou_aqui as (
	SELECT e.id_local as local, count(e.id) n from usuario as u
	INNER JOIN evento as e on u.login = e.login_usuario
	WHERE e.tipo = 12 /** usar inteiro que representa o tipo de evento "estou aqui" */
	GROUP BY id_local
	ORDER BY n DESC
	LIMIT 5
	OFFSET 0
)
SELECT  l.nome from evento e
INNER JOIN local l on l.id = e.id_local
WHERE tipo = 12
AND l.id IN (SELECT local from frequente_estou_aqui)
GROUP BY l.id
LIMIT 5
OFFSET 0

query-8.sql

SELECT em.nome_esporte, cidade, count (tol.nome_tipo_obstaculo) n FROM local l

INNER JOIN tipo_obstaculo_em_local tol on tol.id_local = l.id
INNER JOIN modalidade_usa_tipo_obstaculo mo on mo.nome_tipo_obstaculo = tol.nome_tipo_obstaculo
INNER JOIN esporte_tem_modalidade em on mo.nome_modalidade = em.nome_modalidade

WHERE cidade = 'Goiania'

GROUP BY cidade, nome_esporte
ORDER BY n DESC

/* Query 4
Referência: -16.78024 -49.15648
*/

WITH local_esporte as (
select l.nome, l.lat, l.lon, l.geoposicao, muto.nome_tipo_obstaculo, etm.nome_esporte from local as l
inner join tipo_obstaculo_em_local as toel on l.id = toel.id_local
inner join modalidade_usa_tipo_obstaculo as muto on toel.nome_tipo_obstaculo = muto.nome_tipo_obstaculo
inner join esporte_tem_modalidade as etm on muto.nome_modalidade = etm.nome_modalidade
)

SELECT ST_Distance('SRID=4326;POINT( -16.78024 -49.15648 )'::geometry, 'SRID=4326;POINT( '|| le.lat || ' ' || le.lon || ' )'::geometry)
from local_esporte as le
where le.nome_esporte = 'Bike'