0

I am trying to use sys.geography type in SQL Server 2008 R2. I want to use this geograpy type for location based queries. For example, I pass in a location (as longitude and latitude variables) to a stored procedure and return any records nearby.

The problem is that entity framework doesn't support the geography type so I cannot directly set this value from c#.

I have gotten around this issue by creating LocationLatitude and LocationLongitude columns in the same table. I work with the LocationLatitude and LocationLongitude columns in entity framework and work with the geography type "Location" column in the database stored procedure.

As far as I can tell there are three ways to derive a geography "Location" column from those two fields.

  1. Make the "Location" a computed column
  2. Create a view which returns the contents of the table + the computed location
  3. Create a data trigger on the table. This calculates the geography value and populates the Location column every time the LocationLongitude or LocationLatitude columns are updated.

I am wondering which would be better in terms of performance. I assume the #1 one would be the worst so I think it's a toss up between #2 and #3.

I am currently using #3 (the data trigger) but I know that it is generally best to avoid data triggers. This means that #2 (the view) would be the best from that perspective but... I am worried that using a #2 might be an incredibly dumb thing to do, for some reason. And Stack Overflow is the best place to check for that!

Soo... should I be using #1, #2, #3 or some other method for this?

John
  • 1,286
  • 2
  • 14
  • 22
  • I'd challenge your assumption that #1 would be "worst" - what are you thinking of that would make #1 in any way different from #2? – Damien_The_Unbeliever Aug 09 '13 at 12:12
  • 1
    I think EF5 supports geography data types. Is that an option? – podiluska Aug 09 '13 at 12:39
  • @Damien_The_Unbeliever I figure that having to recalculate the geography every time you query the table would be bad. By having a separate view you will only be calling it within the stored procedure – John Aug 09 '13 at 15:46
  • @podiluska Hmm, possibly? Means I'm going to have to migrate to .NET 4.5 and Visual Studio 2012 though. Maybe worth doing anyway. – John Aug 09 '13 at 15:46
  • @podiluska Could you move your comment into an answer so I can accept it? – John Aug 12 '13 at 15:56

1 Answers1

1

Entity Framework 5 supports geography data types.

podiluska
  • 50,950
  • 7
  • 98
  • 104