CMIS 320 Homework Using the gis.pack.sql and the gis_createinsert.sql answer these queries. What are min, max and average distance of points from the origin (0,0)? SQL> select min(point_distance(gis_point(0,0),pt)),max(point_distance(gis_point(0,0),pt)), avg(point_distance(gis_point(0,0),pt)) from points; MIN(POINT_DISTANCE(GIS_POINT(0,0),PT)) MAX(POINT_DISTANCE(GIS_POINT(0,0),PT)) AVG(POINT_DISTANCE(GIS_POINT(0,0),PT)) -------------------------------------- -------------------------------------- -------------------------------------- 20.5182845 1373.38778 759.639373 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) SQL> select min(p.pt.x),max(p.pt.x),min(p.pt.y),max(p.pt.y) from points p; MIN(P.PT.X) MAX(P.PT.X) MIN(P.PT.Y) MAX(P.PT.Y) ----------- ----------- ----------- ----------- 0 997 1 999 How many points are within a distance of 100 from (600,300)? SQL> select count(*) from points where point_distance(gis_point(600,300),pt)<=100; COUNT(*) ---------- 41 For the area of the rectangles, with one query show the smallest, largest, total, average, standard deviation, and median of the areas. SQL> select min(rect_area(rect)),max(rect_area(rect)),sum(rect_area(rect)),avg(rect_area(rect)),stddev(rect_area(rect)),median(rect_area(rect)) from rectangles; MIN(RECT_AREA(RECT)) MAX(RECT_AREA(RECT)) SUM(RECT_AREA(RECT)) AVG(RECT_AREA(RECT)) STDDEV(RECT_AREA(RECT)) MEDIAN(RECT_AREA(RECT)) -------------------- -------------------- -------------------- -------------------- ----------------------- ----------------------- 0 8160 127149 2542.98 2069.33464 2703.5 Repeat the previous question but for the perimeter. SQL> select min(perimeter(rect)),max(perimeter(rect)),sum(perimeter(rect)),avg(perimeter(rect)),stddev(perimeter(rect)),median(perimeter(rect)) from rectangles; MIN(PERIMETER(RECT)) MAX(PERIMETER(RECT)) SUM(PERIMETER(RECT)) AVG(PERIMETER(RECT)) STDDEV(PERIMETER(RECT)) MEDIAN(PERIMETER(RECT)) -------------------- -------------------- -------------------- -------------------- ----------------------- ----------------------- 12 362 9934 198.68 84.5459518 211 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. SQL> select min(point_distance(r.rect.lowerleft,r.rect.upperright)),max(point_distance(r.rect.lowerleft,r.rect.upperright)),sum(point_distance(r.rect.lowerleft,r.rect.upperright)),avg(point_distance(r.rect.lowerleft,r.rect.upperright)),stddev(point_distance(r.rect.lowerleft,r.rect.upperright)),median(point_distance(r.rect.lowerleft,r.rect.upperright)) from rectangles r; MIN(POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) MAX(POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) ------------------------------------------------------- ------------------------------------------------------- SUM(POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) AVG(POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) ------------------------------------------------------- ------------------------------------------------------- STDDEV(POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) MEDIAN(POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) ---------------------------------------------------------- ---------------------------------------------------------- 5.09901951 128.222463 3762.61301 75.2522602 29.8095544 78.5552743 What are the id and two corner points of the minimum area rectangle? (can there be a one-sided rectangle??) SQL> select * from rectangles where rect_area(rect)=(select min(rect_area(rect)) from rectangles); ID RECT(LOWERLEFT(X, Y), UPPERRIGHT(X, Y)) ---------- ---------------------------------------------------- 16 GIS_RECTANGLE(GIS_POINT(49, 572), GIS_POINT(116, 572)) What is the coefficient of correlation between the area and perimeter of the rectangles? SQL> select corr(rect_area(rect),perimeter(rect)) from rectangles; CORR(RECT_AREA(RECT),PERIMETER(RECT)) ------------------------------------- .922307357 What is the coefficient of correlation between the area and the distance between the two corner points of the rectangles? SQL> select corr(rect_area(rect),point_distance(r.rect.lowerleft,r.rect.upperright)) from rectangles r; CORR(RECT_AREA(RECT),POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) ------------------------------------------------------------------------ .844916099 What is the coefficient of correlation between the perimeter and the distance between the two corner points of the rectangles? SQL> select corr(perimeter(rect),point_distance(r.rect.lowerleft,r.rect.upperright)) from rectangles r; CORR(PERIMETER(RECT),POINT_DISTANCE(R.RECT.LOWERLEFT,R.RECT.UPPERRIGHT)) ------------------------------------------------------------------------ .980595301 What are the id's of the points that are in the largest area rectangle? Do this with one query. SQL> select points.id from points,rectangles where rect_area(rect)=(select max(rect_area(rect)) from rectangles) and point_in_rect(pt,rect)=1; ID ---------- 802 896 911 445 451 537 559 651 667 692 10 rows selected. How many points are contained within rectangles? Add one for each rectangle a point is in. SQL> select count(*) from points,rectangles where point_in_rect(pt,rect)=1; COUNT(*) ---------- 119 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". SQL> select count(*)/2 from rectangles r1, rectangles r2 where r1.id!=r2.id And overlap(r1.rect,r2.rect)=1; COUNT(*)/2 ---------- 14 What are the id's of the rectangles that have no points in them? SQL> select id from rectangles r where 0=(select count(*) from points where point_in_rect(pt,r.rect)=1); ID ---------- 10 15 16 21 22 24 29 38 40 41 43 45 12 rows selected. 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... SQL> select max(point_distance(pts1.pt,pts2.pt)) from points pts1, points pts2; MAX(POINT_DISTANCE(PTS1.PT,PTS2.PT)) ------------------------------------ 1388.05944 SQL> select midpt(pts1.pt,pts2.pt) from points pts1, points pts2 where point_distance(pts1.pt,pts2.pt)>1388; MIDPT(PTS1.PT,PTS2.PT)(X, Y) ------------------------------------------------------------------------------------------------------------------------------------------------- GIS_POINT(489, 502.5) GIS_POINT(489, 502.5) 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). Create Or Replace Function rectangle_shape (r gis_rectangle) Return Number Is xlen Number := r.upperright.x-r.lowerleft.x; ylen Number := r.upperright.y-r.lowerleft.y; Begin If xlen > ylen Then Return -1; Elsif ylen > xlen Then Return 1; Else Return 0; End if; End; / SQL> select count(*) from rectangles where rectangle_shape(rect)=0; COUNT(*) ---------- 1 SQL> select count(*) from rectangles where rectangle_shape(rect)=1; COUNT(*) ---------- 25 SQL> select count(*) from rectangles where rectangle_shape(rect)=-1; COUNT(*) ---------- 24