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).
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
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.
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
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.
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.
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!
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?
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
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
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?
This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.
If you want it in miles, change the average radius of Earth to miles in the function.
create function dbo.F_GREAT_CIRCLE_DISTANCE ( @Latitude1 float, @Longitude1 float, @Latitude2 float, @Longitude2 float ) returns float as /* fUNCTION: F_GREAT_CIRCLE_DISTANCE
Computes the Great Circle distance in kilometers between two points on the Earth using the Haversine formula distance calculation.
Input Parameters: @Longitude1 - Longitude in degrees of point 1 @Latitude1 - Latitude in degrees of point 1 @Longitude2 - Longitude in degrees of point 2 @Latitude2 - Latitude in degrees of point 2
Hi All, Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:
I've got a working query which returns all leads within a supplied proximity to a city. I followed a tutorial I googled a couple months ago (can't find it now). It works, but would love others to look the query over (provided DDL and sample data) and tell me if it's as it should be.
Two things I don't like about query:
1. I have to do a UNION to another query that retrieves everything that is in the same city in order to have complete results. 2. very slow to retrieve results (> 1 minute)
During the installation of Adding node to a SQL Server failover cluster(On passive node) getting error like.. The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.We run the below commands but didn’t get any resolution & got the same above error .
1<sup>st</sup> Method…
1. Open console command (Run->CMD with administrator privileges).
2. net stop winmgmt
3. Rename folder %windir%System32WbemRepository to other one, for backup purposes (for example _Repository).
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2 Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid. Msg 3156, Level 16, State 50, Line 2 File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file. Msg 3119, Level 16, State 1, Line 2 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
Can I build a cluster by adding the cluster service, then the SQL instances, then add the other nodes and their passive SQL instances?I would lean to building the cluster first, the add the SQL instances.
I have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512), @sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX), @column NVARCHAR(MAX); DECLARE @indexname NVARCHAR(512); SELECT name As 'Table'
We are planning to change all IPs of PRODUCTION Failover Cluster Setup. In my cluster setup ... we have 2 Physical Nodes with windows-2008, Roles are MSDTC and SQL-2008R2.
IP change for:
1. Both Nodes(Physical) 2. MSDTC 3. SQL Server 4. windows Cluster
So Almost... All IPs are going to change.
Im DBA here, I need to take care of SQL cluster and MSDTC. But I haven't performed this activity before.So I'm worrying about Impacts and consequences of this change. steps how should I perform this activity.
We have many tables which have cluster index on column with datatype 'Char(200)'. Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
