0

THE NEED: I am looking for a SQL statement to convert content of SQL Geography field of a table to SQL Geometry field type, new table field I am going to add to existent table. Or a better solution, hence the post.

REASON: I am looking to speed up lookup of locations near place inserted by a user. Locations are currently stored in a table that has Lat, Long, and SQLGeography field type, Spatial index is based upon. Due to calculations with Geometry type being less resource intensive than ones with Geography type, I could take the loss of accuracy in favor of speed of execution. My current calculations are restricted to US and I don't see crossing international timeline or poles (maybe in a future?...)

ADDITIONAL CONSIDERATIONS:

  1. In my case the lookup is not just distance, but WITHIN certain area (neighborhood or city).
  2. I have tried to create a computed column, but cannot make it persistent in order to index. I heard that this is possible in 2012, but I am working with 2008 R2.

ENVIRONMENT: VS 2012, ASP.NET 4.0, Entity Framework 5 (doesn't map new geo fields properly into C#, but it is Ok as they are used just on SQL side anyway).

QUESTIONS:

  1. Does it mean that my Spatial Index (based on Geodata field of Location table) is not going to be used because I am using STIntersects and not STDistance?
  2. Should I rework somehow from "STIntersects" to using "STDistance" (I wouldn't know the distance each time as it would vary based on the area size, could be neighborhood or city for example)

GOOD ARTICLES I LOOKED AT:

  1. http://msdn.microsoft.com/en-us/library/ff929109.aspx
  2. http://workshops.opengeo.org/postgis-intro/geography.html

EXTRACTS FROM SQL QUERY:

...
SELECT @bounds = 'POLYGON(('...'))';
SELECT @location = geography::Parse(@bounds);
...
SELECT p.ID
FROM Property p
INNER JOIN Location l WITH(INDEX(SPATIAL_Location)) ON p.LocationID = l.ID
WHERE 
...
AND (l.Geodata.STIntersects(@location) = 1
AND l.Geodata.STDifference(@location).STIsEmpty() = 1)
...
ORDER BY
...
l.Geodata.STDistance(@location.EnvelopeCenter());
san_sani4
  • 1
  • 2
  • Just a small correction: Entity Framework (5+) does supports Geography and Geometry fields. – psousa Jul 26 '13 at 20:24
  • Thank you @psousa, my case is that it is EF5 and ASP.NET 4.0, incompatible for using spatial fields. In order to map properly has to be one of the following two pairs: EF5 & ASP.NET 4.5 -OR- ASP.NET 4.0 & EF6. And this is not my current case. – san_sani4 Jul 29 '13 at 13:05
  • Ah, you're right. I've actually blogged some time ago about that :) – psousa Jul 29 '13 at 15:09

1 Answers1

0

Converting from geography to geometry is answered here: Convert geography to geometry SQL Server 2008R2 Essentially, convert to text then back to geometry.

It's certainly faster to compute distance between two points rather than an intersect (generally, except when the intersect is trivial e.g involves lines or points only), but in your code you are allowing the user to enter what amounts to a polygon, so working out whether one of your property-Locations (lat long) is within a distance to a polygon doesn't really make sense, unless perhaps you take the centroid of the polygon first.

It is going to use the spatial index for filtering down to a valid scan range of lat-longs, but most of the processing is in first finding the boundary area of the user-supplied polygon. The DB Engine has to do that part first before being able to look up candidate points. Distance is fairly similar but takes some execution shortcuts because the area to check if the location falls into is effectively a circle.

Community
  • 1
  • 1
Davos
  • 5,066
  • 42
  • 66