Post reply

Spatial Data Geomertry

  • March 17, 2010 at 9:04 am

    #230968

    I am supplied with a list of times and values for one measure (A) and a list of times and values for another measure (B).

    The times may be different so I may get values at 0 minutes, 5 minutes and 30 minutes for A and values at 0 minutes, 20 minutes and 30 minutes for B.

    I always receive values at 0 minutes and 30 minutes for both.

    I am hoping that one of you SQL Gurus can help me. I am hoping that with the new Spatial data types in particular geometry there is an easy way of calculating the area between the two lines?

    Thanks.

    Mark.

  • March 17, 2010 at 12:08 pm

    #1135462

    Sure, just use the STArea() method. Here's an example from Books online.

    DECLARE @g 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);

    SELECT @g.STArea();

    I'm assuming you have long/lat or x/y coordinates for the starting and ending points of the both lines. I will refer to them as LineA and LineB. Construct the WKT (well-known text) string that starts with the word 'POLYGON' by listing the coordinates clockwise in this manner

    1. The starting point of LineA

    2. The starting point of LineB

    3. The ending point of LineB

    4. The ending point of LineA

    5. The starting point of LineA (polygons must connect back to their point of origin)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 17, 2010 at 4:22 pm

    #1135612

    It is geometry rather than geography so no longitude or latitude. The points are points in time and have a value at that time. It is not a straight line. It is a value at 0 minutes, a value at 30 minutes and then there may or may not be values at any individual minute between 0 and 30. The resulting line may be straight or there may be any number of changes in gradient.

    There are two of these lines and I am looking at identifying the value of the area between the two lines.

    So where A is above by by value of 2 for all 30 minutes. The area is 30 * 2.

    I believe that the polygon could be defined and hoping this will lead to the area betweeb the lines.

    Thanks.

    Mark

  • March 17, 2010 at 4:31 pm

    #1135614

    No problem. Declare the variable as geometry and use your x-y values. The polygon can have irregular sides and the area calculation should still work.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 17, 2010 at 6:16 pm

    #1135651

    Thank you very much

    Mark.

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

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