In this homework, you are going to work with spatial data – you will create some data, visualize it, do queries on it, and visualize the query results.
1. You need to create latitude,longitude spatial coordinates for 9 locations.
One of those will be where your apartment is. The other eight would have to be spread out – spatially distinct, at least 100 feet between adjacent locations (and at most 200 feet).
How would you obtain spatial coordinates at a location? Two ways:
· using the Chrome browser, simply bring up this page on your smartphone (that has GPS), and write down the (latitude,longitude) values that get shown when you load/refresh the page 🙂 As you can see, the page shows your location on a map – cool! Be sure to enable cross-site script loading when you run this (because the script is on our Dropbox area, and accesses a map API at google.com) – click on the shield icon at the right of the URL bar, and click on ‘Load unsafe scripts’. Alternately, you can use this page to obtain the (latitude,longitude) coordinates.
· using your phone’s built-in GPS/compass app, simply read off the displayed GPS coordinate values (if the coordinate display is in degrees, minutes and seconds, you need to convert the minutes,seconds pair of values into a single fractional degree value – one degree is subdivided into 60 minutes (60′), and one minute is subdivided into 60 seconds (60”) – so for example, 30’15”, since it is equivalent to 1815″, would be equal to 1815/3600=0.504 degrees.
Write down location names (you will use them to label your points when displaying).
2. Now that you have 9 coordinates and their label strings (ie. text descriptions such as “Tommy Trojan”, “SAL”), you are going to create a KML file (.kml format) out of them using a text editor. Specifically, each location will be a ‘placemark’ in your .kml file (with a label, and coords). Here is more detail. The .kml file with the 9 placemarks is going to be your starter file, for doing visualizations and queries. Here is a .kml skeleton to get you started (download, rename, edit it to put in your coords and labels).
NOTE – keep your labels to be 15 characters or less (including spaces).
3. Download Google Earth on your laptop, install it, bring it up. Load your .kml file into it – that should show you your 9 sampled locations, on Google Earth’s globe 🙂 Take a snapshot (screengrab) of this, for submitting.
4. Install Oracle 11g+Oracle Spatial, or Postgres+PostGIS on your laptop, and browse the docs for spatial functions.
4 (alt). You can use MySQL or even sqlite. IF YOU ARE FEELING ADVENTUROUS: as an alternative to installing Oracle or Postgres (or MySQL or sqlite), you can use Postgres or Oracle on AWS (without installing anything on your laptop!) – eg. see this page, and this one. Be sure to not leave your DB instance running, when you aren’t working on the hw!
5. You will use spatial db software to execute two spatial queries that you’ll write:
* compute the convex hull for your 9 points [a convex hull for a set of 2D points is the smallest convex polygon that contains the point set]. If you use Oracle, see this page; if you decide to use Postgres, read this and this. Use the query’s result polygon’s coords, to create a polygon in your .kml file (edit the .kml file, add relevant XML to specify the KML polygon’s coords). Load this into Google Earth, visually verify that your 9 points are inside the convex hull, then take a screenshot. Note that even your data points happen to have a concave perimeter and/or happen to be self-intersecting, the convex hull, by definition, would be a tight, enclosing boundary (hull) that is a simple convex polygon. The convex hull is a very useful object – eg. see this discussion.
* compute the three nearest neighbors of your home/apt/dormroom location [look up the spatial function to do this]. Use the query’s results, to create three line segments in your .kml file: line(home,neighbor1), line(home,neighbor2), line(home,neighbor3). Verify this looks correct using Google Earth, take a snapshot.
it is OK to hardcode points, in above queries! Or, you can create and use a table.
Here is what you need to submit (as a single .zip file):
* your .kml file from step 4 above – with the placemarks, convex hull and three nearest-neighbor line segments (1 point)
* a text file (.txt or .sql) with your two queries from step 4 – table creation commands (if you use Postgres and directly specify points in your queries, you won’t have table creation commands, in which case you wouldn’t need to worry about this part), and the queries themselves (6 points)
* screengrabs from steps 3,4 (1.5+1.5 = 3 points)
BONUS QUESTION! [1 point]
Using SGM 123 as the center, compute (don’t use GPS!) a set (sequence) of lat-long (ie. spatial) co-ordinates that lie along a pretty Spirograph(TM) curve 🙂 Create a new KML file with these points, visualize it on Google Earth, submit these three items: your point generation code (see below), the resulting .kml file (“spiro.kml”) and a screenshot (“spiro.jpg” or “spiro.png”).
For the Spirograph curve point creation, use the following parametric equations (with R=5, r=1, a=4):
x(t) = (R+r)*cos((r/R)*t) – a*cos((1+r/R)*t)
y(t) = (R+r)*sin((r/R)*t) – a*sin((1+r/R)*t)
Using the above equations, loop through t from 0.00 to n*Pi (eg. 2*Pi; note that ‘n’ might need to be more than 2, for the curve to close on itself; and, t is in radians, not degrees), in steps of 0.01. That will give you the sequence of (x,y) points that make up the Spiro curve, which would/should look like the curve in the right side of the screengrab below, when R=5, r=1, a=4 (my JavaScript code for the point generation loop is on the left):
You need to ADD each (x,y) curve point to the (lat,long) of the center, ie. to that of SGM 123 or other – that will give you valid Spiro-based spatial coords for use in your .kml file. You can use any coding language you want, to generate the curve’s coords: JavaScript, C/C++, Java, Python, SQL, MATLAB, Scala, Haskell, Ruby, R..
PS: Here is MUCH more on Spirograph (hypocycloid and epicycloid) curves if you are curious.
From here on out you know how to create graphics (KML files containing vector symbols constructed from points, lines and polygons) overlaid on any map)
/docProps/thumbnail.jpeg