CMIS 320
Name:____________________

Create the Geom database using the geom.sql file.
geom.gif is the world

0. How many points are there:

1. What is the largest X, largest Y, smallest X, smallest Y value in the points table?

2. What are the four extrema points: a query for each is OK

6. The average of the points (i.e. what is the average X and average Y values)
i.e. the centroid (which is not an actual point)

7. The distance of the point that is the closest to the origin (0,0):

8. The point farthest from the origin:

9. The average distance of the points from the origin (0,0)

10. The points that are in the rectangular area bounded by (500,500) (600,500) (500,600) (600,600)

11. The point closest to (666,666)

11b.The average distance from the centroid. Hint: use Set variables.

12. The number of rows in the Cartesian product of the points table with itself.

13. The number of rows in the self-join of the table, using a where clause of p1.pt_id<p2.pt_id
    This is the number of pairs of points, not including point paired with itself.

14. The maximum distance that any pair of points is from each other.
hint: self-join

15. The pair of points that is farthest from each other.

16. The 10 pairs of points that are the closest together.
(minimum distance that any pair of points is from each other.)

16b. The points that are the same. Hint: Equals in the Where clause

17. The sum of the distances between every pair of points.

18. The average distance between a pair of points.

19. The length of the path from point 1 to point 2 to point 3 ... to point 1000.  
Hints: self-join and a where clause of p1.id=p2.id+1
(It's a very inefficient path to traverse all points...)
(Notice that the average distance between a pair of points is about 500
and there are 1000 points, so this traversal distance should be about 500*1000)


2. How many rectangles are there:

3. What is the smallest and largest areas, total area of all the rectangles,
and the average rectangle area in the rectangles table?

4. What is the rect_id and Astext value of the largest area rectangle?

4b. The rectangle (whose Centroid) is furthest from the origin

5. Which points are contained in that largest area rectangle?
Hint: join the tables, restrict to that rect_id and use a Contains

6. How many points are contained within rectangles?
Hint: count the Contains

6b. The rectangles with 5 or more points in them.

7. Which rectangles have no points in them? 
 select rect_id from rectangles where rect_id not in 
  (select distinct rect_id from rectangles,points where  contains(rect,pt) );

8. Which rectangles INTERSECT other rectangles? Hint: self join
the rectangles table with itself.  

9. Which rectangles Contain other rectangles?

10. The pair of rectangles whose Centroids are closest