Querying Geometry Information

  • michael.kaufmann

    SSCrazy

    Points: 2816

    Comments posted to this topic are about the item Querying Geometry Information

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    Great question, got it wrong and learnt something new.

    Thanks

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • baabhu

    SSCertifiable

    Points: 6202

    Hi Michael,

    The correct answer should be query 3 and 4. I did not see anything wrong with query 4.

    I need my points back lol. ๐Ÿ™‚

    --- Babu

  • Koen Verbeeck

    SSC Guru

    Points: 258929

    Got it wrong because I missed that query 4 didn't had capital letters in the middle of the word. Ah well, interesting question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck

    SSC Guru

    Points: 258929

    baabhu (2/2/2012)


    Hi Michael,

    The correct answer should be query 3 and 4. I did not see anything wrong with query 4.

    I need my points back lol. ๐Ÿ™‚

    STLineFromText <> STLinefromtext

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • baabhu

    SSCertifiable

    Points: 6202

    Hi Koen Verbeeck,

    Thanks for pointing out that.

    --- Babu

  • CaptainStu72

    SSC-Addicted

    Points: 476

    I also failed on the same part, good question though. I was even sat there wondering why I had the wrong answer.

    Next time, coffee before question...

  • michael.kaufmann

    SSCrazy

    Points: 2816

    Koen Verbeeck (2/3/2012)


    baabhu (2/2/2012)


    Hi Michael,

    The correct answer should be query 3 and 4. I did not see anything wrong with query 4.

    I need my points back lol. ๐Ÿ™‚

    STLineFromText <> STLinefromtext

    Hi Koen,

    thank you very much for responding to Babu on the issue--and for your feedback.

    @ Babu: That's basically how I learned about case sensitivity when working with geometry/geography data. First thought was there's nothing wrong, so why do I receive an error message? Upon digging deeper learned about the fact that case actually matters here--thanks to Jason Follas' blog.

    - Michael

  • Michael Lysons

    SSCertifiable

    Points: 6466

    Cool - learned new stuff today.

  • Kwex

    Default port

    Points: 1407

    Nice question ... thanks!

  • This was removed by the editor as SPAM

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    And this, dear friends, is why everyone working with SQL Server should be forced to do all development and testing on an instance and in databases with a case-sensitive collation.

    SELECT 'Line' AS "type",

    Geometry::STLineFromText('LINESTRING(0.0 0.0, 1.1 1.1)', 0) AS Representation;

    Msg 243, Level 16, State 4, Line 1

    Type Geometry is not a defined system type.

    In a question that tests some other aspect of SQL Server, I wouldn't mind. But this question is specifically focussed on testing knowledge of case sensitiviy, so in this case, not considering the collation of the database is a bad oversight. Especially because the answer options not only varied upper- and lowercase in the method name, but also in the datatype name. To me, this suggested that the author had used a case sensitive database. That's why I focused on option 2 and 4 only, discarded option 2 for the all-lowercase spelling of the method, then picked option 4 because I saw it started with the correct mixed case and failed to check all the letters. Yes, I think that this spelling mistake was a bit too devious.

    The only correct answer would be "query 3 or none of them, depending on the database collation". Given that this option is not available, I'd argue that "None of them" would even be a better choice than the option currently marked as correct.

    Note to Steve - I suggest you might want to change the question to add "in a database with a case-insensitive collation" to the question; after that change, the answer marked as correct is, in fact, correct.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • michael.kaufmann

    SSCrazy

    Points: 2816

    Hugo Kornelis (2/3/2012)


    But this question is specifically focussed on testing knowledge of case sensitiviy, so in this case, not considering the collation of the database is a bad oversight. Especially because the answer options not only varied upper- and lowercase in the method name, but also in the datatype name. To me, this suggested that the author had used a case sensitive database.

    Hugo,

    great catch and thank you very much for your detailed explanation.

    I have to admit I haven't tested the code on a case sensitive database (typically working with CI collations only) and was more than surprised to find that the method name is case sensitive even though working in a CI setting, while the datatype name is not in such an environment (hence the alterations, as they didn't matter in my incomplete test setup).

    My sincere apologies for having missed this fact.

    Lesson learned--will also test with a CS collation before submitting another QotD (this being my first attempt).

    Thanks again and also a great big thank you for all the feedback so far,

    Michael

  • TomThomson

    SSC Guru

    Points: 104767

    Oh drat, I knew the method name was case sensitive abut just picked the two mixed case ones without checking they were actually right. Totally careless, so no point. That'll teach me to take more care.

    I hate case sensitivity, having done a lot of work with text where the emphasis was on the meaning of the text not its shape so that shape sensitivity was essential and case sensitivity was anathema; so I completely missed the point raised by Hugo that the type name is case sensitive too in databases with case-sensitive default collations.

    I'm somewhat confused about the Geometry type, since query 3 uses the spelling "Geometry", and SQL Server reports the type name as Microsoft.SqlServer.Types.SqlGeometry (if I deliberately provoke an error message by misspelling the method name, the type whose method is not found is so reported even if I have spelled it - in my case-insensitive database setup - "geOMeTrY" just to ensure that it's not mimicing the case used in the query) - so what is the correct case mixture? Presumably "geometry" going by Hugo's note, which seems to make SQL Server's type naming a bit inconsistent.

    Tom

  • michael.kaufmann

    SSCrazy

    Points: 2816

    L' Eomot Inversรฉ (2/3/2012)


    so what is the correct case mixture? Presumably "geometry" going by Hugo's note, which seems to make SQL Server's type naming a bit inconsistent.

    From what I've found out in the meanwhile, your assumption is spot on.

    Regards,

    Michael

Viewing 15 posts - 1 through 15 (of 28 total)

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