Which is the correct spatial data type?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182410

    wolfkillj (9/19/2014)


    okbangas (9/19/2014)


    I have some doubt on the correctness of this question. As you get the coordinates from a GPS system and your task is to store the coordinates (calculation of distance is not mentioned), I sincerely think that both geography and geometry would do, but the best option would possibly be to decimal numbers. If you should do calculation on the coordinates, it would be a completely different story.

    Given that geography and geometry were the only options, geography is definitely the best available answer. I agree, though, that unless there's a known need to apply any of the spatial functions to the data, it's probably best to store the coordinates as decimals[/url]. They can always be converted to the geography type as needed.

    First of all, thanks to all for the response and taking the time to answer the question.

    Geometry and geography are the only spatial data types available in SQL Server, hence no other options given;-) There is quite some misunderstanding when it comes to the spatial data and datatypes, the purpose of this question being an attempt to clarify this to a certain degree (easier to write a question than an article:-P)

    Have a good weekend everyone and thanks again for the response.

    😎

  • Ozzmodiar

    Ten Centuries

    Points: 1295

    An excellent and interesting questions!

    Thanks so much. I've not had an opportunity to actually work with either data type in my career so I did quite a bit of reading on both before I made my choice. Thankfully I got it right!

    And @TomThompson; don't feel too bad, I'm sure you'll get it next time. I don't think the timing on it was right this time around. It's a good idea with some good reasoning, unlike those d*mn Quebecors we have here in Canada! 😛

    They have tried a few times to no avail.

  • Kick6Tiger

    SSCrazy

    Points: 2368

    Thank goodness for Top Gear teaching me what a lorry is, or I never would have gotten this right. 😎

    Aigle de Guerre!

  • okbangas

    SSChampion

    Points: 11773

    I've searched a bit, and from what I can see, both Geometry and Gography stores the coordinates as IEEE 754 64-bit float internally, hence there is no difference in precision... Please review the following code:

    DECLARE @geo geography;

    SET @geo = geography::Point(1.234567890123456789, 1.234567890123456789, 4326)

    SELECT @geo.ToString();

    go

    DECLARE @geo geometry;

    SET @geo = geometry::Point(1.234567890123456789, 1.234567890123456789, 4326)

    SELECT @geo.ToString();

    go

    Both select statements return the same:

    POINT (1.2345678901234567 1.2345678901234567)



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • Eirikur Eiriksson

    SSC Guru

    Points: 182410

    okbangas (9/20/2014)


    I've searched a bit, and from what I can see, both Geometry and Gography stores the coordinates as IEEE 754 64-bit float internally, hence there is no difference in precision... Please review the following code:

    DECLARE @geo geography;

    SET @geo = geography::Point(1.234567890123456789, 1.234567890123456789, 4326)

    SELECT @geo.ToString();

    go

    DECLARE @geo geometry;

    SET @geo = geometry::Point(1.234567890123456789, 1.234567890123456789, 4326)

    SELECT @geo.ToString();

    go

    Both select statements return the same:

    POINT (1.2345678901234567 1.2345678901234567)

    The two data types are identical in structure and numerical accuracy, the difference is the actual meaning of the data stored within each type, one can think of geometry as a position and geography as a location. Inspecting the hexadecimal representation of the two:

    DECLARE @geo1 geography;

    DECLARE @geo2 geometry;

    SET @geo1 = geography::Point(1.234567890123456789, 1.234567890123456789, 4326)

    SET @geo2 = geometry::Point(1.234567890123456789, 1.234567890123456789, 4326)

    SELECT CONVERT(VARBINARY(64),@geo1,3) AS GEO_BIN UNION ALL

    SELECT CONVERT(VARBINARY(64),@geo2,3);

    shows that the two are identical

    GEO_BIN

    -----------------------------------------------

    0xE6100000010CFB598C42CAC0F33FFB598C42CAC0F33F

    0xE6100000010CFB598C42CAC0F33FFB598C42CAC0F33F

    Although the SRID is set in both instances, the geometry data type will ignore it which renders it useless for geographical coordinates. Consider the following example:

    DECLARE @GEOGR01 GEOGRAPHY = GEOGRAPHY::Point(50,0,4326);

    DECLARE @GEOGR02 GEOGRAPHY = GEOGRAPHY::Point(51,0,4326);

    DECLARE @GEOM01 GEOMETRY = GEOMETRY::Point(50,0,4326);

    DECLARE @GEOM02 GEOMETRY = GEOMETRY::Point(51,0,4326);

    SELECT @GEOGR01.STDistance(@GEOGR02) AS P_DISTANCE UNION ALL

    SELECT @GEOM01.STDistance(@GEOM02);

    Results

    P_DISTANCE

    ----------------------

    111238.680801466

    1

    The geography returns the distance but the geometry returns the difference between the two x values of the coordinates.

    😎

  • Hany Helmy

    SSChampion

    Points: 13436

    TomThomson (9/19/2014)


    Good question.

    I was feeling too depressed by my nation's decision to stay under England's domination :crying: to do any research this morning, so I took a guess and got it wrong. 🙁

    Hard luck 🙂

  • Hany Helmy

    SSChampion

    Points: 13436

    Like some here said, I never used spatial data types before, so definitly learnt somthing new today, thanx for the question.

  • mickyT

    SSChampion

    Points: 10360

    Good question thanks.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Hany Helmy (9/21/2014)


    Like some here said, I never used spatial data types before, so definitly learnt somthing new today, thanx for the question.

    Same here. Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Thank you Don for the question. Nice one.

    Thank you to Okbangas and Eirikur for putting together the sample.

    Thumb up!

    Cheers,

    Iulian

Viewing 10 posts - 16 through 25 (of 25 total)

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