# Country Shape Files and STIntersect

• pietlinden

SSC Guru

Points: 62848

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!

• rick.thorington

Valued Member

Points: 53

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

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

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

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

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

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