TP2

Création des tables

DROP TABLE T1;
 
DROP TABLE T2;
 
CREATE TABLE IF NOT EXISTS T1 (
    id CHAR PRIMARY KEY,
    geo GEOMETRY(MULTIPOLYGON, 4326)
);
 
CREATE TABLE IF NOT EXISTS T2 (
    id CHAR PRIMARY KEY,
    geo GEOMETRY(MULTIPOLYGON, 4326)
);
 
INSERT INTO T1 (id, geo) VALUES
('A', ST_GeomFromText('POLYGON((2 3, 4 3, 4 5, 2 5, 2 3))')),
('B', ST_GeomFromText('POLYGON((1 5, 4 5, 4 8, 1 8, 1 5))')),
('C', ST_GeomFromText('POLYGON((4 8, 5 11, 3 11, 4 8))')),
('D', ST_GeomFromText('POLYGON((5 1, 7 1, 7 3, 5 3, 5 1))')),
('E', ST_GeomFromText('POLYGON((8.5 1, 10 1, 10 3, 8.5 3, 8.5 1))'));
 
INSERT INTO T2 (id, geo) VALUES
('Z', ST_GeomFromText('POLYGON((2 7, 4 7, 5 8, 5 9, 2 9, 2 7))')),
('Y', ST_GeomFromText('POLYGON((12 8, 14 8, 14 9, 12 9, 12 8))')),
('X', ST_GeomFromText('POLYGON((5 0, 15 0, 15 14, 10 14, 10 7, 5 7, 5 0), (8 0.5, 14 0.5, 8 6, 8 0.5))'));

Q1

SELECT t1.id
FROM t1
JOIN t1 b ON b.id = 'B'
WHERE ST_Touches(t1.geo, b.geo);

Q2

SELECT t2.id
FROM t2
JOIN t1 b ON b.id = 'B'
WHERE ST_Intersects(t2.geo, b.geo);

QGIS