?
Solved

Remove duplicate pairs

Posted on 2004-11-22
6
Medium Priority
?
511 Views
Last Modified: 2008-02-01
I have a table of street name intersections (street1, street2, x_coord, y_coord, fdname1, fdname2) - here is the current query...

select distinct *
from VIEW_XSTREETS
WHERE STREET1 IS NOT NULL AND STREET2 IS NOT NULL
order by street1 asc, street2 asc

This works great to populate two drop down lists (using cf_jselect) but it includes duplicate pairs. I believe this is because the x and y coords are not identical for each pair (they are a little bit different) so they have multiple listings in the original table. I have to include the coords in the select because they are crucial to the map update after selection of the correct pair.

Here is the sql that creates the view...

CREATE VIEW dbo.VIEW_XSTREETS AS
   SELECT
      FDPRE1 + ' ' + FDNAME1 + ' ' + FDTYPE1 AS STREET1,
      FDPRE2 + ' ' + FDNAME2 + ' ' + FDTYPE2 AS STREET2,
       X_COORD,
      Y_COORD,
      FDNAME1,
      FDNAME2
FROM dbo.fwBV_Metro_Intersections
WHERE (FDNAME1 IS NOT NULL) AND (FDNAME2 IS NOT NULL)


Question - How can I (any or all would help)...
1) change the original query that creates the view to remove duplicate combinations of the street1 + street2? or
2) Change my select query to filter them on the user end?
3) I am also interested in filtering the name pairs based on a comparison of coordinates - like select any unique pair of streets, but include duplicates if the coordinates are more than plus/minus 50 different (indicating it might actually be a significantly different location).

Thanks a million in advance!

I have been farting around with this for a couple days but I am kind of new to this stuff and have run out of time.
I really need this solved today (although I think it is probably fairly simple for an experienced sql person...)
 - so higher points were assigned due to the urgency.
0
Comment
Question by:Bauerchick
  • 3
  • 3
6 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12649792
How about this?

SELECT STREET1, STREET2, AVG(X_COORD) X_COORD, AVG(Y_COORD) Y_COORD, FDNAME1, FDNAME2
FROM VIEW_XSTREETS
WHERE STREET1 IS NOT Null AND STREET2 IS NOT Null
GROUP BY STREET1, STREET2, FDNAME1, FDNAME2
ORDER BY STREET1 ASC, STREET2 ASC
0
 

Author Comment

by:Bauerchick
ID: 12649867
Hey thanks! The selection now appears to have unique pairs (that was pretty simple... I was trying to do self joins and other such approaches) - but please help me understand what the avg part does?
0
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12649877
It averages the x-coordinates and y-coordinates for each intersection. I guessed you would still want values returned here, and averages seemed to be the most appropriate aggregate function to include in your context.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:Bauerchick
ID: 12649917
Is it then averaging the x,y of all the duplicate pairs? Cool solution if it is, but I might actually want to be able to distinguish two slightly different intersection points... do you have any ideas on how to filter for a stated distance?   like 50 or 100 feet? (the coordinates are in feet, of course). Being able to show the pairs that are not really close to each other could help locate incorrect street entries too...
0
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 2000 total points
ID: 12650013
Yes, it is doing that. You can't include slightly different intersection points without reintroducing the duplicates.

There is no quick to execute way of doing exactly what you suggest, though I see the problem you mean - two streets may cross more than once, for instance, if they aren't straight. Is that right? Then you would need to go for:

SELECT DISTINCT STREET1, STREET2, ROUND(X_COORD, -2) X_COORD, ROUND(Y_COORD, -2) Y_COORD, FDNAME1, FDNAME2
FROM VIEW_XSTREETS
WHERE STREET1 IS NOT Null AND STREET2 IS NOT Null
GROUP BY STREET1, STREET2, FDNAME1, FDNAME2
ORDER BY STREET1 ASC, STREET2 ASC

This will round all coordinates to the nearest 100 feet and then select distinct. It's not really feasible to work on actual distances, as the overhead of computing them is high, but this might suit your purpopses.
0
 

Author Comment

by:Bauerchick
ID: 12650316
Thanks for your help, muzzy!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question