November 12, 2013 at 12:59 pm
Hello people. It's my first post with this account due to the fact that my previous email was shut down. I'm facing the following problem.
I have a table that has 32 rows (its a static table for look-up). Now The table has the structure i have on the attached xlsx and pdf file (they are the same).
This shows the coordinates i captured from an image (in the png file) for each tooth. What I am trying to do is when inside my application the user clicks on a specific region (e.g. a certain tooth) an SQL query will run and try based on the coordinates to match the tooth from the table. The problem is on the Y axis where from tooth 1 to 8 (as seen in the pic) the Y values are going from smaller to larger for start and end respectively. but from tooth 8 to 16 (and then from 17 to 24) the condition is reversed and the start point is a larger value on the Y axis than the end point. What can i do ? I tried a query with case statement and a where exists statement, but i didn't seem to get a solution. Please help me. I'll answer all relevant questions in order to find a solution for it.
Thank you in advance.
November 12, 2013 at 1:05 pm
Post the query you've been working on so we can see it.
Mark
November 12, 2013 at 1:10 pm
Flip the start and end positions where start >= end so you can use a "standard query" and add constraints to ensure start <=end.
November 12, 2013 at 1:27 pm
i was thinking something like that
SELECT TOP 1000 [ID]
,[ToothNumber]
,[startX]
,[endX]
,[startY]
,[endY]
,[Quadrant]
,[QuadrantTooth],
case
when 230 > starty then
(SELECT * FROM [RC_EMA].[dbo].[70_DentalMapping] WHERE
52 > STARTX AND 52 < ENDX AND 230 > ENDY AND 230 < STARTY)
when 230 < starty then
(SELECT * FROM [RC_EMA].[dbo].[70_DentalMapping] WHERE
52 > STARTX AND 52 < ENDX AND 230 < ENDY AND 230 > STARTY)
end
FROM [RC_EMA].[dbo].[70_DentalMapping]
@LutzM : what is your proposal, based on the query i have provided ? Or you are thinking something completely different ?
November 12, 2013 at 1:57 pm
Hi
I was thinking geometry, but that would be overkill :-). Another option would be to add some calculated columns for minx, maxx, miny and maxy.
Having a look at the geometries that your coordinates produce does show that you have a couple of overlaps between 7 and 8, 22 and 23 and 27 and 28
create table #toothmap (
id int identity primary key,
ToothNumber int,
StartX int,
EndX int,
StartY int,
EndY int,
Quadrant int,
QuadrantTooth int,
minX as case when startx <= endx then startx else endx end,
maxX as case when startx > endx then startx else endx end,
minY as case when starty <= endy then starty else endy end,
maxY as case when starty > endy then starty else endy end,
bBox geometry
)
insert into #toothmap (ToothNumber,startX,endX,startY,endY,Quadrant,QuadrantTooth)
values
(1,47,78,252,222,1,8),
(2,43,79,214,191,1,7),
(3,50,87,179,158,1,6),
(4,64,98,144,123,1,5),
(5,79,115,113,97,1,4),
(6,98,125,88,78,1,3),
(7,121,146,43,66,1,2),
(8,144,178,32,65,1,1),
(9,182,212,32,63,2,1),
(10,219,240,45,69,2,2),
(11,234,261,73,89,2,3),
(12,246,282,100,114,2,4),
(13,263,297,129,146,2,5),
(14,272,308,159,180,2,6),
(15,282,313,191,219,2,7),
(16,279,310,228,254,2,8),
(17,280,312,279,310,3,8),
(18,283,310,319,345,3,7),
(19,279,309,352,387,3,6),
(20,257,293,393,422,3,5),
(21,236,270,425,451,3,4),
(22,225,249,452,474,3,3),
(23,206,227,462,482,3,2),
(24,183,205,468,488,3,1),
(25,160,182,469,492,4,1),
(26,142,155,456,484,4,2),
(27,113,135,474,452,4,3),
(28,92,123,452,429,4,4),
(29,71,104,424,395,4,5),
(30,56,88,391,362,4,6),
(31,49,86,353,321,4,7),
(32,49,82,314,278,4,8)
update #toothmap
set bBox = Geometry::STGeomFromText('POLYGON ((' +
cast(startX as varchar(3)) + ' ' + cast(startY as varchar(3)) + ', ' +
cast(startX as varchar(3)) + ' ' + cast(endY as varchar(3)) + ', ' +
cast(endX as varchar(3)) + ' ' + cast(endY as varchar(3)) + ', ' +
cast(endX as varchar(3)) + ' ' + cast(startY as varchar(3)) + ', ' +
cast(startX as varchar(3)) + ' ' + cast(startY as varchar(3)) + '))'
,0)
-- Will show map in spatial results tab
select *
from #toothmap
-- select on calculated columns
select *
from #toothmap
where 50 between minX and maxX and 231 between minY and maxY
-- select on geometry
select *
from #toothmap
where bBox.STContains(Geometry::Point(50,231,0)) = 1
-- determine overlapping tooth maps
select a.toothNumber, b.toothNumber
from #toothmap a
inner join #toothmap b on a.id <> b.id and a.bBox.STIntersects(b.bBox) = 1
drop table #toothmap
November 12, 2013 at 2:07 pm
@Kyr_Soronas: I though about an approach towards what mickyT came up with, but just without the "fancy geometry stuff" (@mickyT: no offence intended 😉 )
However, instead of using computed columns, I would update the current table and add an instead of trigger that would store the values in the appropriate column.
PS: @mickyT: I still like the graphical display though.
November 12, 2013 at 2:25 pm
LutzM (11/12/2013)
@Kyr_Soronas: I though about an approach towards what mickyT came up with, but just without the "fancy geometry stuff" (@mickyT: no offence intended 😉 )However, instead of using computed columns, I would update the current table and add an instead of trigger that would store the values in the appropriate column.
PS: @mickyT: I still like the graphical display though.
Yep ... geometry is definitely overkill:-D The performance wouldn't be that great, not that it would have a large impact on such a small table (1ms vs 3ms).
I wasn't going to post the geometry part of it until I saw the overlaps and thought it would be easier to show them that way rather than try to describe it.
November 12, 2013 at 4:50 pm
mickyT (11/12/2013)
LutzM (11/12/2013)
@Kyr_Soronas: I though about an approach towards what mickyT came up with, but just without the "fancy geometry stuff" (@mickyT: no offence intended 😉 )However, instead of using computed columns, I would update the current table and add an instead of trigger that would store the values in the appropriate column.
PS: @mickyT: I still like the graphical display though.
Yep ... geometry is definitely overkill:-D The performance wouldn't be that great, not that it would have a large impact on such a small table (1ms vs 3ms).
I wasn't going to post the geometry part of it until I saw the overlaps and thought it would be easier to show them that way rather than try to describe it.
So what would you do if you were me ? i'm not THAT good in SQL (using geometry etc)...so I would appreciate very much if you could simplify it for me.
Thanks again...
November 12, 2013 at 6:05 pm
Kyr_Soronas (11/12/2013)
mickyT (11/12/2013)
LutzM (11/12/2013)
@Kyr_Soronas: I though about an approach towards what mickyT came up with, but just without the "fancy geometry stuff" (@mickyT: no offence intended 😉 )However, instead of using computed columns, I would update the current table and add an instead of trigger that would store the values in the appropriate column.
PS: @mickyT: I still like the graphical display though.
Yep ... geometry is definitely overkill:-D The performance wouldn't be that great, not that it would have a large impact on such a small table (1ms vs 3ms).
I wasn't going to post the geometry part of it until I saw the overlaps and thought it would be easier to show them that way rather than try to describe it.
So what would you do if you were me ? i'm not THAT good in SQL (using geometry etc)...so I would appreciate very much if you could simplify it for me.
Thanks again...
Given that the toothmap table is likely to be static, I would go for making the data fit for the query.
This can be done with either a trigger that Lutz suggested, the computed columns I suggested or the easiest way of creating table with the start values being less than the end value.
Assuming that the a queried coordinate should only return a single tooth, I would also remove the overlaps between the teeth.
Once you have the columns with a min/max configuration, the query is
select *
from toothmap
where @x between minx and maxx and @y between miny and maxy
Sorry for confusing things with the posting of geometry stuff;-)
Thinking about this a bit more, while the geometry stuff I posted will work for queries, the map that is shows is inverted on the y axis. Images measure from the top left (y down), whereas geometry measures from the lower left (y up)
November 13, 2013 at 3:43 pm
MickyT !!! you're a savior...it works...and I only had to fix 3 overlapping variables !!!! Thank you all for your time and a very special thanks to Mick....a life saver !!! remind me to buy you guys beers!!!
November 13, 2013 at 3:52 pm
No problem ... glad to help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply