DBM Week 5

Laboratory Report DeVry University College of Engineering and Information Sciences

Course Number: DBM449

Laboratory Number: 4

Laboratory Title: Spatial Indices

Note: There is no limit on how much information you will enter under the three topics below. It is important to be clear and complete with your comments. Like a scientist you are documenting your progress in this week’s lab experiment.

Objectives: (In your own words what was this lab designed to accomplish? What was its purpose?)

Understand and become familiar with current capabilities and limitations of the OpenGIS implementation in MySQL. Learn to create, update, and use spatial indices. Explore practical approaches to calculating distances between points on the Earth’s surface. Understand fundamentals of geotagging. Create stored procedures to determine real-world distances, and to process spatial queries returning result sets of data points within a bounding rectangle. Explore visualization of GIS data.

Results: (Discuss the steps you used to complete your lab. Were you successful? What did you learn? What were the results? Explain what you did to accomplish each step. You can include screen shots, code listings, and so on. to clearly explain what you did. Be sure to record all results specifically directed by the lab procedure. Number all results to reflect the procedure number to which they correspond.)

3. DDL

5. Insert GIS Data

USE DBM449Lab4;

INSERT INTO Points (name, location) VALUES ( 'DeVry University' , GeomFromText('POINT(41.882844 -87.634982)'));

INSERT INTO Points (name, location) VALUES ( 'Museum of Science' , GeomFromText('POINT(41.8357734 -87.7009021)' ) );

INSERT INTO Points (name, location) VALUES ( 'Horseshoe Casino' , GeomFromText('POINT(41.7869902 -87.7162545)' ) );

INSERT INTO Points (name, location) VALUES ( 'Bensenville' , GeomFromText('POINT(41.9620321 -87.976798)'));

INSERT INTO Points (name, location) VALUES ( 'BAPS Shri Mandir' , GeomFromText('POINT(41.8704303 -88.13295)' ) );

INSERT INTO Points (name, location) VALUES ( 'Aurora IL' , GeomFromText('POINT(41.7509511 -88.3767589)' ) );

6. Explain what use GeoFromText

GeoFromText is used to populate data to spatial columns as values are stored in internal geometry format so we need to use the location text to convert it to format for storing geometry data. In this case we got the point with longitude/latitude and used to GeoFromText function format it for storage.

7. Results of insert statements

8. Select *from Location

9. Explain ASTEXT()

ASTEXT() function gets the data form binary format and displays it in readable format. So as in this case we want to display the Location in text format we need to convert the geometry data to readable data by using the astext() function. Otherwise the binary data will display the garbage values which are not readable.

10. Show Location as-text

11. Explain

To calculate the distance between two points the mathematical formula is used:

Math.sin(dLat/2) * Math.sin(dLat/2) +

Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2);

The coefficient used for this formula is 3959 which shows the results in miles.

12. Stored procedure to calculate distance

13. Explain

To calculate the boundary and check the other points are within that range on not, first we have to calculate the distance from all the points and then need to check that the distance from the point is within the range or not.

14. Stored procedure to calculate the boundary

15. Show points within 20miles

16. Bounding box limitations

There can be few points which are less than the 20 miles but is not showing in the results because the Bounding box is a polygon.

17. Explain

Google maps can be used to visualize by mapping and displaying the spatial data.

18. Query to get nearest location

SELECT B.Name, X(B.location) as longitude, Y(B.location) as latitude, ( 3959 * acos( cos( radians(Y(center.Location)) )

* cos( radians( Y(B.location) ) )

* cos( radians( X(B.location) ) - radians(X(center.Location)) )

+ sin( radians(Y(center.Location)) )

* sin( radians( Y(B.location) ) ) ) ) AS distance

FROM POINTS center JOIN POINTS B ON center.Name <> B.Name

WHERE center.Name = 'DeVry University'

ORDER BY Distance LIMIT 1;

19. Results of query to get nearest location

22. Format google map url for the nearest location

24. Google map result

The image is showing the route from store to friends’ home. It shows different routes that can be taken from the source to the destination.

Conclusions: (After completing this lab, in your own words, what conclusions can you draw from this experience?)

In this lab I learned to work with OpenGIS support in MySQL. Practical hands on experience on working with spatial data. Understanding the available functions to manipulate geometry data. I learned to develope the functions to calculate distance between two locations and was also able to check the locations near me with in a bounded rectangle. Finally, I was able to generate the google map-url and seeing it visually.