Bauerchick
asked on
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_Intersectio
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.
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_Intersectio
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.
ASKER
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?
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.
ASKER
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help, muzzy!
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