EF5, SQL Server, Longitude and Latitude

I found that the best type to store lat and long in SQL Server is decimal (9,6) (ref. What datatype to use when storing latitude and longitude data in SQL databases?) and so I did

AddColumn("dbo.Table", "Latitude", c => c.Decimal(nullable: false, precision: 9, scale: 6));
AddColumn("dbo.Table", "Longitude", c => c.Decimal(nullable: false, precision: 9, scale: 6));

SQL seems ok, everything is working, BUT when I insert / update a value, ie

lat = 44.5912853

it is saved like this:

44.590000

I checked the flow, and just before the update, my entity contains the correct value, so I don't think is related to my code, but to some round that EF / SQL does. Do you have any idea to avoid this?

UPDATE

update [dbo].[Breweries]
set [RankId] = @0, 
[Name] = @1, 
[Foundation] = null, 
[DirectSale] = @2, 
[OnlineSale] = @3, 
[StreetAddress] = @4, 
[StreetAddress1] = null, 
[ZIP] = @5, 
[City] = @6, 
[Province] = @7, 
[CountryCode] = @8, 
[Latitude] = @9, 
[Longitude] = @10, 
[PIVA] = null, 
[CodFiscale] = null
where ([BreweryId] = @11)

POCO entity

[Table("Breweries")]
public class Brewery : ABrewery 
{
  ....
  public decimal Latitude { get; set; }
  public decimal Longitude { get; set; }
}

SQL Profiler

exec sp_executesql N'update [dbo].[Breweries]
set [RankId] = @0, [Name] = @1, [Foundation] = null, [DirectSale] = @2, [OnlineSale] = @3, [StreetAddress] = @4, [StreetAddress1] = null, [ZIP] = @5, [City] = @6, [Province] = @7, [CountryCode] = @8, [Latitude] = @9, [Longitude] = @10, [PIVA] = null, [CodFiscale] = null
where ([BreweryId] = @11)
',N'@0 int,@1 nvarchar(128),@2 bit,@3 bit,@4 nvarchar(256),@5 varchar(16),@6 nvarchar(64),@7 nvarchar(64),@8 nvarchar(128),@9 decimal(18,2),@10 decimal(18,2),@11 int',@0=2,@1=N'Davide',@2=0,@3=0,@4=N'Via Moscardini, 24',@5='zip',@6=N'city',@7=N'province',@8=N'ITA',

@9=44.59,@10=11.05,@11=2

Thanks

---

Apparently this guy had the exact same problem and solved it thus:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Activity>().Property(a => a.Latitude).HasPrecision(18, 9);
    modelBuilder.Entity<Activity>().Property(a => a.Longitude).HasPrecision(18, 9);
}

Although you may want to look at using the spatial data types (particularly geography ) in SQL Server 2008 and later.

---

您可以使用DbGeography类型来存储经度和纬度。

using System.Data.Entity.Spatial;

public class Test
{
    public DbGeography Location { get; set; }
}

---

Two things:

I just purchased a Zip code database and it stores all latitude and longitude values as decimal(12,6) data type. I don't think this is going to radically change your results though.

I would check the exact SQL being sent to your SQL Server. You can then check to see where the rounding is occurring. You can check the SQL being sent by grabbing the output from EF or using SQL Profiler. My guess is that it's occuring in your C# code.

Also, it might be useful to see your table schema and your domain entity.

链接地址: http://www.djcxy.com/p/93910.html

上一篇: C＃中经度/纬度值的双精度或小数点

下一篇: EF5，SQL Server，经度和纬度