Nearest Distance
Jan 21, 2004
Hi
How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated
View 12 Replies
ADVERTISEMENT
Mar 1, 2007
Is there a recommended practice for mirroring in regards to distance? Is it best practice to mirror with both nodes at the same physical location and use another method for failing over to a remote location or can one just put the other node in the mirror a few thousand miles away? I'm suspecting not.
Any comments??
View 2 Replies
View Related
Mar 18, 2008
Hi,
please, it is possible to know the edit distance used in the fuzzy lookup/grouping.
On this forum I read fuzzy lookup use 4-gram with fix size.
Does exist any document explaining how fuzzy lookup calculate the similarity? In other word, what kind of edit distance, algorithm is used by fuzzy lookup/grouping?
I hope I was enough clear with my poor english.
Thanks All
View 1 Replies
View Related
Oct 7, 2006
Hi, I'm making a birthdays database where I want to list everyone in it ordered by the firstcoming birthdays according to the current date.
I have all the birthday records stored in a table called bursdager and the person name is stored in the navn column and the persons birthday date is stored in the dato column.
I'm having some problems, currently I have this statement:
Code:
DECLARE @tbl TABLE (navn VARCHAR(60), dato DATETIME)
INSERT INTO @tbl
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) >= DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)
INSERT INTO @tbl -- those are the one who allready have had birthday this year
SELECT navn, dato FROM bursdager
WHERE DATEPART(month, dato) < DATEPART(month, getdate())
ORDER BY DATEPART(month, dato), DATEPART(day, dato)
SELECT * FROM @tbl
It works *allmost* as it should-- except, it still lists the last persons who had birthday first, even the days after their birthday if the month is still the same.
I thought about adding an additional check:
Code:
AND DATEPART(day, dato) >= DATEPART(day, getdate())
in the WHERE clause of the SELECT statement but that won't be correct either because it then just lists everyone based on whether the day number the person was born is higher or less than the day number of the current date.
Anyone have any suggestions? Is there an easier way to do it?
Dag
View 7 Replies
View Related
Sep 16, 2014
I have a column called as NDM$ What I want do it round it the nearest value example I am giving below
34.100->34%
39.8->40
35.4->35 some thing like that.
View 6 Replies
View Related
Nov 16, 2006
T-SQL:How to round to the nearest WHOLE number ?sofrom -- to-------------170 --17096.58 --97thanks
View 1 Replies
View Related
May 11, 2007
Hi
Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?
Thanks,
Neil
View 7 Replies
View Related
Mar 28, 2007
I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.
SELECT UserID, ACOS(SIN($StartLatitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($StartLatitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($StartLongitude - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
HAVING (Distance <= 150)
It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.
View 5 Replies
View Related
Jul 23, 2005
I'm looking to find out how I'd go about setting up a database where avisitor to my site could punch in their postal code, and find out how farthey are from another postal code. For example, AutoTrader has this featureI believe to tell you how far the vehicle is from you. Dating sites havethem so you can do proximity searches.Anyone have any ideas where I could start? I'm thinking the post office,but if anyone else has suggestions, I'm open to hear them.Thanks!
View 4 Replies
View Related
Mar 20, 2007
Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.
Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?
View 4 Replies
View Related
Mar 27, 2007
I am new to data mining so please excuse my ignorance. Lets assume
- i have created a cluser model
- identified 3 clusters ( a, b, c)
- each record consists of 15 columns
- collecting new records( 15 variables) real time
what i would like to do is plot these new records programmatically as i collect them realtime. I assume this new record will belong to one of these three clusters. I believe we can find the cluster this new record belongs to by ' SELECT Cluster()....' and distance from the center of the cluster by ClusterDistance(). To plot this on a 2-dimentional space i need (x, y).
ClusterDistance() could be Y but what will be X.
thanks.
View 6 Replies
View Related
Jan 12, 2008
I have a user defined function, I want to determine the distance between the 2 points. I have it working but i'm having a problem getting to print.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Code Snippetcreate function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float
as
begin
declare @DegToRad as float
declare @Ans as float
declare @Miles as float
set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0
if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0
begin
return ( @Miles )
end
set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)
set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)
set @Miles = CEILING(@Miles)
return ( @Miles )
end
DECLARE @RC float
EXEC Distance '39.943762', '-78.122265', '32.334709', '-96.633546'
PRINT @RC /* in miles */
View 3 Replies
View Related
Dec 6, 2007
I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.
View 3 Replies
View Related
Mar 19, 2013
How would I match datetimes in records structured as follows:
Code:
Record1 AccountNo StartDateTime EndDateTime
1 1234 4/30/2012 8:00 AM NULL
2 1234 NULL 5/15/2012 8:00 AM
Desired Result:
Code:
AccountNo StartDateTime EndDateTime
1234 4/30/2012 8:00 AM 5/15/2012 8:00 AM
Of course there are multiple accounts, about 2100 in this case but they very by time periods, and multiple start and stop dates for an account. I need to get the start times and match them w/ the nearest end times but AFTER the value of the start time. Nearest end times must be forced to correspond to the nearest start time but there are some start times w/o end times and end times w/o start times due to user data entry errors. I need a solution that handles this. It is ok w/ the customer to make the assumption of nearest times supposedly going together so they can show the users the errors. I am on SQL Server 2008 R2.
View 4 Replies
View Related
Sep 8, 2014
how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.
Also, the solution should not be too cumbersome because it will be used in a high volume environment.
View 5 Replies
View Related
Nov 23, 2005
I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated
View 1 Replies
View Related
May 19, 2008
Hi,
I have such a scenario:
- two tables with record containing car vehicle number, datetime of message and other data like weight ect.
- first table contains only two messages for one car per one day
- second has many messages for one car for one day
I would like to get a list of messages from first table but joined with the nearest (previous) record for the same car from second table.
Thanks,
Przemo
View 13 Replies
View Related
Jan 7, 2008
OK, this is the scenario. I have a database with many columns ( each a mean value and a standard deviation, and with it a set of coordinates that i want to retrieve ).
Then i have a value that i want to query with the database, by comparing it with the mean and its standard deviation, and it should return a few sets (lets say 2) of coordinates whereby the the value of the mean is closet to the one in the database, in order of nearest value. How should i do it, since i am not using the exact value of the mean in the database?
I know its a bit confusing the way i wrote, but anyone understand wat i am trying to say and can help, i am very grateful. I had googled around for answers but cannot find. Thanks.
View 4 Replies
View Related
Dec 7, 2007
I have a date (my birthday). I would like to find the closest birthdays to mine, both before and after my birthday. I would like to list the people in my database who are the closest age to me, but in that order. So sorting my table by age and taking a row below and above my birthday is not going to work. This is because the three people below me may all have their birthday the next day, while those above me may have theirs years before mine.
Birthdays sorted by date:
05/10/1979 jim
12/01/1980 bob
10/04/1983 mike
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
05/07/1999 dylan
The order I wish to achieve is:
10/05/1983 larry
11/21/1983 dan
12/07/1984 josh
12/01/1980 bob
05/10/1979 jim
05/07/1999 dylan
Thanks in advance.
Mike
View 4 Replies
View Related
Oct 15, 2007
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
View 1 Replies
View Related
Oct 15, 2015
DECLARE @Latitude NUMERIC(9, 6), @Longitude NUMERIC(9, 6)
DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)
Set @Latitude = 42.329596;
Set @Longitude = -83.709286;
Set @MyLatitude = 42.430883;
Set @MyLongitude = -82.923642;
Question: How do we calculate the distance in miles between the 2 points.
View 5 Replies
View Related
Nov 22, 2013
I am trying to write a piece of SQL which gives me a list of enquiries within 10 metre distance of a enquiry.
The idea is to identify possible duplicates.
Table: enquiry
Primary key: enquiry_number
Co-ordinates data fields: enquiry.enquiry_easting and enquiry.enquiry_northing.
I will need to self-search on the same table to find possible enquiries within 10m distance.
View 1 Replies
View Related
Jun 24, 2005
See here www.merriampark.com/ld.htm for information about the algorithm. This page has a link (http://www.merriampark.com/ldtsql.htm) to a T-SQL implementation by Joseph Gama: unfortunately, that function doesn't work. There is a debugged version in the also-referenced package of TSQL functions (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5), but this still has the fundamental problem that it only works on pairs of strings up to 49 characters.
CREATE FUNCTION edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
DECLARE @s1_len int, @s2_len int, @i int, @j int, @s1_char nchar, @c int, @c_temp int,
@cv0 varbinary(8000), @cv1 varbinary(8000)
SELECT @s1_len = LEN(@s1), @s2_len = LEN(@s2), @cv1 = 0x0000, @j = 1, @i = 1, @c = 0
WHILE @j <= @s2_len
SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
WHILE @i <= @s1_len
BEGIN
SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1
WHILE @j <= @s2_len
BEGIN
SET @c = @c + 1
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
IF @c > @c_temp SET @c = @c_temp
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
IF @c > @c_temp SET @c = @c_temp
SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
END
SELECT @cv1 = @cv0, @i = @i + 1
END
RETURN @c
END
View 20 Replies
View Related
Jul 20, 2005
I am trying to use the haversine function to find the distance betweentwo points on a sphere, specifically two zip codes in my database. I'mneither horribly familiar with SQL syntax nor math equations :), so Iwas hoping I could get some help. Below is what I'm using and it is,as best as I can figure, the correct formula. It is not however,giving me correct results. Some are close, others don't seem right atall. Any ideas?SET @lat1 = RADIANS(@lat1)SET @log1 = RADIANS(@log1)SET @lat2 = RADIANS(@lat2)SET @log2 = RADIANS(@log2)SET @Dlat = ABS(@lat2 - @lat1)SET @Dlog = ABS(@log2 - @log1)SET @R = 3956 /*Approximate radius of earth in miles*/SET @A = SQUARE(SIN(@Dlat/2)) + COS(@lat1) * COS(@lat2) *SQUARE(SIN(@Dlog/2))SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))/*SET @C = 2 * ASIN(min(SQRT(@A))) Alternative calculation*/SET @distance = @R * @Cthnx,cjrsumner
View 7 Replies
View Related
Oct 15, 2007
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
View 1 Replies
View Related
Nov 24, 2014
I have a table with 257 mil records with latitude and longitude data.
My goal is to find the closest intersecting values from a locations table (88 rows) and update any of the 257 mil records that are applicable with the location_Name and Location_Group_Name.
The query I have works but doesn't perform well on such a big data set.
CREATE TABLE #Positions -- Base table 257 mil rows. Actual table has 20 columns
(
IDBigInt PRIMARY KEY,
LatitudeDec(10,6),
LongitudeDec(10,6),
[Code] ....
Attached you will find the tables, test data, a function to measure distance and some queries that work but are too slow for this much data.
View 9 Replies
View Related
Jun 28, 2015
how to round the nearest value after round of decimal and return integer.
declare @data decimal(18,2)
set @data = 5.55
-- output
select '5'
set @data = 5.58
-- output
select '6'
View 3 Replies
View Related
Feb 1, 2007
hi everyone:
the report show two tables two matrixs
how can i control the distance between them
I want to set the same distance between the table and matrix
or (table and table )
View 3 Replies
View Related
Jan 2, 2008
Could I implement a failover cluster solution on the two DBs which are based in two different cities?
Possible?
View 6 Replies
View Related
Jun 14, 2006
I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.
Does anyone have any algorithms in T-SQL to do this?
View 5 Replies
View Related
Oct 13, 2012
Table :
ChangeID ChangeDate EquipmentID ModuleID EquipStatus
1 12/9/08 230 1789 Normal
2 13/9/08 450 1245 Normal
3 17/9/08 230 1789 Open
4 21/9/08 230 1899 Open
5 21/9/08 450 1674 Normal
6 22/9/08 450 2364 Normal
Given a date, what module was each equipment item in on that date?How do I get the date of the nearest previous event from a list like this? I got a query from one of the post in this Forum only using Cross Apply to find the nearest record from the above table based on Date i.e.
SELECT outerT.*
FROM your_table AS outerT
CROSS APPLY
(
SELECT TOP 1
equipment_id
, change_date
FROM your_table AS innerT
WHERE innerT.change_date <= @point_in_time
AND innerT.equipment_id = outerT.equipment_id
ORDER BY change_date DESC
) AS applicable_records
WHERE applicable_records.change_date = outerT.change_date
The problem is I need to get this query without using Cross Apply as i need to use the same for the LINQ which doesn't support Cross Apply.
View 1 Replies
View Related
Aug 12, 2015
I am getting the time difference between two dates using
DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,
My output looks like
1.33
0.17
1.50
etc
I'd like to round to the nearest quarter hour
1.50
0.25
.150
etc
View 4 Replies
View Related
Dec 6, 2007
I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.
SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)
View 4 Replies
View Related