Does SQL Server Data Need to be Spatial

Question

In reference to my previous question.

I am looking for the benefits of telling SQL Server your data is spatial. For instance, I have a duplicate of that table generated by the Shape2SQL tool (see previous question). The major difference is it is not a spatial table, but I have a field where I am storing the well-known text in lat/long (e.g. POINT(-82.2990401 38.9186892)). I found I was able to run a similar query that worked to get me the data I was requesting.

declare @poly geometry = 'POLYGON((-82.8113 39.690, -81.145 39.725, -80.717 38.74, -82.813 38.614, -82.8113 39.690))'

SELECT * FROM [TABLE]
WHERE @poly.STIntersects(WKT) <> 0;

I am looking to see the real benefits to telling SQL Server that you're dealing with spatial data. The only two things I can think of off the top of my head are spatial indexing and being able to visualize your data in the query results window within SQL Server Management Studio.

I am looking for the pros of SQL Server serving spatial data. This is for a personal website, and I have no desire to invest in running a spatial server instance like ArcGIS Server or GeoServer. I am curious if I will be better off leaving things in a table and just using WKT in my queries or if I should really be using a geom/geog object.

Answer 1

spatial indexing and being able to visualize your data

You're absolutely right on those two counts.

I would make one more point; building the geometry object is computationally expensive, especially on large or complex data sets. Performance and scaling will suffer, because the database is having to build the geometry on-the-fly.

Your query will perform better if the geometry object was already created and stored in a field.

Also, I think your query should look more like this:

SELECT * FROM [TABLE]
WHERE @poly.STIntersects(geometry::STGeomFromText(WKT)) <> 0;