none
Recommended way of creating an index on geometry column?? RRS feed

  • Question

  • Hi Team,

    What is the recommended way of creating an index on geometry column?? How to determine the X and the Y axis of the bounding box? 

    FYI, the table is already populated and I have create to an index on it.



    Regards, Ashif Shaikh



    • Edited by Asif_DBA Tuesday, September 10, 2019 11:47 AM
    Monday, September 9, 2019 1:27 PM

All replies

  • Wow I have a lecture level 400 on this topic...
    I mean about special data types index. I think you can find the recording somewhere online from one of the events I spoke about it😃
    It is very interesting how the index works behind the scenes. Awesome logic and really provide great performance. In fact I have several solutions/tricks which are not related to special data which I solve using special data types (like this) since it can provide great performance.

    Good day Asif,

    As you might understand from my opening, that there is a SPATIAL INDEX for special data types.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-spatial-index-transact-sql?view=sql-server-2017

    Update: This index works totally different from regular tree index. Here I found a better document which gives some insights on how it is working under the scenes:

    https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-indexes-overview?view=sql-server-2017


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, September 9, 2019 1:38 PM
    Moderator
  • Hi Ashif,

    This post also tells how to create spacial indexes with some examples. 

    I think it could give you some ideas as well. 

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-spatial-indexes/

    Regards,

    Sabrina 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 10, 2019 5:41 AM
  • Thanks got it,

    Any idea on how to define the bounding box parameters. I mean the X and Y axis values, from where do I get it??


    Regards, Ashif Shaikh

    Tuesday, September 10, 2019 9:24 AM
  • Hi,

    In order to define the BOUNDING_BOX you first need to know what are the boundaries which you need, and for this you need to be familiar with your data (and system). You should make sure that the BOUNDING_BOX cover all the current data and future data in the table.

    If you can describe your data and what it is used for then maybe we can think about what the BOUNDING_BOX parameters fits your needs. Without this information we cannot guess what is your data and there what is the BOUNDING_BOX which is needed.

    In the meantime the following document regarding to "how" please check the first link I gave you which include the parameter full explanation on configure the BOUNDING_BOX and there are several samples there at the bottom like this one.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]
    Tuesday, September 10, 2019 11:41 AM
    Moderator
  • I want to cover all of the GCC countries with their specific points. 

    We have created a column delivery area with geometry data type and this basically would be used for delivery purpose.

    Regards, Ashif Shaikh

    Tuesday, September 10, 2019 11:50 AM
  • I want to cover all of the GCC countries with their specific points. 

    We have created a column delivery area with geometry data type and this basically would be used for delivery purpose.

    Regards, Ashif Shaikh

    Hi,

    I have no idea what is "GCC". I tried to search google and I got: Gulf Cooperation Council - political and economic alliance of six Middle Eastern countries—Saudi Arabia, Kuwait, the United Arab Emirates, Qatar, Bahrain, and Oman.... all the countries which I cannot visit😢, Anyhow this is totally not relevant🙂

    >> We have created a column delivery area with geometry data type and this basically would be used for delivery purpose.

    I assume that you speak about area on Earth and yet you speak about using geometry and boundaries - this make no sense.

    According to your description as I understand it (if we can say that single sentence can be considered as description) you should not use geometry data type but geography data type.

    SQL Server supports the Open Geospatial Consortium (OGC) methods on geography instances using the geography data type.

    geography data type already based on (built-in) the real shape of the Earth which is not any exactly fit to any geometry shape (it is like ellipsoid with distortions). In Geography data type the ellipsoidal coordinates are given in degrees of latitude and longitude.

    Geography type has an additional instance type named FullGlobe.

    boundaries should be according to the coordinates of the area of the countries you need to work with, or simply use the full globe as your "boundaries"

    Check the following document regarding geography:
    https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography?view=sql-server-2017


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, September 10, 2019 2:57 PM
    Moderator