Remove duplicate pairs

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.
BauerchickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
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
BauerchickAuthor Commented:
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
muzzy2003Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BauerchickAuthor Commented:
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
muzzy2003Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BauerchickAuthor Commented:
Thanks for your help, muzzy!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.