how to include a spatial index in c# EF

Stephen Cameron 0

This query is not using the spatial index: (@__point_0 sys.geometry)SELECT TOP(1) [t].[Id], [t].[Bus_StopsId], [t].[Point]
FROM [TripRef_Stops] AS [t]
WHERE [t].[Point].STBuffer(5.0000000000000002E-05).STContains(@__point_0) = CAST(1 AS bit) - it is transformed from c# in EF

Viorel 116.7K Reputation points

2024-08-21T11:12:58.9466667+00:00

What did you write in C#?
Stephen Cameron 0 Reputation points

2024-08-21T17:26:45.0233333+00:00
public async Task<bool> IsBusStop(Point point, bool trackChanges)

{

var findrecord = await FindByCondition(c => c.Point.Buffer(0.00005).Contains(point), trackChanges).FirstOrDefaultAsync();

.......

}

The query takes more than 30 seconds without using an index - the standard BOUNDINGBOX definition and around 70,000 records. It just is not very effective. I wonder about just storing an area rather than a point and work with one operation c.Area.Contains(point), rather than c.Point.Buffer(x).Contains(point) instead?
Viorel 116.7K Reputation points

2024-08-21T17:44:16.31+00:00

Maybe you can also consider the STDistance function, which supports spatial indices in case of "<" operation (according to documentation). Check if there is an equivalent function in Entity Framework. The Area.Contains should work too for geometry type, but not geography.

1 answer

Stephen Cameron 0 Reputation points

2024-08-22T09:23:09.54+00:00

Thanks - the EF Distance() worked. I don't use Geography for that matching - so all good. Obviously multiple operators like Buffer().Contains() obviously break the index process.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

how to include a spatial index in c# EF

1 answer

Your answer