December 10, 2009 at 11:39 am
int pnpoly(int npol, float *xp, float *yp, float x, float y)
{
int i, j, c = 0;
for (i = 0, j = npol-1; i < npol; j = i++)
{
if ((((yp <= y) && (y < yp[j])) || ((yp[j] <= y) && (y < yp))) &&
(x < (xp[j] - xp) * (y - yp) / (yp[j] - yp) + xp))
c = !c;
}
return c;
}
For each point (x,y) in my Locations table, I have to check it is inside the polygon using the above method.
I have to convert and develop a stored procedure which returns all the points inside the polygon.
My idea is to pass vertices of the polygon as xml, use cursor1 to loop through each point i fetch from Locations table, use cursor2 to loop through the vertices of polygon and use the above logic inside the second loop.
Can somebody give an example of how to use multiple cursors if possible? Or Can you give me a better idea to solve this?
December 10, 2009 at 11:54 am
Can you? Yes.
Should you? No.
Please read these two articles:
http://www.sqlservercentral.com/articles/T-SQL/66097/
http://www.sqlservercentral.com/articles/T-SQL/66494/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2009 at 11:55 am
Just put the formula in your Where clause. No need for cursors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 10, 2009 at 11:59 am
If you could put the checking method into SQL or some kind of English-based pseudo-code, I would be happy to show you how to do it without any cursors at all.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2009 at 12:50 pm
Seeing as this is SQL 2008, why not just use the built-in geometry data type and one of the built-in geometry functions that can calculate whether a point is inside an object or not.
It's going to be quicker and probably more accurate than rolling your own geometry routines.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2009 at 6:04 pm
Thank you so much guys. Without checking your replies, I went on to use cursors. The c++ code I posted didn't make sense to me completely and I used an other algorithm.
Please find attached a text file in which I have written functions. I know you will hate me after looking at my code. It is taking crapy amount of time. Please suggest me how I can improve it.
I will also look into GilaMonster's suggestion of using geometry datatype and in-built functions.
December 11, 2009 at 12:26 am
joebadguy0000 (12/10/2009)
Thank you so much guys. Without checking your replies, I went on to use cursors. ......It is taking crapy amount of time. Please suggest me how I can improve it.
Yes, well perhaps you should have read our replies to your request for help first?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 11, 2009 at 7:35 am
For what it's worth, I come from a structured programming and OOP background myself (mostly VB, VB.NET, ASP.NET, etc.).
But when it comes to SQL and set-based programming, I take a completely different mindset. I have to. SQL is a completely different animal, and your traditional structured code will likely not help you. You almost have to "unlearn" everything you know about structured programming.
While loop structures are a necessity in traditional programming, they should be considered nuclear waste when it comes to SQL.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 11, 2009 at 8:08 am
Oh no.. my miscommunication... I have checked your reply BarryYoung, I didn't check GilaMonster's though.. you wanted me to post the code.. that is when I went on to write it so that you would get the whole picture..
My another mistake 🙁 .. i am using sql server 2005 and i posted it in wrong place.. and GilaMonster, I cannot use geometry data type..
Thank you Ray.. most I of the times, I needed only simple queries.. I should make an effort to learn it though..
Joe
December 11, 2009 at 9:59 am
ok.. i think i made some improvement here if anybody is still with me.. please find the attachment.. got rid of one cursor.. time reduced from 56sec to 46sec for just around 60000 records in Locations table (yeah.. u can laugh).. still gottu find a way to get rid of the other cursor..
and I don't see an obvious way.. i need two consecutive records at a time to help do the math..
(ignore the c++ code i posted initially...)
Joe
December 11, 2009 at 11:09 am
I haven't taken the time to dissect your code, but I did want to write my thoughts when it came to attacking set-based vs. structured programming.
Most traditional structured languages are designed to follow an instruction set (like step-by-step instructions in a cookbook, for example). That's okay when dealing with instructions. SQL, however, is designed to work with data. It does not make sense to go through and process it a line at a time; this is very inefficient and ineffective.
Let me try this analogy on you: let's say you have a spreadsheet with 1,000 records. You need to make a change -- the same change -- to these records. Would you rather do it one line at a time, or would you rather use a mass update process that will do it all at once?
Maybe some of you out there who have more SQL experience than I do -- and there are a LOT of you out there 😉 -- can elaborate on this further, but that's pretty much how I attack a database issue as opposed to a structured programming issue.
Enough rambling from me. Hope this helps! Good luck!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
December 11, 2009 at 11:29 am
Ray K (12/11/2009)
I haven't taken the time to dissect your code, but I did want to write my thoughts when it came to attacking set-based vs. structured programming.Most traditional structured languages are designed to follow an instruction set (like step-by-step instructions in a cookbook, for example). That's okay when dealing with instructions. SQL, however, is designed to work with data. It does not make sense to go through and process it a line at a time; this is very inefficient and ineffective.
Let me try this analogy on you: let's say you have a spreadsheet with 1,000 records. You need to make a change -- the same change -- to these records. Would you rather do it one line at a time, or would you rather use a mass update process that will do it all at once?
Maybe some of you out there who have more SQL experience than I do -- and there are a LOT of you out there 😉 -- can elaborate on this further, but that's pretty much how I attack a database issue as opposed to a structured programming issue.
Enough rambling from me. Hope this helps! Good luck!
The key to efficient SQL is to think in columns, not rows.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 11, 2009 at 11:35 am
OK, here's how to implement your inner function without Cursors:
CREATE function [dbo].[InsidePolygon] (@vertices xml, @x float,@y float) returns bit
begin
DECLARE @b-2 BIT
;WITH PolygonVertices AS (
SELECT
p.n.value('X[1]', 'FLOAT') AS xp,
p.n.value('Y[1]', 'FLOAT') AS yp,
COUNT(*) OVER(PARTITION BY 0) AS Cnt,
'foo' AS Dummy
FROM @vertices.nodes('Root/Point') AS p(n)
)
, OrderedVertices AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY Dummy) AS Num
FROM PolygonVertices
)
SELECT @b-2 = CASE WHEN EXISTS(
SELECT *
FROM OrderedVertices AS j
JOIN OrderedVertices AS i ON i.Num = (j.Num % j.Cnt) + 1
WHERE ( (i.yp <= @y) AND (@y < j.yp)
OR
(j.yp <= @y) AND (@y < i.yp)
)
AND
(
@x < (j.xp - i.xp) * (@y - i.yp) / (j.yp - i.yp) + i.xp
)
) THEN 0
ELSE 1 END
return 1
end
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 11, 2009 at 11:40 am
I notice that you already eliminated the Cursor in the outer function, so combining these should eliminate all of the Cursors. I suggest that you try that and let us know how it is. There are a couple more performance improvements that can probably be made after that, as embedding these kind of functions in multi-row SELECTS is fairly inefficient even without Cursors..
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 13, 2009 at 9:17 pm
I am surprised that nobody suggested reusing the existing code here. In any case T-SQL is not the place to do compute-intensive stuff with vertices and points. This routine belongs in a .NET routine - T-SQL was extended to support objects written in .NET languages for exactly this reason - in fact the 'native' geometry types are CLR user-defined types.
@Barry: COUNT(*) OVER (PARTITION BY 0)? Is this different from COUNT(*) OVER()? 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply