Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cosmicgazer
Starting Member
2 Posts |
 Posted - 2011-04-23 : 05:03:39
|
I am trying to convert the below VBA sub procedure to T-SQL. I am new to MS SQL Server and would appreciate any assistance.This script basically takes (x, y) parameters from a source table and processes the coordinates into the below spatial algorithm. The output is then written to destination table.As the source table contains several millions rows of data, the processing rate is an issue !Ideally if the script can be adopted into a stored procedure this could be automated.----------------------------------------------------------- VBA Script -Sub CreateCentroidTablesNew1() Dim tblRegion As String Dim numRegion As Integer Dim regionID As Integer Dim numVertex As Long Dim vertexID As Long 'Dim openDynaset As String Dim x As Double Dim y As Double 'tblRegion = "dbo_westminster_const_region6" '' adapt for user input ''define recordsetDim dbDatabaseToRead As DAO.DatabaseDim boundaryLine As RecordsetSet dbDatabaseToRead = CurrentDbSet boundaryLine = dbDatabaseToRead.OpenRecordset(tblRegion, dbOpenDynaset)'' x = 0 y = 0 numRegion = DMax("id", tblRegion) - DMin("id", tblRegion) + 1 ' later subtract DMin' MsgBox numRegion & " regions found" regionID = DMin("id", tblRegion) ' later adapt to DMin For regionID = 87 To DMax("id", tblRegion) numVertex = DMax("RunningCounterModID", tblRegion, "id =" & regionID) - DMin("RunningCounterModID", tblRegion, "id =" & regionID) + 1' MsgBox numVertex & " vertices found for region " & regionID vertexID = DMin("RunningCounterModID", tblRegion, "id =" & regionID) While vertexID < DMax("RunningCounterModID", tblRegion, "id =" & regionID) boundaryLine.FindFirst "[RunningCounterModID]=" & vertexID & " AND [id] =" & regionID x = x + boundaryLine![X-Coordinate] y = y + boundaryLine![Y-Coordinate] ' x = x + DLookup("[X-Coordinate]", tblRegion, "id =" & regionID & " AND RunningCounterModID =" & vertexID)' MsgBox "X Centroid of region " & vertexID & " = " & x' y = y + DLookup("[Y-Coordinate]", tblRegion, "id =" & regionID & " AND RunningCounterModID =" & vertexID)' MsgBox "Y Centroid of region " & vertexID & " = " & y Debug.Print "Lookup - VertexID: (" & vertexID & "), X-Coordintate: (" & x & "), Y-Coordinate: (" & y & ")" vertexID = vertexID + numVertex / 1000 Wend x = x / numVertex y = y / numVertex Dim dbDatabaseToWrite As DAO.Recordset Set dbDatabaseToWrite = dbDatabaseToRead.OpenRecordset("centroid_table_devolved_const") '' write to table dbDatabaseToWrite.AddNew dbDatabaseToWrite("id").Value = regionID dbDatabaseToWrite("X-Centroid").Value = x dbDatabaseToWrite("Y-Centroid").Value = y dbDatabaseToWrite.Update Debug.Print "Writing to table - RegionID: (" & regionID & "), XCoordintate: (" & x & "), YCoordinate: (" & y & ")" Next regionIDEnd Sub-----------------------------------------------------------Many Thanks Help !  |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-23 : 07:14:56
|
| While theoretically someone should be able to look at the VB code and figure out the logic and translate it to T-SQL with just the information you have provided, the task would be much easier for them if you can provide some more information. Brett's post here has some guidelines: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx. Mainly it is the table DDL's, sample data and expected output that would help.I suspect that most people who respond to questions on this forum spend no more than 5 or 10 minutes on a single post, and many of them won't respond if it takes a long time for them to understand the question. And, it is precisely those people you do want responses from, because many of them have a deep level of understanding of SQL that you or I may never achieve in our lifetimes. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
cosmicgazer
Starting Member
2 Posts |
Posted - 2011-04-25 : 07:39:03
|
quote:
Originally posted by sunitabeck
While theoretically someone should be able to look at the VB code and figure out the logic and translate it to T-SQL with just the information you have provided, the task would be much easier for them if you can provide some more information. Brett's post here has some guidelines: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx. Mainly it is the table DDL's, sample data and expected output that would help.I suspect that most people who respond to questions on this forum spend no more than 5 or 10 minutes on a single post, and many of them won't respond if it takes a long time for them to understand the question. And, it is precisely those people you do want responses from, because many of them have a deep level of understanding of SQL that you or I may never achieve in our lifetimes.
|
|
|
|
|
|
|
|
|
Convertion of VBA module to t-SQL