Country Shape Files and STIntersect

Question

Post reply

Country Shape Files and STIntersect

pietlinden

SSC Guru

Points: 63421
More actions
February 13, 2020 at 7:22 am

#3725511

A friend of mine asked me a really interesting question... Imagine a flight path (I guess a linestring of (Longitude, Latitude) points)… I'm trying to figure out all the countries a flight would overfly. I could do it with STIntersect and STBuffer (to give myself some margin for error). But where do I get the shapefiles for all the countries?
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1328 More actions · Answer 1

https://www.diva-gis.org/gdata

https://hub.arcgis.com/datasets/a21fdb46d23e4ef896f31475217cbb08_1

https://gadm.org/download_country_v3.html

http://www.maplibrary.org/library/index.htm

And many others of varying reliability.

You'll need to use a GIS converter to import into SQL Server.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

I assume that you are using one of the sql spatial types for this ? if so then make sure you are using the geography type and not the geometry type (geography can handle the curvature of the earth and not plot a flight path through the centre of the earth.

no idea where you would get the data, but i'd love to see the solution

MVDBA

pietlinden SSC Guru Points: 63421 More actions · Answer 3

Mike... most of the answer that I found is right here...

DECLARE @g geography;  
 DECLARE @h geography;  
 SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326);  
 SET @h = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);

then this gives you whether the two intersect:

SELECT CASE @g.STIntersects(@h) 
WHEN 1 THEN '@g intersects @h'  
ELSE '@g does not intersect @h'  
END;

So in my case, I'd declare the linestring to describe the flight, and then use STIntersects to see if it intersects a country's borders. Then I can add a buffer using STBuffer (so if the flight comes within 1000 meters of the border, it counts as a crossing), so something like

DECLARE @BufferSize INT = 1000;
SELECT @g.STIntersects(@h.STBuffer(@BufferSize));

I saw this old article by Seth Phelabaum... and it's tantalizingly close to what I want, but not quite it. It has states in the US, but I need all the countries of the world. (Or at least North, Central and South America).

Any thoughts?

thanks!

Pieter