Sfrgttk

Potentiometer like component

Why would a jet engine that runs at temps excess of 2000°C burn when it crashes?

What exactly is the purpose of connection links straped between the rocket and the launch pad

Is having access to past exams cheating and, if yes, could it be proven just by a good grade?

How to make readers know that my work has used a hidden constraint?

Are there situations where a child is permitted to refer to their parent by their first name?

Is this animal really missing?

How is the Swiss post e-voting system supposed to work, and how was it wrong?

Giving Plot options defined outside of the Plot expression

Why doesn't the EU now just force the UK to choose between referendum and no-deal?

US to Europe trip with Canada layover- is 52 minutes enough?

When were linguistics departments first established

Unreachable code, but reachable with exception

What is the probability somebody's birthday is the day before mine?

Silly Sally's Movie

Why do Australian milk farmers need to protest supermarkets' milk price?

Who is our nearest neighbor

Counter-example to the existence of left Bousfield localization of combinatorial model category

Do I need to leave some extra space available on the disk which my database log files reside, for log backup operations to successfully occur?

Is it true that real estate prices mainly go up?

Does the Bracer of Flying Daggers benefit from the Dueling Fighting style?

Deleting missing values from a dataset

How do anti-virus programs start at Windows boot?

Make a transparent 448*448 image

MSSQL Spatial performance (point in polygon)

UDF performance suddenly degradedFind the neighbouring polygons of a given polygon in a MySQL spatial table in ver < 5.6.1sql server spatial index performanceMySQL Syntax - Searching points within polygons using GeomFromTextSQL Server spatial filter with additional conditions performance issueAccess linked to SQL with SSMA slow performanceImprove Parameterized Query PerformanceShould I join all of my polygons to make a single polygon for point-in-polygon search?Spatial query to get enclosing radius/polygon of result setMBRCovers return true even though point outside the polygon

0

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

share

asked 4 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 | 

0

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

share

asked 4 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

share

asked 4 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.

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;

SELECT [Shape] from [GISDB].[GISUSER].[POINTS]

WHERE [OBJECTID] = '100000';



    Returns: 0xBB100000010C300A689B1B4B3BC0400E645B061F6340

share

asked 4 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.

share

asked 4 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 4 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 4 mins ago

anakaineanakaine

1