CMIS 320
Homework


Using the gis.pack.sql and the gis_createinsert.sql answer these queries.


1.What are min, max and average distance of points from the origin (0,0)?

2.What are the smallest and largest X and Y values in the points table?
(the coordinates do not have to be from the same point)

3.How many points are within a distance of 100 from (600,300)?

4.For the area of the rectangles, with one query show the smallest,
largest, total, average, standard deviation, and median of the areas.

5.Repeat the previous question but for the perimeter.

6.Repeat the previous question but for the distance between the two
corner points of rectangle.  (This query can be done in about 367
characters). Don't forget the required table alias.

7.What are the id and two corner points of the minimum area rectangle? 
(can there be a one-sided rectangle??)

8.What is the coefficient of correlation between the area and perimeter
of the rectangles?

9.What is the coefficient of correlation between the area and the
distance between the two corner points of the rectangles?

10.What is the coefficient of correlation between the perimeter and the
distance between the two corner points of the rectangles?

11.What are the id's of the points that are in the largest area
rectangle?  Do this with one query.

12.How many points are contained within rectangles?  Add one for each
rectangle a point is in.

13.How many rectangles intersect other rectangles?  In other words, how
many intersections are there?  Note: if rectangles A and B overlap,
count that as one intersection (not two intersections as in A overlaps
B and B overlaps A).  Hint: join the rectangles table with itself.  Do
not include "self-overlaps".

14.What are the id's of the rectangles that have no points in them? 

15.With N points there are (N*(N-1))/2 connections between them.
So 1000 points have 499500 lines connecting each point to every other
point (this is a "complete" graph, also known as a fully connected undirected
graph.
Find the midpoint of the longest of the connecting lines.
Use two queries to do this (unless you think of a way to do it in one query).
It's ok to have the answer duplicated...

16.Write a function that determines whether a gis_rectangle is wider than
taller, or taller than wider, or is square.
Use it to count the number of rectangles that are wider than taller,
the number that are taller than wider, and the number that are square
(use a query for each count).