I have just finished a project were I made a lot of use of the SQL Spatial to do some processing and loading into the database. Here are some of lesson I learnt:
- The first method that needs to call after instantiating a SQLGeographybuilder object is: SetSrid() then the BeginGeography(), BeginPoint().
- Before using sending a SQLGeometry object to the SQL Server use the IsVaild() function to ensure that Geometry object is valid.
I create a console application which loaded line, which was using OS coordinates system, data from a flat file. Some of the line data within the file wasn’t as correct as I was led to believe. The application create the SQLGeometry object .NET but the Sql Server then rasied the error when it received and try to save it into the table.
- The STPointN() function is 1 base which is stated in the Books On-Line
- If your query needs to select some of the derive data from the functions you can help performance by using persisted compute columns on the base table.
I had to create a view which needed the latitude and longitude of the starting and end points of the geom line also including the distance. The view would take about 20 seconds to finish executing. Once I created some persisted compute columns on the base table the view would finish executing under 3 seconds
- You can create more than one spatial index for a spatial column.
- Trying to join tables using the spatial functions isn’t great for performance.