MSSQL Spatial performance (point in polygon)
Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.
The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText - and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.
Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).
The code as it is now:
DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377 -27.385831))', 4326));
SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;
.
The data in the shape field is stored like such (Well Known Binary, I'm guessing)
SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';
Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340
sql-server query-performance spatial
asked 17 mins ago
anakaineanakaine
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.
The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText - and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.
Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).
The code as it is now:
DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377 -27.385831))', 4326));
SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;
.
The data in the shape field is stored like such (Well Known Binary, I'm guessing)
SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';
Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340
sql-server query-performance spatial
asked 17 mins ago
anakaineanakaine
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.
The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText - and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.
Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).
The code as it is now:
DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377 -27.385831))', 4326));
SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;
.
The data in the shape field is stored like such (Well Known Binary, I'm guessing)
SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';
Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340
sql-server query-performance spatial
asked 17 mins ago
anakaineanakaine
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Discolsure: This is a cross post from GIS, however the group over there isn't necessarily focused
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a 10,000,000 point shape can be loaded in to view in ArcMap in ~5 seconds.
The question is: Can the SQL query be structured to perform any better? My polygon is being given as points, and being converted to geography. My data has a Shape column that according to MSSQL is stored as geography. In order to get the query to work I'm asking every field to be converted via STAsText - and I think that's a performance hangup. For the life of me I cannot seem to get the query to work any other way. If I remove this, the query fails to run.
Ultimately a similar query will be run on a table with 20,000,00 points, so the performance as it is currently is not really acceptable (That would be 833 seconds).
The code as it is now:
DECLARE @geog1 geography;
SET @geog1 = (geography::STPolyFromText('POLYGON((153.013377 -27.385831, 153.018303 -27.399108, 153.030438 -27.395896, 153.028725 -27.386901, 153.013377 -27.385831))', 4326));
SELECT * FROM [GISDB].[GISUSER].[POINTS]
WHERE @geog1.STIntersects([POINTS].[Shape].STAsText()) = 1;
.
The data in the shape field is stored like such (Well Known Binary, I'm guessing)
SELECT [Shape] from [GISDB].[GISUSER].[POINTS]
WHERE [OBJECTID] = '100000';
Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340
sql-server query-performance spatial
sql-server query-performance spatial
asked 17 mins ago
anakaineanakaine
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 17 mins ago
anakaineanakaine
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 17 mins ago
anakaineanakaine
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 17 mins ago
anakaineanakaine
1
asked 17 mins ago
anakaineanakaine
1
1
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
anakaine is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
anakaine is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f232077%2fmssql-spatial-performance-point-in-polygon%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
anakaine is a new contributor. Be nice, and check out our Code of Conduct.
anakaine is a new contributor. Be nice, and check out our Code of Conduct.
anakaine is a new contributor. Be nice, and check out our Code of Conduct.
anakaine is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f232077%2fmssql-spatial-performance-point-in-polygon%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
