Location Coordinates Query
Jul 20, 2005
Hi All,
I've been struggling with how best to define a query to get a list of unique
coordinates from a database table and my SQL skills are not good enough to
work out a good solution. The basic table structure is as follows:
CREATE TABLE [Readings Data Table] (
CY DOUBLE,
PY DOUBLE,
C1X DOUBLE, C2X DOUBLE,
P1X DOUBLE, P2X DOUBLE)
Each record in the table represents a geophysical measurement for a sensors
at the four locations (P1X,PY), (P2X,PY), (C1X,CY) and (C2X,CY). There can
be many repeat readings (ie with the same locations) and many overlapped
readings (ie which share at least one sensor location) and an example set of
data is:
CY PY C1X C2X P1X P2X
0 0 0 100 200 300
0 0 0 100 200 300
0 0 0 100 300 400
0 0 0 100 400 500
0 0 100 200 300 400
0 0 100 200 500 600
100 0 0 100 200 300
The distinct locations for this set are:
(0,0), (100,0), (200,0), (300,0), (400,0) (500,0), (600,0), (0,100),
(100,100), (200,100) and (300,100) ie there are 11 unique locations out of
the 28 (=7*4) sensor measurement locations.
My problem is to create a table with a list of just the distinct sensor
locations. I have implemented a brute-force method where I build a table by
looping through each of the four coordinate combinations to create all
possible locations and then getting just the distinct locations, but this is
quite slow with even just a moderate number of readings. Can anyone suggest
a more efficient way to get the distinct sensor locations using SQL?
Many thanks
John Paine
View 2 Replies
ADVERTISEMENT
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
Apr 28, 2008
I'm writing a script for a pending migration (2k enterprise to 2k5x64 enterprise). I've got about 325 or so databases, spanned across 3 instances that I need to migrate. My plan is to do a mass scripted detach of all DBs of the old boxes, copy the files (on a new domain) and then reattach all of the mdf and ldf files.
I've been writing a sql script that will scan a "staging" directory for each of the MDF and LDF files, then programmatically run sp_attachdb. The problem is that the mdf and ldf files weren't always a 1 to 1 naming convention. My predecessors stuck all sorts of things in the file names that make doing a simple scripted search pretty difficult. The disk paths are going to be different from server to server (simpler), so I need to move the files
Is there a way (using sp_attachdb or otherwise) that I could somehow scan each of the MDF files to get the path of the LDF? I can already do that with a .bak file using the RESTORE FILELISTONLY command, but I'm not sure how to do it like this. I'm figuring there has to be a way though, as SSMS seems to know where the LDF is if you try to attach an MDF.
Usually, Google does well, but I haven't been able to find anything as of yet. Any ideas?
View 14 Replies
View Related
Apr 7, 2014
I have a question regarding the selection of arrival and departure city.
CREATE TABLE #XY123
(
tktamt MONEY,
departcty VARCHAR(5),
arrivalcty VARCHAR(5),
tktnum INT
[Code] ....
There are One way, Round trips and journey with connecting flights in the above data, what I am looking for a report is tktamt, deprt city, arrival city and tktnum in a single line for each trip.
SO if it is one way trip or round trip there is no issue, if it is a trip with connecting flights tktnum has more than four records. In the above case for tktnum 34567 there are four records, for which I have to display only one record, where the depart city is 'JFK and Arrival city is 'SFO' as well as amount and tktnum.
View 9 Replies
View Related
Sep 20, 2006
I am working on a program in VB 2005 in which i want to store and retrieve GPS coordinates. I am not sure which data type is the best to use to enter Latitude & Longitude numbers and maintain their proper integrity.
Like LAT ( N38 28.025' ) and LONG (W105 52.098' )
The numbers will be entered by the user and that format can be maintained, but how to re-enter & or insert them into the database using the same format is my real question.
I hope I have explained this right. The numbers in BOLD are what I need to maintain.
Thanks for any help in advance.
Steve
View 3 Replies
View Related
Dec 2, 2013
is there a way to change SSMS 2012 default query save location?
Scenario:
1. Open SSMS
2. Create new query
3. Click Save
I see "DocumentsSQL Server Management Studio" folder, but I want to change it to be "d:". How do I do this?
I tried:
1. [URL]- in folder "DocumentsSQL Server Management StudioSettingsSQL Server Management Studio" there is a file NewSettings.vssettings, setting it to "d:" or "d:" didn't work.
2. Changing HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShell VisualStudioProjectsLocation didn't work too. There is no "Shell" under "110Tools"
Did I do something wrong, or is there another way?
View 6 Replies
View Related
Oct 19, 2006
It is very intersting to find out that when we are backuping a database we are not able to specify a shared folder .Why?Is it because security concern or it is idenfied as bug or is there any work around.Only thing we are able to specify is that it points to the local drive of the particular server. Help would be more appericated.
View 7 Replies
View Related
Mar 27, 2007
Does anyone know where a good article pertaining to where you should locate your Data and Log files (in SQL Server 2005)?
I read an article several years ago stating that Log files should be on a seperate RAID 1 and Data on a seperate RAID 5.
Anyway, any help is appreciated.
View 1 Replies
View Related
Apr 10, 2007
I am getting this error when i run my aspx project..Server Error in '/' Application.
Incorrect syntax near '.'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '.'.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near '.'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354 System.Data.SqlClient.Sql is there anyway that i can get the line number ,file or any other information regarding location off error.. i got abt 3 aspx files..so i dont really know where to look for the error..
View 2 Replies
View Related
Jun 16, 2008
Hi Guys,
I'm actually a beginner in SQL Server and I got use to a database called "Cache" where the Data itself is stored separately from the tables (Table definition), and I'm wondering if its the same in SQL Server where the Data is stored in a deferent location then the Table definition, and if so, where it could be stored?
Thanks.
View 3 Replies
View Related
Feb 20, 2001
Can someone tell me what the default installation location
is for SQL 2000? Is it different for the default instance
compared to a named instance? What about the tools? It
seems to me I read something about the tools defaulting
to the program filesmicrosoft SQL Server directory.
I guess I'm more concerned about each instance and the
location of those files. I don't necessarily like the
idea of each instance installing to the program files
directory, if that's the default.
Can anyone help me out?
Thanks
Toni
View 1 Replies
View Related
Jul 14, 2000
Hello,
Can anyone tell me how to change the location of 'tempdb.mdf'
It locates in C drive but I want to move to D drive because D has much more space than C in my computer.
Thanks,
Mike
View 1 Replies
View Related
Feb 17, 2000
Hi,
currently, the default location for new db is c:msqldbdata and I want to change it to another location.
anyone know how?
Thanks.
View 1 Replies
View Related
Jul 24, 2007
We have three separate disks for our sql server.
Disk c : system
Disk d : data
Disk e : logs
A couple of months ago our IT manager moved the tempdb database from the c drive to the d and e drives because it had grown to 9Gb and the c drive had run out of space. When I discovered this, I shrank tempdb back to it's specified size of 1.9Gb and now I want to move it back to the c drive because I believe we will see better performance with the system + tempdb together than with the data/logs + tempdb together. Am I correct?
A couple of notes. Server is used exclusively by SQL server. We have three large databases (40Gb, 20Gb, 15Gb), three databases around 4Gb databases and around 15 smaller databases. Most of them are accessed exclusively from a web application.
View 5 Replies
View Related
Jun 3, 2008
Easy question, I hope We are setting up an active active sql cluster. The management have done everything the wrong way around and purchased the hardware already.
We are planning on having 2 databases 1 in each instance. We will place the transaction log files for each database on their own cluster "physical disk" resource. We only have 4 disks availalbe for the transaction log files. To make things a little more complicated I have been given no information on the expected transaction use other than they should be no larger than 50GB
Here are my options
1. 2 Mirrored raid arrays one for each sql instance
2. 1 Raid 10 array with 2 logical drives (so the transaction logs are sharing the same disk spindles)
What would you do? seperate the disk spindles or share them and go for fast disk performace.
Thanks in advanced
View 1 Replies
View Related
Nov 7, 2007
Hi all,
I hope someone can give me some tips on how to implement a nice solution.
I need a table to store distances between 2 locations.
First thought was to make a table like this
- id
- fromid
- toid
- distance
However distances can be used in both directions.
So a search to get distance between xx and yy would be: select distance where (fromid = xx and toid = yy) or (fromid=yy and toid=xx)
A unique index (fromid, toid) would make sure no double entries are made, but this still doesn't deny anyone from changing from and to and entering a distance in the other direction (from yy to xx)
Any suggestions for a cleaner db design ?
Ideally one where searches don't need the or, or where the db design can restrict people from entering a distance in the other direction ?
I could force something in business logic to sort locations alphabetically but that also seems like a dirty trick..
Aeneas.
View 4 Replies
View Related
Jul 30, 2005
Hi again,I am able to schedule a backup and default it to the server. However, isthere an option or a way to locate the backup somewhere else on the network?regards,gilbert
View 2 Replies
View Related
Jul 1, 2006
Hi!
When I'm looking at the properties of my dbases I see there are at c:program filesms sql server etc..
But I want to put ALL my current and future databases to E:.
Can somebody tell me how to achieve that?
View 2 Replies
View Related
Oct 2, 2007
I have an installation of SQL2005 SP1 configured as follows:
Dell Poweredge 2650 with 4 GB RAM
c:, d: and e: are all on the local host and f: and g: drives are on a SAN. I have the system db's on the e: local disk and the Team Foundation Server databases and logfiles on the SAN disks. SQL2005 is installed to the E: disk.
We are migrating VSS to TFS and our times are very long for a simple 1 GB migration.
I need to know if I need the application databases and logfiles and system databases on the same disk because I see a lot of disk thrash and the tempdb is getting large, 3 GB today.
SQL architecture - should the system db's be on the same disk as the application db's?
Thanks
View 1 Replies
View Related
Jul 17, 2006
Hi,
When the battery dies the SQL Ce installation vanishes. Especially the following dlls which were copied into the Windows directory during installation are getting vanished when the battery dies.
sqlCese30.dll
sqlCepqp30.dll
sqlcemme30.dll
sqlceer30EN.dll
I guess this is because of the fact that RAM content vanishes when the power goes off.
Is there a way i could install the SQL CE in the Storage Card?
I can install the Compact Framework in the Storage Card but not the SQL CE.
I have installed the CF, My application in the Storage Card. Just because of this SQL CE does not allow me to choose the installation directory, i have to install the SQL CE everytime the battery dies out.
View 4 Replies
View Related
Apr 10, 2007
Can anyone tell me where the data for a snapshot is kept at?
R
View 1 Replies
View Related
Mar 12, 2007
Can SQL Server be installed onto a file server...if so, how?
View 8 Replies
View Related
May 10, 2007
How do you know what exactly to use as the database name?
In school the prof says he is not exactly a database programmer but gave us some code he had from an old one. That code worked fine if you want to actually connect to the database at school but I want to send everything right through sqlexpress on my own machine so I can work on the programs at home.
The following is the code to connect to the school database, how would I change it to connect through sqlexpress instead?
Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
namespace Database
{
/// <summary>
/// Summary description for Class1.
/// </summary>
public class Class1
{
SqlConnection connection;
public Class1()
{
}//closes constructor
public void connect(string computerID)
{
connection = new SqlConnection("Data Source = " + computerID +
"; Initial Catalog = test; Integrated Security = SSPI");
try
{
if(connection.State == ConnectionState.Closed)
connection.Open();
}//closes try
catch(Exception ex)
{
MessageBox.Show("connection no" + ex.Message.ToString());
}//closes catch
}//closes connection method
public void Write(string Name, string ID, string Grade, string Average)
{
string add = "INSERT INTO Example VALUES('" + Name + "', '" +
ID + "', '" + Grade + "', '" + Average + "');";
try
{
connect("DNV-WS-RM336INS");
SqlCommand command = new SqlCommand(add, connection);
command.ExecuteNonQuery();
}//closes try
catch(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}//closes catch
this.connection.Close();
}//closes write mehtod
public string Read()
{
string Info = "";
SqlCommand command = new SqlCommand("select * from Example;", connection);
SqlDataReader reader = command.ExecuteReader();
try
{
while(reader.Read())
Info += reader.GetString(1).ToString() + " " +
reader.GetString(2).ToString() + " " +
reader.GetString(3).ToString() + " " +
reader.GetString(4).ToString() + "";
}//closes try
catch(Exception ex)
{
MessageBox.Show("read Method " + ex);
//Console.WriteLine("Error retrieving data.");
}//closes catch
reader.Close();
return Info;
}//closes read method
}//closes class
}//closes namespace
What I tried was replacing the dialog for the school database with just the text sqlexpress but that certainly didn't work.
View 5 Replies
View Related
Sep 21, 2007
Hello,
I'm wondering where exactly is the default location of the rdl file stored on the server?http://<server>/reports/
Thanks,
-Lawrence
View 6 Replies
View Related
Mar 2, 2006
Hello,
In process of installing TFS, I unintentionally installed SQL server on C drive as I could'n see any option of selecting the drives.
Is there any way in which I can move the database folder to another drive without reformatting and going through the same procedure.
TIA
View 1 Replies
View Related
Jul 13, 2006
Got a couple of questions
1. If you are managing databases in SQL Express with SQL Server Managment Express CTP, why doesnt VBE place the database there where you can see them? I can create a database inside SQL Server Managment Express CTP and view its tables, relations,etc. from within SQL Server Managment Express CTP, but when I make a database with VBE it does not show up in SQL Server Managment Express CTP. Also the database that was made inside SQL Server Managment Express CTP cannot be seen from VBE. Why? what did I do wrong, or is it supposed to be like that? Is that where you would want to place a database for multiple pc to share?
2.Login Question ;-----shouldnt post this here but,
Is there a reason why you dont want to use SQL Authetication over Windows? The are a few pcs here that doesnt have user accounts installed(I know that not a good practice, but when you have multiple people needing different data quickly- switching users is not preferred, its a time thing for what I would call dummy terminals).
How do you take an instance of SQL and change to SQL Authetication over windows?
Curious?
David
View 3 Replies
View Related
Oct 29, 2007
hi frends
I am working on project in which we have to show advertisements as per users country. how i can know about the location of user that just type the url and press enter.
any body have any solution or tips most welcome
Thanks
Sarbjit
View 3 Replies
View Related
Sep 21, 2007
Hi all,
Need Some Advice€¦
Current Setup of the Databases:
All €œsystem databases€? + €œreportserver & reportservertempdb€? are on €œ D drive€? --- Raid 1 ( Mirrored) and their location is : D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData
Free Space left on "D" Drive is # 18 Gb.
€œUser Databases€? are on ---- €œE Drive€? --- Raid 5 and their location is E:Datadb
Now , we got new storage device with much larger capacity. So, we are planning to transfer our databases on this new drive which is €œRAID 5€?.
My question is:
(1) Should I transfer all the databases ( (i.e.) €œsystem databases€?+ €œreportserver & ReportServerTempdb€? + €œUser databases€?) on that new drive ( RAID 5).
(OR)
(2) Keep the €œsystem databases€? + €œReportServer& ReportServerTempdb€? on the old
drive (i.e.) RAID 1 and Transfer only the €œUSER Databases€? on that new drive (i.e.) RAID 5.
(OR)
(3) Transter only Tempdb to the new drive (i.e.) on Raid 5, so as to avoid any I/O congestion. and keep all other system & ReportServer & ReportServerTempdb on the old "D"drive.
(OR)
(4) Is there anything I should be careful when doing this & Is there any better way of doing this€¦.
Thanks for all your help€¦.
View 4 Replies
View Related
May 7, 2007
hey i am going to be writing a query to search for stock in a certian location, but what i wnat to do is allow the user to search in all locations as well, how could i do this? or would it be more asp based?
Jez
View 16 Replies
View Related
Feb 1, 2002
Hello all..
I need your precious expertise in resolving one problem . I have transaction log file devices created on on two drives i.e c and d drives .
I want to move the the log device from d drive to c drive or vice versa .
How can i do it . Can somebody help with detailed steps pl .
What are the things necessary to check after the device has been moved to either of the locations , to ensure that everythingh works well .
I have SQL Server 7.0 with SP 3 .
Will the database be marked suspect ? I that case what should i do ?
Any kind of help on the issue will help me a lot .
Thank you all in advance
Annalina .
View 1 Replies
View Related
Feb 20, 2002
How do I move the location of the tempdb. I wish to place it on a separated
disk to to increase performance.
Parg
View 3 Replies
View Related
Dec 14, 2000
How do I move the tempdb to a different drive? It can't be dropped and it is automatically put on the same drive as the master during installation. I have moved a transaction log to a different drive, but I can't change the tempdb location. Please help.
View 2 Replies
View Related
Sep 26, 2003
Hi,
I wanted to find the location in database objects(table/stored proc/view/function) for particular text.
For example a text 'BU1032' is located in tiles and titleathor table at PUBS databse.
I wanted get the result like
text_name filed_name table_name
--------- --------- ---------
BU1032 title_id titles
BU1032 title_id titleauthor
if we are using in stored proc/view for comparison that should be display
like
text_name storedproc_name
-------- ---------------
BU1032
Anybody has script for this kind of search?.
Thanks,
Ravi
View 6 Replies
View Related