November 6, 2019 at 11:14 pm
A friend of mine asked me a really interesting question, and I'm a little stumped. She's trying to determine from a given flight plan, which countries a plane will fly over. I was thinking the flight plan could be described as a linestring of (long, lat) points and then I could use STIntersect to see if they actually intersect. (I even found some code that shows some of this...)
use tempdb;
go
DECLARE @g geometry;
DECLARE @h geometry;
--SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @g = geometry::STGeomFromText('LINESTRING(0 1, 0 3, 0 4)',0);
SET @h = geometry::STGeomFromText('POINT(0 2)', 0);
SELECT @g.STIntersects(@h);
The part I don't understand how to do is to eliminate as many "landmarks"/countries that are too far away. Do I just add a buffer value to the long/lat values so that instead of a series of lines, I get a series of rectangles? (Or should I have all of my landmarks stored in a table, and filter the table on as many attributes as necessary to reduce the set of points/polygons to something manageable?
(Or do I need to read up on spatial indexes?)
Sorry for the thinking out loud, but I haven't done a lot with geography data types in forever. If there are any useful resources for this, feel free to point them out.
Thanks!
November 7, 2019 at 4:42 pm
You first need to do some research on spatial concepts. You can only consider flight paths to be straight lines over very small distances. If you are flying over multiple countries, you are actually having to fly a "great circle route", which looks like a big curve on a flat map, but a straight line on a globe.
Once you have the great circle curve plotted in terms of lat/long points, you can use stIntersect to see if it intersects the geometries of any countries. Using rectangles for the countries can save you some CPU time in only testing against countries you might fly close to. (The more detailed the geometry, the more work it is test for an intersection.
Good Luck
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 7, 2019 at 4:46 pm
Thanks!
Any suggestions where to start?
November 15, 2019 at 7:58 pm
Sorry for being gone so long, regretfully I was in the hospital. Apologies. Sadly, I worked with all this years ago and have given away all my books. Hopefully by now, you've found a good source of information through googling. Good luck.
Almost forgot. I did find this article about calculating an initial bearing for a great circle route. It is at least a starting point: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/da179b3e-b4fb-48a9-ad24-9c9428bc7db6/sql-server-calculate-initial-bearing-from-latitude-amp-longitude-using-geography?forum=transactsql
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply