I have an existing database solution that is not performing very well. I am searching on multi-dimensional data with a traditional table structure using floats, etc. The database contains ~ 1-2M rows.
After some searching I came across the SQL Server spatial data types where you can define a geometry based on Point or MultiPoint where you can search with a distance. I think this might be a solution but I need some help to get started in the right direction.
My current table structure is as follows where each row contains 2 (eventually more) XYZ values:
| ID | X1 | Y1 | Z1 | X2 | Y2 | Z2 | | 1 | 1.1 | 2.2 | 5.1 | 1.2 | 2.1 | 4.1 | | 2 | 3.2 | 5.1 | 4.1 | 3.2 | 3.1 | 3.1 | | 3 | 4.1 | 2.3 | 6.3 | 4.2 | 4.1 | 2.1 | | 4 | 2.4 | 3.5 | 2.1 | 3.2 | 2.1 | 4.1 |
Im performing search with a tolerance for each X, Y and Z value.
So for example:
(pseudocode) declare targetXYZ1 = (4.1, 2.2, 3.1); declare targetXYZ2 = (2.8, 2.2, 4.2); declare toleranceXYZ = (2,2,2) Select ID from MyXYZTable tb Where Math.Abs(tb.X1 - targetXYZ1.X1) < toleranceXYZ.X AND Math.Abs(tb.Y1 - targetXYZ1.Y1) < toleranceXYZ.Y AND Math.Abs(tb.Z1 - targetXYZ1.Z1) < toleranceXYZ.Z AND Math.Abs(tb.X2 - targetXYZ1.X2) < toleranceXYZ.X AND Math.Abs(tb.Y2 - targetXYZ1.Y2) < toleranceXYZ.Y AND Math.Abs(tb.Z2 - targetXYZ1.Z2) < toleranceXYZ.Z
After some analysis I would probably need to make a table with 1 or 2 geometry columns for xyz1 and xyz2 with geometry::Point(0, 0, 0)
- Is there a solution possible with SQL Server spatial types?
- Do I need to use nearest neighbor search or can I use STDistance
- Do I need to setup a spatial index for the table?
Any advice or tips and tricks are very welcome!