locked
Create Lines From Geography? RRS feed

  • Question

  • I have a text file that contains two complete sets of lat/long data. This file is not yet in an SQL database but will be soon. The first set of coordinates represents the surface location of a well site, the second set represents the bottom hole location where the oil is actually coming from. I want to find a way to create a line from the surface hole to the bottom hole using these coordinates.

    At this time my knowledge of SQL is rather limited and I do not know how to approach this problem.

    Thanks.

    Thursday, July 22, 2010 1:23 AM

Answers

  • The general syntax for creating a linestring connecting two lat/long points is as follows:

    SELECT
    geography::STLineFromText('LINESTRING(Long1 Lat1, Long2 Lat2)', 4326)
    

    So, just substitute in the names of the columns containing your pairs of coordinates (CASTing them to varchar as necessary). Also replace the 4326 with the SRID of the system in which the coordinates are measured if necessary.

    You only mention 2d coordinates - lat/long - are you also recording the depth of the well? If so each point in the linestring needs three coordinates - Long/Lat/Z.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, July 26, 2010 9:25 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:25 AM
    Thursday, July 22, 2010 6:22 AM
    Answerer

All replies

  • The general syntax for creating a linestring connecting two lat/long points is as follows:

    SELECT
    geography::STLineFromText('LINESTRING(Long1 Lat1, Long2 Lat2)', 4326)
    

    So, just substitute in the names of the columns containing your pairs of coordinates (CASTing them to varchar as necessary). Also replace the 4326 with the SRID of the system in which the coordinates are measured if necessary.

    You only mention 2d coordinates - lat/long - are you also recording the depth of the well? If so each point in the linestring needs three coordinates - Long/Lat/Z.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, July 26, 2010 9:25 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 8:25 AM
    Thursday, July 22, 2010 6:22 AM
    Answerer
  • Thank you. the file contains both the ground elevation and the depth but at this time we do not use that information. Am I right in thinking that the results can be exported to either a text or GML file? Ultimately this data will be used in our GIS.

     

    I have your book BTW; obviously I have to read it again!

    Thursday, July 22, 2010 5:34 PM
  • Try pages 142 - 144. There, I use an example to create a new geography column in a table, containing points representing the location of earthquakes.

    Your situation is nearly identical, except that instead of using STPointFromText() and only supplying a single pair of latitude/longitude coordinates, you want to use STLineFromText() and supply two coordinate pairs.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Proposed as answer by Tom Li - MSFT Monday, July 26, 2010 9:25 AM
    Thursday, July 22, 2010 6:22 PM
    Answerer