0

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
4
  • 1
    Features per seconds is a throughput capability of the DBMS, It is not reasonable to expect 20m features to draw in under 10 minutes. If, however, you generate a fishnet and group the features in the first overlapping polygon, you could render 20m multipoints (in 10k features) in subsecond timing.
    – Vince
    Mar 13, 2019 at 23:12
  • I'm struggling a little to visualise how that goes together, to be honest.A fishnet in this instance would cover a very large state, and need to be very fine in resolution, potentially resulting in tens to hundreds of millions of records in the fishnet itself. In this example would I be prepopulating the fishnet with the group counts, and then interrogating the first overlapping fishnet polygon with my own polygon at runtime? . .
    – anakaine
    Mar 14, 2019 at 0:09
  • I guess the other thing to question is that the view in ArcMap appears to be selecting only those points within the view to display, and does it very quickly (within seconds). Surely that is also drawing spatially from the DB.
    – anakaine
    Mar 14, 2019 at 0:18
  • 1
    No, a coarse fishnet -- 20x20 = 400 features w/ average 50k vertices. Once you've zoomed in sufficiently, then the scale-dependent point clusters turn off and the individual points turn on. Be sure to optimize the physical feature order to spatial clusters (e.g., fishnet ID). With proper organization, rendering should be easily subsecond. The STAsText part is really expensive (for no benefit)
    – Vince
    Mar 14, 2019 at 0:38

1 Answer 1

0

The answer in this instance was related to the SRID. What was actually happening was the DB was attempting transforms on the fly.

Contrary to the answer that Vince gave I was able to correct the SRID and perform the query on the table with 20,000,000 rows in less than 1 second.

The SRID of the input polygon needed to match the SRID of the stored object (Which I though it did, but it didn't.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.