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

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

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