location coordinates query

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[posted and mailed, please reply in news]

John Paine (jpaine@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95907781F7757Yazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> John Paine (jpaine@.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