8.9

Suppose the student relation has an attribute named location of type point, and the classroom relation has an attribute location of type polygon. Write the following queries in SQL using the PostGIS spatial functions and predicates that we saw earlier:

  1. Find the names of all students whose location is within the classroom Packard 101.

  2. Find all classrooms that are within 100 meters of Packard 101; assume all distances are represented in units of meters.

  3. Find the ID and name of student who is geographically nearest to the student with ID 12345.

  4. Find the ID and names of all pairs of students whose locations are less than 200 meters apart.

I recommend reading section “8.4.4 Spatial Queries” of the book.

  1. Find the names of all students whose location is within the classroom Packard 101.

This is an example of Region Queries.

SELECT name 
FROM student 
WHERE ST_Contains(
    student.location, 
    (
        SELECT location 
        FROM classroom 
        WHERE building = 'Packard' 
            AND room_number = '101'
    )
);
  1. Find all classrooms that are within 100 meters of Packard 101; assume all distances are represented in units of meters.

This is an example of a Nearness Queries.

SELECT building, room_number
FROM classroom 
WHERE ST_Distance(
    location,
    (
        SELECT location 
        FROM classroom 
        WHERE building = 'Packard' 
            AND room_number = '101'
    )
) <= 100;
  1. Find the ID and name of student who is geographically nearest to the student with ID 12345.

This is an example of nearest-neighbor query.

WITH distance_bn_12345(id, name, dis) AS (
    SELECT id, name, ST_Distance(
                        location, 
                        (
                            SELECT location 
                            FROM student 
                            WHERE id = '12345'
                        )
                    ) as dis
    FROM student
    WHERE id <> '12345'
)
SELECT id, name
FROM distance_bn_12345
WHERE dis = (SELECT MIN(dis) FROM distance_bn_12345);
  1. Find the ID and names of all pairs of students whose locations are less than 200 meters apart.
SELECT  s1.id AS id1, 
        s1.name AS name1, 
        s2.id AS id2, 
        s2.name AS name2
FROM student AS s1 CROSS JOIN student AS s2
WHERE 
    s1.id < s2.id AND 
    ST_Distance(s1.location, s2.location) < 200;