Calculate distance based on latitude/longitude
Joined: 18-Apr-2006
Joined: 18-Apr-2006
Joined: 18-Apr-2006
Joined: 18-Apr-2006
Joined: 18-Apr-2006
Joined: 18-Apr-2006
Joined: 18-Apr-2006
reproduced. It's some kind of crash where an expression is an operand of the dbfunction call but it is by itself null... very odd. Looking into it.
(though I can see why, sin, cos, atan and sqrt don't have a db equivalent mapped by default).
This is the tree right before everything is wrapped up:
Entity(CreativeAddressEntity).Select(Projection(address => new <>f__AnonymousType0`2(address = [507], lat1 = (([508] * 3,14159265358979) / 180)))).Select(Projection(<>h__TransparentIdentifier0 => new <>f__AnonymousType1`2(<>h__TransparentIdentifier0 = [531], lon1 = (([508] * 3,14159265358979) / 180)))).Select(Projection(<>h__TransparentIdentifier1 => new <>f__AnonymousType2`2(<>h__TransparentIdentifier1 = [531], x2 = (Sin([508]) * Cos([508]))))).Select(Projection(<>h__TransparentIdentifier2 => new <>f__AnonymousType3`2(<>h__TransparentIdentifier2 = [531], d = (3963,1 * ((-1 * Atan(([508] / Sqrt((1 - ([508] * [508])))))) + 1,5707963267949))))).Where(<>h__TransparentIdentifier3 => (([508] != null) && ([508] != null))).Select(Projection(<>h__TransparentIdentifier3 => [508]))
i.o.w., you don't want this 
. Will look into a way to run this though.
Ok I have it working
1) I used float fields:
CREATE TABLE [dbo].[CreativeAddresses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](50) NULL,
[Latitude] [decimal](18, 10) NULL,
[Longitude] [decimal](18, 10) NULL,
[LatitudeF] FLOAT NULL,
[LongitudeF] FLOAT NULL,
CONSTRAINT [PK_CreativeAddresses] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Then I defined the missing mappings for the functions:
internal class AdventureWorksFunctions
{
public static double Sin(double a)
{
return 0.0;
}
public static double Cos(double a)
{
return 0.0;
}
public static double Atan(double a)
{
return 0.0;
}
public static double Sqrt(double a)
{
return 0.0;
}
}
internal class AdventureWorksFunctionMappings : FunctionMappingStore
{
public AdventureWorksFunctionMappings()
{
this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Sin", 1, "SIN({0})"));
this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Cos", 1, "COS({0})"));
this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Atan", 1, "ATAN({0})"));
this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Sqrt", 1, "SQRT({0})"));
}
}
Then I wrote the query with these functions:
[Test]
public void DoubleAndDecimalMathCalculationsWithLet()
{
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
LinqMetaData metaData = new LinqMetaData(adapter, new AdventureWorksFunctionMappings());
const double EarthRadius = 3963.1;
var q = from address in metaData.CreativeAddress
//let lat1 = (double)address.Latitude.Value * Math.PI / 180
//let lon1 = (double)address.Longitude.Value * Math.PI / 180
let lat1 = address.LatitudeF.Value * Math.PI / 180
let lon1 = address.LongitudeF.Value * Math.PI / 180
let x2 = AdventureWorksFunctions.Sin(lat1) *
AdventureWorksFunctions.Cos(lat1)
let d = EarthRadius * (-1 * AdventureWorksFunctions.Atan(x2 / AdventureWorksFunctions.Sqrt(1 - x2 * x2)) + Math.PI / 2)
where address.Latitude.HasValue && address.Longitude.HasValue
select d;
foreach(var d in q)
{
Console.WriteLine(d);
}
}
}
which gives a value, I added random values to the db, so it has no meaning, but it does work. Please check your code what's different in your code.
(generated query: )
Query: SELECT [LPA_L1].[d] FROM (SELECT [LPA_L3].[Id], [LPA_L3].[City], [LPA_L3].[Latitude], [LPA_L3].[Longitude], [LPA_L3].[LatitudeF], [LPA_L3].[LongitudeF], [LPA_L3].[lat1], [LPA_L3].[lon1], [LPA_L3].[x2], @LLBLEP1 * ((@LLBLEP2 * (ATAN([LPA_L3].[x2] / (SQRT(@LLBLEP3 - ([LPA_L3].[x2] * [LPA_L3].[x2])))))) + @LLBLEP4) AS [d] FROM (SELECT [LPA_L4].[Id], [LPA_L4].[City], [LPA_L4].[Latitude], [LPA_L4].[Longitude], [LPA_L4].[LatitudeF], [LPA_L4].[LongitudeF], [LPA_L4].[lat1], [LPA_L4].[lon1], (SIN([LPA_L4].[lat1])) * (COS([LPA_L4].[lat1])) AS [x2] FROM (SELECT [LPA_L5].[Id], [LPA_L5].[City], [LPA_L5].[Latitude], [LPA_L5].[Longitude], [LPA_L5].[LatitudeF], [LPA_L5].[LongitudeF], [LPA_L5].[lat1], ([LPA_L5].[LongitudeF] * @LLBLEP5) / @LLBLEP6 AS [lon1] FROM (SELECT [LPLA_1].[ID] AS [Id], [LPLA_1].[City], [LPLA_1].[Latitude], [LPLA_1].[Longitude], [LPLA_1].[LatitudeF], [LPLA_1].[LongitudeF], ([LPLA_1].[LatitudeF] * @LLBLEP7) / @LLBLEP8 AS [lat1] FROM [AdventureWorks].[dbo].[CreativeAddresses] [LPLA_1] ) [LPA_L5]) [LPA_L4]) [LPA_L3]) [LPA_L1] WHERE ( ( ( ( ( ( [LPA_L1].[Latitude] IS NOT NULL) AND ( [LPA_L1].[Longitude] IS NOT NULL))))))
Parameter: @LLBLEP1 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3963,1.
Parameter: @LLBLEP2 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: -1.
Parameter: @LLBLEP3 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @LLBLEP4 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1,5707963267949.
Parameter: @LLBLEP5 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3,14159265358979.
Parameter: @LLBLEP6 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 180.
Parameter: @LLBLEP7 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3,14159265358979.
Parameter: @LLBLEP8 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 180.
(edit) with the latest runtime libs of course (of April 2009)
Joined: 18-Apr-2006
Otis wrote:
Ok I have it working
Brilliant! I have it running too, now all I have to do is see whether it performs good enough
wtijsma wrote:
Otis wrote:
Ok I have it working
Brilliant! I have it running too, now all I have to do is see whether it performs good enough
hehe 
Well, back in the days of the demoscene where angle/rotation calculations were common nature, we tend to precalculate as much as possible. So for example, you could pre-calc PI/180 and perhaps other values. SQL is pretty poor when it comes to doing these kind of operations.