1

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.

2

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;
  • So, whenever I am running a query where I am taking a WKT and doing something with it, is SQL Server converting that in the backend to a geometry object? – Branco Aug 14 '15 at 18:11
  • Correct, declare @poly geometry is building the geometry and storing it temporarily. – Mintx Aug 14 '15 at 18:13
  • Ok, but the basic idea I was thinking was that declare @poly geometry part would take an polygon that would be created elsewhere and finding which WKT's from my table would intersect or whatever other operation. It isn't tied directly to the values stored within the table. – Branco Aug 14 '15 at 18:14
  • I've added a bit to my answer, check out this link to see if it helps you: msdn.microsoft.com/en-us/library/bb933899.aspx – Mintx Aug 14 '15 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.