Performance Considerations for FE and BE

The performance of the map view and the data view is limited by a few factors. This concerns mainly the the performance of the database and the rendering in the frontend. The Rest-API does not limit the performance.

Frontend

The frontend queries for every pinned map the general content that is supposed to be shown. Performance issues arise here due to the rendering and the UpdateData Mechanic. 300 Polygons is already quite much and may already limit the usability.

General Strategy: Limiting/simplifying Objects

The amount of objects is a limiting factor and we can reduce them with a few ideas:

• Limit the number of objects from BE: Hardcoded Limit
• Limit the size of the viewport: Bad Usability but may be needed - maybe only request for center-rectangle
• Limit the zoom level that is shown: Only show stuff when you are close enough -> limits the number of objects but less relevant on a huge screen
• Transform Polygons to markers when looking from far above: Improves rendering performance by reducing the effort because static markers are easier than polygons. And polygons are not really needed when you look from so far above that you can't see it anymore

Backend

The BE has to search through the database in one typical way: Get All Results from a specific Dataset that intersects a given rectangle. This is as default not usable with query times more than 10 min in the dockerized application on a normal desktop PC. As such the question is how to improve query performance:

1. Using Geometry instead of Geography Datatype: Since we do have a geospatial context it would be intuitive to use the geography datatype for maximum accuracy. But this model uses an ellipsoid for calculating distances and similar things which takes a lot of time! Also, the index creation (a performance-boosting B-Tree) can't be given a bounding box which worsens its performance. Also in general operations like "give me the 5 closest" are way less performant.
2. Creating an index: Creating an index on Geography already improves performance to give results (always for the closest zoom level) that at least return in dimensions of a few minutes. Creating an index on Geography has a huge problem that it can't be given a bounding box! It always takes the whole world as its area of interest. This means that the first few levels of the hierarchy are wasted for just navigating to Germany. And if the dataset concerns only a single Landkreis its improvement is negligible compared to what it could do with a bounding rectangle. So you should create the index on Geometry with a bounding box. Perfect parameters still have to be evaluated but results for 5 closest objects took around 10 seconds for different variations (GEOMETRY_GRID vs GEOMETRY_AUTO_GRID.). The following SQL can be used to get the surrounding rectangle:

USE BIEDB;
SELECT
 geometry::EnvelopeAggregate(Location).STPointN(1).STX AS MinX,
 geometry::EnvelopeAggregate(Location).STPointN(1).STY AS MinY,
 geometry::EnvelopeAggregate(Location).STPointN(3).STX AS MaxX,
 geometry::EnvelopeAggregate(Location).STPointN(3).STY AS MaxY
FROM dbo.Hausumringe_mittelfranken;

With such an index creation:

CREATE SPATIAL INDEX SI_Index_auto_grid
ON dbo.Hausumringe_mittelfranken_random(Location)
USING GEOMETRY_AUTO_GRID
WITH (
     BOUNDING_BOX = (
	         XMIN = 48.8718628962929,  -- MaxLat
		 YMIN = 10.0834777514127, -- MinLat
		 XMAX = 49.7790341781705, -- MaxLong
		 YMAX = 11.5931637088103  -- MinLong
    )
);

3. Working on a randomly sorted database: The index tree tries to create a balanced tree. For improving this balancing act it is recommended to work on a randomly sorted database so the tree is as balanced as possible to improve the query time