Question: 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

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 Post GIS spatial functions and predicates that we saw earlier:

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

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

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

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

Step by Step Solution

★★★★★

3.58 Rating (162 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

a This query can be answered using the STContains function SELECT ... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Database System Concepts Questions!

Write the following queries in SQL, using the university schema. (We suggest you actually run these queries on a database, using the sample data that we provide on the web site of the book, db...

Write the following queries in SQL, using the university schema. a. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicate names in the...

Write the following queries in relational algebra, using the university schema. a. Find the ID and name of each instructor in the Physics department. b. Find the ID and name of each instructor in a...

* use data structure and object oriented programming language to solve the followin you are required to do the following : Add the following nonmember functions A nonmember function that read the...

please solve fast please please * use data structure and object oriented programming language to solve the followin you are required to do the following : Add the following nonmember functions A...

the following relational algebra questions based on tables S1 and S2 (below) Question 1 What is the output of S1 S2 ? The row identified by sid 22 The rows identified by sid 31 and 58 The row...

SQL download the script database_university sql file from here: https://ufile.io/w7eu2 Section 1: Database Update Create a new database named as "YourUserName_University", and run the script...

A small private hospital specialises on providing medical procedures for a long - term physical rehabilitation of patients. Patients need to stay in the hospital for some period. After successful...

Do not answer if you cannot answer all of them. Problem 2. Answer 2.1-2.3 using university schema classroom (building, room number, capacity) department (dept name, building, budget) course (course...

13-31. Consider the SUPPLIER, PART, and SHIPMENT rela- tions and distributed database mentioned in the section on query optimization in this chapter. a. Write a global SQL query (submitted in...

On all current computers, at least part of the interrupt handlers are written in assembly language. Why?

The Piper Cherokee (a light, single-engine general aviation aircraft) has a wing area of 170 ft 2 and a wing span of 32 ft. Its maximum gross weight is 24501b. The wing uses an NACA 65-415 airfoil,...

The time between unplanned shutdowns of a power plant has an exponential distribution with a mean of 20 days. Find the probability that the time between two unplanned shutdowns is a. less than 14...

13. What is offshoring of white-collar service jobs and how does that practice relate to international trade? Why has offshoring increased over the past few decades? Give an example (other than that...

Describe an efficient greedy algorithm for making change for a specified value using a minimum number of coins, assuming there are four denominations of coins (called quarters, dimes, nickels, and...

Give an efficient algorithm for deleting a string from a standard trie and analyze its running time.

Say that a pattern P of length m is a circular substring of a text T of length n > m if P is a (normal) substring of T, or if P is equal to the concatenation of a suffix of T and a prefix of T, that...

Susan Bright will get returns of 18%,20.3%,14%,17.6%, and 8.3% in the next five years on her investment in CoffeeTown, Inc. stock, which she purchases for $73,419.66 today. What is the arithmetic...

Question 1 : Consider a coupon bond with an 8% annual coupon rate, a 10% interest rate, and a $1000 face value. The bond will mature in 4 years. What is the duration of this bond? Duration is defined...

(Capital Asset Pricing Model) Johnson Manufacturing, Inc., is considering several investments. The rate on Treasury bills is currently 7.5 percent, and the expected return for the market is 14.5...