Spatial index is not working in MS SQL Server
I was working with SQL Server 2008 Spatial Data, but I got a weird problem that the Spatial Index created for the tables doesn’t work when I query them with the view which is created based on this table. Following is the scripts I was using:
declare @Geometry1 geometry = geometry::STGeomFromText(
'POINT(937767.89433333278 -230404.864666667)', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry1.STIntersects(SHAPE)=1);',
N'@Geometry1 geometry', @Geometry1
I googled a lot and found an workaround at http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/ , but seems like this workaround just works when the queried geometry is point type, for polygons, like the script as following:
declare @Geometry2 geometry = geometry::STGeomFromText(
'POLYGON((-2079214.0399 1392052.275,-2079214.0399 -1156112.025,
1981332.1069 -1156112.025,1981332.1069 1392052.275,
-2079214.0399 1392052.275))', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry2.STIntersects(SHAPE)=1);',
N'@Geometry2 geometry', @Geometry2
The spatial still doesn’t work. Anybody knows how to deal with this situation? Seems like the Microsoft doesn’t give a good instructions about this.
Any response will be appreciated.
sql-server indexing tags gis spatial edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
asked Nov 4 '13 at 10:23
BillxuBillxu
112
add a comment |
I was working with SQL Server 2008 Spatial Data, but I got a weird problem that the Spatial Index created for the tables doesn’t work when I query them with the view which is created based on this table. Following is the scripts I was using:
declare @Geometry1 geometry = geometry::STGeomFromText(
'POINT(937767.89433333278 -230404.864666667)', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry1.STIntersects(SHAPE)=1);',
N'@Geometry1 geometry', @Geometry1
I googled a lot and found an workaround at http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/ , but seems like this workaround just works when the queried geometry is point type, for polygons, like the script as following:
declare @Geometry2 geometry = geometry::STGeomFromText(
'POLYGON((-2079214.0399 1392052.275,-2079214.0399 -1156112.025,
1981332.1069 -1156112.025,1981332.1069 1392052.275,
-2079214.0399 1392052.275))', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry2.STIntersects(SHAPE)=1);',
N'@Geometry2 geometry', @Geometry2
The spatial still doesn’t work. Anybody knows how to deal with this situation? Seems like the Microsoft doesn’t give a good instructions about this.
Any response will be appreciated.
sql-server indexing tags gis spatial edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
asked Nov 4 '13 at 10:23
BillxuBillxu
112
1
Is there a question here? I don't see one.
– Damien_The_Unbeliever
Nov 4 '13 at 10:28
Sorry, I have update the question. Thanks
– Billxu
Nov 6 '13 at 9:03
add a comment |
I was working with SQL Server 2008 Spatial Data, but I got a weird problem that the Spatial Index created for the tables doesn’t work when I query them with the view which is created based on this table. Following is the scripts I was using:
declare @Geometry1 geometry = geometry::STGeomFromText(
'POINT(937767.89433333278 -230404.864666667)', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry1.STIntersects(SHAPE)=1);',
N'@Geometry1 geometry', @Geometry1
I googled a lot and found an workaround at http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/ , but seems like this workaround just works when the queried geometry is point type, for polygons, like the script as following:
declare @Geometry2 geometry = geometry::STGeomFromText(
'POLYGON((-2079214.0399 1392052.275,-2079214.0399 -1156112.025,
1981332.1069 -1156112.025,1981332.1069 1392052.275,
-2079214.0399 1392052.275))', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry2.STIntersects(SHAPE)=1);',
N'@Geometry2 geometry', @Geometry2
The spatial still doesn’t work. Anybody knows how to deal with this situation? Seems like the Microsoft doesn’t give a good instructions about this.
Any response will be appreciated.
sql-server indexing tags gis spatial edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
asked Nov 4 '13 at 10:23
BillxuBillxu
112
I was working with SQL Server 2008 Spatial Data, but I got a weird problem that the Spatial Index created for the tables doesn’t work when I query them with the view which is created based on this table. Following is the scripts I was using:
declare @Geometry1 geometry = geometry::STGeomFromText(
'POINT(937767.89433333278 -230404.864666667)', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry1.STIntersects(SHAPE)=1);',
N'@Geometry1 geometry', @Geometry1
I googled a lot and found an workaround at http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/ , but seems like this workaround just works when the queried geometry is point type, for polygons, like the script as following:
declare @Geometry2 geometry = geometry::STGeomFromText(
'POLYGON((-2079214.0399 1392052.275,-2079214.0399 -1156112.025,
1981332.1069 -1156112.025,1981332.1069 1392052.275,
-2079214.0399 1392052.275))', 102003)
exec sp_executesql
N'SELECT shape FROM view WHERE (@Geometry2.STIntersects(SHAPE)=1);',
N'@Geometry2 geometry', @Geometry2
The spatial still doesn’t work. Anybody knows how to deal with this situation? Seems like the Microsoft doesn’t give a good instructions about this.
Any response will be appreciated.
sql-server indexing tags gis spatial sql-server indexing tags gis spatial edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
asked Nov 4 '13 at 10:23
BillxuBillxu
112
edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
asked Nov 4 '13 at 10:23
BillxuBillxu
112
edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
edited Nov 15 '18 at 1:55
Rahul Neekhra
6001627
6001627
asked Nov 4 '13 at 10:23
BillxuBillxu
112
asked Nov 4 '13 at 10:23
BillxuBillxu
112
asked Nov 4 '13 at 10:23
BillxuBillxu
112
112
1
Is there a question here? I don't see one.
– Damien_The_Unbeliever
Nov 4 '13 at 10:28
Sorry, I have update the question. Thanks
– Billxu
Nov 6 '13 at 9:03
add a comment |
1
Is there a question here? I don't see one.
– Damien_The_Unbeliever
Nov 4 '13 at 10:28
Sorry, I have update the question. Thanks
– Billxu
Nov 6 '13 at 9:03
1
1
Is there a question here? I don't see one.
– Damien_The_Unbeliever
Nov 4 '13 at 10:28
Is there a question here? I don't see one.
– Damien_The_Unbeliever
Nov 4 '13 at 10:28
Sorry, I have update the question. Thanks
– Billxu
Nov 6 '13 at 9:03
Sorry, I have update the question. Thanks
– Billxu
Nov 6 '13 at 9:03
add a comment |
1 Answer
1
active
oldest
votes
Please use the diagnostics stored procedure sp_help_spatial_geography_index_xml and look at its' output.
This is the primary stored procedure you can use to get an idea of how your geospatial index could be used.
Given that your query is so simple, the @query_sample argument to sp_help_spatial_geography_index_xml can be the same as your @geometry parameter. This will tell you some fairly useful information, such as whether the spatial index needs a full scan to evaluate the question.
Another thing to point out: The dynamic management object view sys.dm_db_missing_index_details explicitly states in the documentation that it does not support spatial indexes, ergo SQL Server does not instrument a lot of intelligence around these indexes.
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f19765688%2fspatial-index-is-not-working-in-ms-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Please use the diagnostics stored procedure sp_help_spatial_geography_index_xml and look at its' output.
This is the primary stored procedure you can use to get an idea of how your geospatial index could be used.
Given that your query is so simple, the @query_sample argument to sp_help_spatial_geography_index_xml can be the same as your @geometry parameter. This will tell you some fairly useful information, such as whether the spatial index needs a full scan to evaluate the question.
Another thing to point out: The dynamic management object view sys.dm_db_missing_index_details explicitly states in the documentation that it does not support spatial indexes, ergo SQL Server does not instrument a lot of intelligence around these indexes.
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
|
show 3 more comments
Please use the diagnostics stored procedure sp_help_spatial_geography_index_xml and look at its' output.
This is the primary stored procedure you can use to get an idea of how your geospatial index could be used.
Given that your query is so simple, the @query_sample argument to sp_help_spatial_geography_index_xml can be the same as your @geometry parameter. This will tell you some fairly useful information, such as whether the spatial index needs a full scan to evaluate the question.
Another thing to point out: The dynamic management object view sys.dm_db_missing_index_details explicitly states in the documentation that it does not support spatial indexes, ergo SQL Server does not instrument a lot of intelligence around these indexes.
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
|
show 3 more comments
Please use the diagnostics stored procedure sp_help_spatial_geography_index_xml and look at its' output.
This is the primary stored procedure you can use to get an idea of how your geospatial index could be used.
Given that your query is so simple, the @query_sample argument to sp_help_spatial_geography_index_xml can be the same as your @geometry parameter. This will tell you some fairly useful information, such as whether the spatial index needs a full scan to evaluate the question.
Another thing to point out: The dynamic management object view sys.dm_db_missing_index_details explicitly states in the documentation that it does not support spatial indexes, ergo SQL Server does not instrument a lot of intelligence around these indexes.
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
Please use the diagnostics stored procedure sp_help_spatial_geography_index_xml and look at its' output.
This is the primary stored procedure you can use to get an idea of how your geospatial index could be used.
Given that your query is so simple, the @query_sample argument to sp_help_spatial_geography_index_xml can be the same as your @geometry parameter. This will tell you some fairly useful information, such as whether the spatial index needs a full scan to evaluate the question.
Another thing to point out: The dynamic management object view sys.dm_db_missing_index_details explicitly states in the documentation that it does not support spatial indexes, ergo SQL Server does not instrument a lot of intelligence around these indexes.
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
edited Nov 21 '18 at 18:09
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
answered Nov 14 '18 at 17:42
John ZabroskiJohn Zabroski
1,0691132
1,0691132
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
|
show 3 more comments
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. Please write a comment instead.
– Zoe
Nov 14 '18 at 19:57
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
– Eric Brown
Nov 14 '18 at 20:20
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
@EricBrown The question was "Anybody knows how to deal with this situation?" This is one of the best ways to deal with the situation. There are truly limited ways to debug spatial queries, as the cost-based optimizer does not surface anything more than an opaque table-valued function called PlanarTesselation in the query plan output. However, in this case, the user is reporting they are not even seeing the index being used. Given it's the geospatial equivalent of bookmark lookup (on a single point), the BEST the user can do is to ask SQL Server for the diagnostics on the index for the sample
– John Zabroski
Nov 14 '18 at 20:42
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
I've updated the answer to make it more obvious to people who don't do SQL regularly why this is the correct answer.
– John Zabroski
Nov 14 '18 at 21:00
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
@EricBrown I don't understand why this has gotten runover by moderators. Can you please explain?
– John Zabroski
Nov 15 '18 at 21:33
|
show 3 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f19765688%2fspatial-index-is-not-working-in-ms-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Is there a question here? I don't see one.
– Damien_The_Unbeliever
Nov 4 '13 at 10:28
Sorry, I have update the question. Thanks
– Billxu
Nov 6 '13 at 9:03