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 2 1644
[posted and mailed, please reply in news]
John Paine (jp****@bigpond .net.au) writes: 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:
In general, for this type of queries it is a good advice to post:
o CREATE TABLE statement for the involved table(s). (Which you did,
thank you.)
o INSERT statements with sample data.
o The desired output given the sample data. (Which you did, thanks!)
This makes it easy for anyone who takes a stab at your problem to post
a tested solution, because it's easy to cut and paste into Query
Analyzer.
Since there is no INSERT statements for the data, this is an untested
solution:
SELECT P1X, PY FROM [Readings Data Table}
UNION
SELECT P2X, PY FROM [Readings Data Table}
UNION
SELECT C1X, CY FROM [Readings Data Table}
UNION
SELECT C2X, CY FROM [Readings Data Table}
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1... [posted and mailed, please reply in news]
John Paine (jp****@bigpond .net.au) writes: 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:
In general, for this type of queries it is a good advice to post:
o CREATE TABLE statement for the involved table(s). (Which you did, thank you.) o INSERT statements with sample data. o The desired output given the sample data. (Which you did, thanks!)
This makes it easy for anyone who takes a stab at your problem to post a tested solution, because it's easy to cut and paste into Query Analyzer.
Since there is no INSERT statements for the data, this is an untested solution:
SELECT P1X, PY FROM [Readings Data Table} UNION SELECT P2X, PY FROM [Readings Data Table} UNION SELECT C1X, CY FROM [Readings Data Table} UNION SELECT C2X, CY FROM [Readings Data Table}
Hi Erland,
Thanks for the tips on posting to this group. I haven't used newsgroups much
in the past, so I'll keep them in mind next time I post here.
Thanks especially for the answer as it has helped speed the process
significantly. In a small sample database with 4028 readings, there are 528
unique locations and the change from brute-force to the use of the UNION
code produced a speed-up of about 4 times. Which is enough to eliminate an
annoying wait of 20 seconds when recalculating the location coordinates.
John Paine This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Siegfried Heintze |
last post by:
I want to implement drag and drop for tables, divs, spans. The problem is
that I don't know how wide or long my tables, divs and spans are going to be
in advance so I cannot use absolute coordinates. (It always seemed odd to me
that there is no function called GetExtent that would return the bounding
rectangle of an object -- this would solve my problem).
There is a way, however, to fetch the coordinates of an object (such as a
table, or...
|
by: steve bull |
last post by:
I have a picture box which lies on a panel which lies on a form. Is there some way that I can calculate the position of
the picture box relative to the form rather than the panel on which it lies? The location of the picture box in the
Control class is given relative to the panel on which it lies.
Thanks,
Steve
|
by: MAF |
last post by:
I am draggin an object into a user control that contains a tree view and
want to insert the object into the tree. The problem I have is that the X,
Y(coordinates for the form that contains the user control) are not within
the underlying treeview control. Isn't there a calculation that I can use
to adjust the x, y coordinates?
Thanks
|
by: Biff |
last post by:
Hello,
I am bringing up an iFrame with a calendar control in it in
coordination with a text box that holds a date field. In my code
behind class I add a method call to the text box's OnFocus event that
calls the javascript method that shows and positions the iFrame
(arguments are the text control, and the desired x and y coordinates of
the iFrame). Right now I use hardcoded fixed values for the calendar
location - it was expedient and...
|
by: KatB |
last post by:
Hi, I'm creating an asp.net application and one of the functions I need
is to click on a "Where Is This Office Located?" button. In a table,
each employee will have an office code location entered.
I then need to have the office layout image load and have the name of
that person highlighted such as bold/red (in the correct office
location).
Similar to an image map, but not quite. I do not want to click on an
office "hotspot" and go...
| |
by: quickcur |
last post by:
Hi, I have html like this:
<div id="myCanvas" style="border:10px,
black;position:relative;height:250px;width:100%;">
<img id="p" src="p.jpg">
</div>
When user click the mosue, I would like
1. Test if the mouse location is on top of the image
|
by: Jon Slaughter |
last post by:
Is there a way to get a controls location in screen coordinates? (not
relative to the container it is in)?
Thanks,
Jon
|
by: EggHead |
last post by:
Hi all,
My development environment is VB 6.0, and my OS is winxp. I would like to know how to find the mouse location at a control when the mouse is point at that control. The problem is that the mouse "whatever" events will not fire since the mouse is hjhack by other control. Anyway, I have the following code and I can check is the mouse over the control or not, however, I cannot find out where the mouse location at that control.
Private...
|
by: ajithmanmadhan |
last post by:
hi pls help me out in inserting text into a richtextbox.
the situation is like this::
i want to insert a string say "hello" at a particular location in the rich textbox.
The particular location is given in the form of X and Y coordinates.
eg:: PutString("hello",10,20)
i write a function that passes the string and x,y coordinates.
is that possible??
x,y coordinates will represent the location of the string in richtextbox.
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |