Loading ...
Sorry, an error occurred while loading the content.

SQL Query to select pairs / groups of rows

Expand Messages
  • darrylgreig
    Hi All, I am very new to SQL, and for all my reading I can t find an answer to my query scenario. The context is a database of locations on images, where each
    Message 1 of 6 , Aug 4, 2008
    • 0 Attachment
      Hi All,

      I am very new to SQL, and for all my reading I can't find an answer
      to my query scenario. The context is a database of locations on
      images, where each row consists of a single location. For simplicity
      say the row is ImgFileName, XPos, Ypos. Multiple rows may have the
      same ImgFileName referring to different locations on the same image.

      I would like some way to get my hands on the data corresponding to
      the query:

      "give me all pairs of locations that are found on the same image and
      are closer than distance d to each other"

      I am guessing the best return table structure is something like:
      ImgFileName XPos1, YPos1, XPos2, YPos2
      but any other workable structure would be fine.

      Any ideas how to formulate an SQL query to do this? It needs to be
      generic SQL as I am using it across multiple platforms.

      Thanks,
      Darryl.
    • Arnie Rowland
      I would try something like: SELECT ImgFileName, XPos, YPos FROM YourTable WHERE abs( XPos - YPos )
      Message 2 of 6 , Aug 4, 2008
      • 0 Attachment
        I would try something like:

        SELECT
        ImgFileName,
        XPos,
        YPos
        FROM YourTable
        WHERE abs( XPos - YPos ) <= @d_Param

        Depending upon the data structure and volume, a persisted and indexed computed column [abs( XPos - YPos )] might be useful.

        Regards,

        Arnie Rowland, MVP (SQL Server)

        "I am a great believer in luck, and I find that the harder I work, the more I have of it."
        - Thomas Jefferson (1743-1826)


        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of darrylgreig
        Sent: Monday, August 04, 2008 8:06 PM
        To: Arnie
        Subject: [SQLQueriesNoCode] SQL Query to select pairs / groups of rows

        Hi All,

        I am very new to SQL, and for all my reading I can't find an answer
        to my query scenario. The context is a database of locations on
        images, where each row consists of a single location. For simplicity
        say the row is ImgFileName, XPos, Ypos. Multiple rows may have the
        same ImgFileName referring to different locations on the same image.

        I would like some way to get my hands on the data corresponding to
        the query:

        "give me all pairs of locations that are found on the same image and
        are closer than distance d to each other"

        I am guessing the best return table structure is something like:
        ImgFileName XPos1, YPos1, XPos2, YPos2
        but any other workable structure would be fine.

        Any ideas how to formulate an SQL query to do this? It needs to be
        generic SQL as I am using it across multiple platforms.

        Thanks,
        Darryl.



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

        Yahoo! Groups Links







        Disclaimer - August 4, 2008
        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
      • Bob Filipiak
        Is this correct: Image Xpos Ypos pic1 2 4 pic2 2 3 pic1 3 2 pic1 5 4 You want to get all of the pic1 rows and
        Message 3 of 6 , Aug 5, 2008
        • 0 Attachment
          Is this correct:

          Image Xpos Ypos

          pic1 2 4
          pic2 2 3
          pic1 3 2
          pic1 5 4


          You want to get all of the pic1 rows and then check the distance
          between the cartisian coordinates.

          On Mon, Aug 4, 2008 at 4:16 AM, darrylgreig <darrylgreig@...> wrote:
          > Hi All,
          >
          > I am very new to SQL, and for all my reading I can't find an answer
          > to my query scenario. The context is a database of locations on
          > images, where each row consists of a single location. For simplicity
          > say the row is ImgFileName, XPos, Ypos. Multiple rows may have the
          > same ImgFileName referring to different locations on the same image.
          >
          > I would like some way to get my hands on the data corresponding to
          > the query:
          >
          > "give me all pairs of locations that are found on the same image and
          > are closer than distance d to each other"
          >
          > I am guessing the best return table structure is something like:
          > ImgFileName XPos1, YPos1, XPos2, YPos2
          > but any other workable structure would be fine.
          >
          > Any ideas how to formulate an SQL query to do this? It needs to be
          > generic SQL as I am using it across multiple platforms.
          >
          > Thanks,
          > Darryl.
          >
          >



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

          Bob Filipiak
        • John Warner
          The irony is doesn t SQL 2008 have a data type to solve this problem? John Warner ... rows ... more I have ... intended solely ... email. Any ... and ...
          Message 4 of 6 , Aug 5, 2008
          • 0 Attachment
            The irony is doesn't SQL 2008 have a data type to solve this problem?

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
            > Sent: Monday, August 04, 2008 11:11 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: RE: [SQLQueriesNoCode] SQL Query to select pairs / groups of
            rows
            >
            > I would try something like:
            >
            > SELECT
            > ImgFileName,
            > XPos,
            > YPos
            > FROM YourTable
            > WHERE abs( XPos - YPos ) <= @d_Param
            >
            > Depending upon the data structure and volume, a persisted and indexed
            > computed column [abs( XPos - YPos )] might be useful.
            >
            > Regards,
            >
            > Arnie Rowland, MVP (SQL Server)
            >
            > "I am a great believer in luck, and I find that the harder I work, the
            more I have
            > of it."
            > - Thomas Jefferson (1743-1826)
            >
            >
            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of darrylgreig
            > Sent: Monday, August 04, 2008 8:06 PM
            > To: Arnie
            > Subject: [SQLQueriesNoCode] SQL Query to select pairs / groups of rows
            >
            > Hi All,
            >
            > I am very new to SQL, and for all my reading I can't find an answer
            > to my query scenario. The context is a database of locations on
            > images, where each row consists of a single location. For simplicity
            > say the row is ImgFileName, XPos, Ypos. Multiple rows may have the
            > same ImgFileName referring to different locations on the same image.
            >
            > I would like some way to get my hands on the data corresponding to
            > the query:
            >
            > "give me all pairs of locations that are found on the same image and
            > are closer than distance d to each other"
            >
            > I am guessing the best return table structure is something like:
            > ImgFileName XPos1, YPos1, XPos2, YPos2
            > but any other workable structure would be fine.
            >
            > Any ideas how to formulate an SQL query to do this? It needs to be
            > generic SQL as I am using it across multiple platforms.
            >
            > Thanks,
            > Darryl.
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
            >
            >
            >
            > Disclaimer - August 4, 2008
            > This email and any files transmitted with it are confidential and
            intended solely
            > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
            > addressee you should not disseminate, distribute, copy or alter this
            email. Any
            > views or opinions presented in this email are solely those of the author
            and
            > might not represent those of Westwood Consulting, Inc. Warning: Although
            > Westwood Consulting, Inc has taken reasonable precautions to ensure no
            > viruses are present in this email, the company cannot accept
            responsibility for
            > any loss or damage arising from the use of this email or attachments.
            > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • darrylgreig
            Thanks for all your suggestions - actually got a very good suggestion from another quarter, for those that are interested: SELECT a.imgFname, a.centreX AS
            Message 5 of 6 , Aug 5, 2008
            • 0 Attachment
              Thanks for all your suggestions - actually got a very good suggestion
              from another quarter, for those that are interested:

              SELECT a.imgFname, a.centreX AS centreX1, a.centreY AS centreY1,
              b.centreX AS centreX2, b.centreY AS centreY2 FROM cartogra AS a,
              cartogra AS b WHERE a.imgFname=b.imgFname AND (b.centreX<>a.centreX OR
              b.centreY<> a.centreY) AND sqrt(power((b.centreX-a.centreX),2)+power
              ((b.centreY-a.centreY),2))<100

              Cheers,
              Darryl.
            • Arnie Rowland
              Yep. (SQL Server 2008, that is...) Regards, Arnie Rowland, MVP (SQL Server) I am a great believer in luck, and I find that the harder I work, the more I have
              Message 6 of 6 , Aug 5, 2008
              • 0 Attachment
                Yep. (SQL Server 2008, that is...)


                Regards,

                Arnie Rowland, MVP (SQL Server)

                "I am a great believer in luck, and I find that the harder I work, the more I have of it." 
                - Thomas Jefferson (1743-1826)


                -----Original Message-----
                From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                Sent: Tuesday, August 05, 2008 4:50 AM
                To: Arnie
                Subject: RE: [SQLQueriesNoCode] SQL Query to select pairs / groups of rows
                Importance: Low

                The irony is doesn't SQL 2008 have a data type to solve this problem?

                John Warner


                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Arnie Rowland
                > Sent: Monday, August 04, 2008 11:11 PM
                > To: SQLQueriesNoCode@yahoogroups.com
                > Subject: RE: [SQLQueriesNoCode] SQL Query to select pairs / groups of
                rows
                >
                > I would try something like:
                >
                > SELECT
                > ImgFileName,
                > XPos,
                > YPos
                > FROM YourTable
                > WHERE abs( XPos - YPos ) <= @d_Param
                >
                > Depending upon the data structure and volume, a persisted and indexed
                > computed column [abs( XPos - YPos )] might be useful.
                >
                > Regards,
                >
                > Arnie Rowland, MVP (SQL Server)
                >
                > "I am a great believer in luck, and I find that the harder I work, the
                more I have
                > of it."
                > - Thomas Jefferson (1743-1826)
                >
                >
                > -----Original Message-----
                > From: SQLQueriesNoCode@yahoogroups.com
                > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of darrylgreig
                > Sent: Monday, August 04, 2008 8:06 PM
                > To: Arnie
                > Subject: [SQLQueriesNoCode] SQL Query to select pairs / groups of rows
                >
                > Hi All,
                >
                > I am very new to SQL, and for all my reading I can't find an answer
                > to my query scenario. The context is a database of locations on
                > images, where each row consists of a single location. For simplicity
                > say the row is ImgFileName, XPos, Ypos. Multiple rows may have the
                > same ImgFileName referring to different locations on the same image.
                >
                > I would like some way to get my hands on the data corresponding to
                > the query:
                >
                > "give me all pairs of locations that are found on the same image and
                > are closer than distance d to each other"
                >
                > I am guessing the best return table structure is something like:
                > ImgFileName XPos1, YPos1, XPos2, YPos2
                > but any other workable structure would be fine.
                >
                > Any ideas how to formulate an SQL query to do this? It needs to be
                > generic SQL as I am using it across multiple platforms.
                >
                > Thanks,
                > Darryl.
                >
                >
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >
                >
                >
                >
                >
                > Disclaimer - August 4, 2008
                > This email and any files transmitted with it are confidential and
                intended solely
                > for SQLQueriesNoCode@yahoogroups.com. If you are not the named
                > addressee you should not disseminate, distribute, copy or alter this
                email. Any
                > views or opinions presented in this email are solely those of the author
                and
                > might not represent those of Westwood Consulting, Inc. Warning: Although
                > Westwood Consulting, Inc has taken reasonable precautions to ensure no
                > viruses are present in this email, the company cannot accept
                responsibility for
                > any loss or damage arising from the use of this email or attachments.
                > This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                >
                > ------------------------------------
                >
                > Yahoo! Groups Links
                >
                >
                >


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

                Yahoo! Groups Links
              Your message has been successfully submitted and would be delivered to recipients shortly.