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?


  • rick.thorington

    Valued Member

    Points: 53

    And many others of varying reliability.

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

  • MVDBA (Mike Vessey)


    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


  • 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' 

    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?



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

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