HUGE number and SQL functions 2

[trix13]

Okay I am creating a zip code search page for our site. I have a function and a procedure in my SQL Server database that work just fine. However as luck would have it I've been instructed to make this process work through a dll I create in VB 6 instead of relying on our SQL server. Okay. To convert the work SQL is doing, it appears it's going to take 5 functions in VB. My first and biggest problem is that my formula requires the use of a HUGE number, over 8 billion, to do the math so to speak. I can't find anything in VB that will hold a number of that size without an overflow error. I've looked up every type of numeric I can use, I've cast the number, I know the limits and all. Does this mean there is nothing in VB that will allow me to use this formula? I mean do I have to re-configure the whole process of finding the zip codes with another formula because VB can't do what SQL does? I'm more inclined to think I don't know what I'm doing in VB. lol! Any suggestions please?

 

[gmmastros]

try using the Double data type. It looks like it will allow you the size and precision you need.

Can you please explain why you need such a huge number. I 'smell' a performance hog, and there may be a better way than what you are currently contemplating.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

I actually did try the double. I think you're idea of explaining why might be better. LOL. Is it okay if I post the two sql chunks of code doing the work I now want VB to do even tho this is the VB forum?

-trix
"Wiggle your big toe.

 

[gmmastros]

I think it's fine to do that. After all, you want to convert SQL Code to VB code, right? If you wanted to do the reverse, then I would suggest you post in the SQL Server programming forum.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

George, I'm brain dead. I'm at home, not at work where my connection to my SQL Server and all my code is. What a dolt. May I post tomorrow morning? Any chance you'll be online at some point tomorrow during the day?

-trix
"Wiggle your big toe.

 

[gmmastros]

I'll be here. And so will many others. There are a lot of sharp people here at Tek-Tips.

Can you explain why you need such a huge number?
Also, what type of Zip Code search are you doing?
Why do you need to make this work through a dll instead of sql server?

Is there something in the formula that requires you to use such a large number? Is the formula meant to calculate distances?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

Thank you.
The formula used to calculate the distance is at one point using large numbers to include one upwards of 8 billion.
I am trying to create a web ap for users to enter a zip code and find all clients within 50 miles of that zip code. It does not have to be PRECISE as some ways of finding this information I have found are very much so. I just need all clients with zip codes falling within 50 miles of the one entered.
The head programmer said it would be better based on our standard ops for the work to be done thru my dll rather than on the SQL server itself. Other than to say I'm not the boss I can't explain that one entirely.

And yes you're right, it is in the formula calculating the distances where these large numbers are being used. I have an up to date table of zip codes including their latitude and longitude.

-trix
"Wiggle your big toe.

 

[gmmastros]

Are the latitude and Longitude in the WGS 84 projection? I have SQL Code and VB Code to calculate distance in those projections. They are very accurate and require nothing more than a float (in SQL Server) or a double in VB.

In fact, if you are using the WGS coordinate system, the 5th digit of precision represents 30 feet (approximately... in the continental u.s.), so a single would suffice.

Your head programmer is wrong regarding the calculations. Trust me. Unfortunately, you head in to murky waters if you try to suggest to your head programmer that he is wrong, so best to avoid that.

Here's my suggestion, for tomorrow morning:

Speak with your head programmer. Find out why he wants this done VB side. I suspect he will claim performance issues. Find out what acceptable performance means from him. Tell him that you may be able to improve performance and ask for an hour or 2 to work on it (SQL Server side). If you can't get the performance to be acceptable within that time frame, then you will code it in VB instead.

I can almost guarantee that the VB processing is going to be slower than the SQL Server processing. (assuming it's done properly)

The trick is to ONLY calculate the distances for those zipcodes that could 'reasonbly' be included in the result set. For example, you can immediately discard lat/lons that are greater than 2 degrees (in either direction) of the zip code your are searching on. This will eliminate 99% of the data, and therefore cause your search to be many times faster.

If you are 'allowed' to work on the stored procedure, then I suggest you post your code in the SQL Server Programming forum. We'll help you optmimize it. If you are forced to do your work in VB, then post in this thread.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

George that helps tons. Here's the situation. Because of the way they've developed their software and subsequent websites, the only thing SQL Server handles is the actual data. They have done all procedures, SQL calls, you name it, in VB. I spoke with both head pgmer and my direct boss. Apparently changes are or will be frequently made to the SQL database and they are concerned that my one or two lone functions and procedures could get, I don't know, overwritten or lost somehow. Here's my thought. I will post the SQL code tomorrow to the SQL forum. However I would also like to learn how to accomplish it in VB. If I could show them both ways and give my opinion on which way to go, perhaps they'll see the value of doing it in SQL. I can tell you now tho that I at least need to find out how to do it in VB whether I wind up having to or not. I'll post my SQL code here tomorrow and perhaps you can lead me in a better direction than the formulas I'm currently using. I can't thank you enough for your help and time.

-trix
"Wiggle your big toe.

 

[gmmastros]

I just did some testing on a zip code table. I can return all the zipcode within 50 miles of a given zipcode in less than 0.01 seconds.

Ultimately, you need clients (not zipcode) within 50 miles, so you would return different data. However, determining the zipcodes within 50 miles of a given zipcode is fast. Joining to a client table on zipcodes will probably NOT slow this down.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

Are you doing this in SQL or in VB right now?

-trix
"Wiggle your big toe.

 

[gmmastros]

SQL Server.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Here's the problem, as I see it. Round trips to the SQL Server are expensive (time consuming).

Assuming you are allowed to return a subset of all zip codes (within +/- 1 degree in each direction), you still need to perform the calculations in VB. End result, a list of zip codes that fall within 50 miles of a give zip code.

Now, here's the problem. How do you get the clients that fall within those zip codes. When I ran my stored procedure for zip code 10001 (New York City), it returned 1173 zip codes. Now you need to go back to the server to get the clients that fall within those zip codes. Even in rural areas, this is likely to result in a couple hundred zip codes. So, you will have to create one ugly looking database call that looks like...

Select fields
From clients
Where zipcode In ('10001','10002','10003', etc... a thousand more times)

To calculate distances in VB, you can use these functions...

Public Function CalculateDistance(ByVal StartLongitude As Single, ByVal StartLatitude As Single, ByVal EndLongitude As Single, ByVal EndLatitude As Single) As Single
    
    Dim DeltaX As Single
    Dim DeltaY As Single
    Dim DeltaXMeters As Single
    Dim DeltaYMeters As Single
    Dim MetersPerDegreeLong As Single
    Dim CenterY As Single
    
    DeltaX = Abs(EndLongitude - StartLongitude)
    DeltaY = Abs(EndLatitude - StartLatitude)
    CenterY = (StartLatitude + EndLatitude) / 2
    MetersPerDegreeLong = MetresPerDegreeLong(CenterY)
    DeltaXMeters = DeltaX * MetersPerDegreeLong
    DeltaYMeters = DeltaY * 111113.519
    CalculateDistance = Sqr(DeltaXMeters * DeltaXMeters + DeltaYMeters * DeltaYMeters) / 1609.344

End Function

Private Function MetresPerDegreeLong(ByVal Latitude As Single)
    
    Dim gEARTH_CIRCUM_METRES As Single
    
    gEARTH_CIRCUM_METRES = 6378007 * 2 * 3.14159265

    MetresPerDegreeLong = (Cos(Latitude * (3.14159265 / 180)) * gEARTH_CIRCUM_METRES) / 360
    
End Function

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I made a mistake. Sorry. It took 0.1 seconds to return the zip codes, not 0.01 seconds. I guess I got carried away with the zeros.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Just to clarify, we are talking about Microsoft SQL Server 2000, right?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

Okay I found some notes here at home. This is the user defined function in SQL I was using:

CREATE Function dbo.CalculateDistance(@Longitude1 Float, @Latitude1 Float, @Longitude2 Float, @Latitude2 Float) Returns Float
AS
Begin
Declare @Deltax Float
Declare @Deltay Float
Declare @DeltaXMeters Float
Declare @DeltaYMeters Float
Declare @MetersPerDegreeLong Float
Declare @CenterY Float
Declare @gEarth_Circum_Meters Float

Set @gEarth_Circum_Meters Float = 6378007 * 2 * PI
Set @Deltax = Abs(@Longitude2 - @Longitude1)
Set @DeltaY = Abs(@latitude1 - @Latitude2)
Set CenterY = (@Latitude1 + @Latitude2) / 2
Set @MetersPerDegreeLong = (Cos(@CenterY * (PI()/180)) * @gEarth_Circum_Meters/ 360
Set @DeltaXMeters = @DeltaX * @MetersPerDegreeLong
Set @DeltaYMeters = @DeltaY * 111113.519

Return Sqrt(@DeltaXMeters * @DeltaXMeters + @DeltaYMeters * @DeltaYMeters) / 1609.344

End

And that's just the calculation. I don't even know if you want to see the select statement or stored procedure that pulls everything together. Am I hopeless here?


-trix
"Wiggle your big toe.

 

[trix13]

Just to clarify, we are talking about Microsoft SQL Server 2000, right?

Yes we are sorry I should have mentioned that.

-trix
"Wiggle your big toe.

 

[trix13]

George looking at your code I'm starting to get the feeling you're the person who helped me make it work in SQL in the first place!

-trix
"Wiggle your big toe.

 

[gmmastros]

Apparently changes are or will be frequently made to the SQL database and they are concerned that my one or two lone functions and procedures could get, I don't know, overwritten or lost somehow.

Here's a thought. Why not create some VB code that will call a sql stored procedure for searching the zip codes. Here's the kicker, and what makes it soooo cool. You include code that will create the functions and procedure before actually using them.

You could write a DLL in VB that checks to see if the functions and procedures exist. If they don't exist, then create them. Here's how...

Option Explicit

Private Sub CreateMetersPerDegree()
    
    Dim cSQL As String
    
    cSQL = ""
    cSQL = cSQL & vbCrLf & "If Not Exists(  Select * "
    cSQL = cSQL & vbCrLf & "                From Information_Schema.Routines "
    cSQL = cSQL & vbCrLf & "                Where Specific_Name = 'MetresPerDegreeLong' "
    cSQL = cSQL & vbCrLf & "                And Routine_Type = 'Function' "
    cSQL = cSQL & vbCrLf & "        ) "
    cSQL = cSQL & vbCrLf & "Create Function MetresPerDegreeLong(@Latitude Float) "
    cSQL = cSQL & vbCrLf & "Returns Float "
    cSQL = cSQL & vbCrLf & "As "
    cSQL = cSQL & vbCrLf & "Begin "
    cSQL = cSQL & vbCrLf & "Declare @gEARTH_CIRCUM_METRES Float "
    cSQL = cSQL & vbCrLf & "    "
    cSQL = cSQL & vbCrLf & "Set @gEARTH_CIRCUM_METRES = 6378007 * 2 * 3.14159265 "
    cSQL = cSQL & vbCrLf & ""
    cSQL = cSQL & vbCrLf & "Return (Cos(@Latitude * (3.14159265 / 180)) * @gEARTH_CIRCUM_METRES) / 360 "
    cSQL = cSQL & vbCrLf & "    "
    cSQL = cSQL & vbCrLf & "End"
    
    Call db.Execute(cSQL)
    
End Sub

Private Sub CreateCalculateDistance()

    Dim cSQL As String
    
    cSQL = ""
    cSQL = cSQL & vbCrLf & "If Not Exists(  Select * "
    cSQL = cSQL & vbCrLf & "                From Information_Schema.Routines "
    cSQL = cSQL & vbCrLf & "                Where Specific_Name = 'CalculateDistance' "
    cSQL = cSQL & vbCrLf & "                And Routine_Type = 'Function' "
    cSQL = cSQL & vbCrLf & "        ) "
    
    cSQL = cSQL & vbCrLf & "Create  Function CalculateDistance(@Longitude1 Float, @Latitude1 Float, @Longitude2 Float, @Latitude2 Float) "
    cSQL = cSQL & vbCrLf & "Returns Float "
    cSQL = cSQL & vbCrLf & "AS "
    cSQL = cSQL & vbCrLf & "Begin "
    cSQL = cSQL & vbCrLf & " "
    cSQL = cSQL & vbCrLf & "    Declare @DeltaX Float "
    cSQL = cSQL & vbCrLf & "    Declare @DeltaY Float "
    cSQL = cSQL & vbCrLf & "    Declare @DeltaXMeters Float "
    cSQL = cSQL & vbCrLf & "    Declare @DeltaYMeters Float "
    cSQL = cSQL & vbCrLf & "    Declare @MetersPerDegreeLong Float "
    cSQL = cSQL & vbCrLf & "    Declare @CenterY Float "
    cSQL = cSQL & vbCrLf & "    "
    cSQL = cSQL & vbCrLf & "    Set @DeltaX = Abs(@Longitude2 - @Longitude1) "
    cSQL = cSQL & vbCrLf & "    Set @DeltaY = Abs(@Latitude2 - @Latitude1) "
    cSQL = cSQL & vbCrLf & "    Set @CenterY = (@Latitude1 + @Latitude2) / 2 "
    cSQL = cSQL & vbCrLf & "    Set @MetersPerDegreeLong = dbo.MetresPerDegreeLong(@CenterY) "
    cSQL = cSQL & vbCrLf & "    Set @DeltaXMeters = @DeltaX * @MetersPerDegreeLong "
    cSQL = cSQL & vbCrLf & "    Set @DeltaYMeters = @DeltaY * 111113.519 "
    cSQL = cSQL & vbCrLf & "    Return Sqrt(@DeltaXMeters * @DeltaXMeters + @DeltaYMeters * @DeltaYMeters) / 1609.344 "
    cSQL = cSQL & vbCrLf & "    "
    cSQL = cSQL & vbCrLf & "End "

    Call db.Execute(cSQL)

End Sub

Public Sub CreateGetZipCodesWithin50Miles()
    
    Dim cSQL As String
    
    cSQL = cSQL & vbCrLf & "If Not Exists(  Select * "
    cSQL = cSQL & vbCrLf & "                From Information_Schema.Routines "
    cSQL = cSQL & vbCrLf & "                Where Specific_Name = 'GetZipCodesWithin50Miles' "
    cSQL = cSQL & vbCrLf & "                And Routine_Type = 'Procedure' "
    cSQL = cSQL & vbCrLf & "            ) "
    
    cSQL = cSQL & vbCrLf & "Create Procedure GetZipCodesWithin50Miles "
    cSQL = cSQL & vbCrLf & "    @ZipCode Integer "
    cSQL = cSQL & vbCrLf & "As "
    cSQL = cSQL & vbCrLf & "SET NOCOUNT ON "
    cSQL = cSQL & vbCrLf & " "
    cSQL = cSQL & vbCrLf & "Declare @Longitude Decimal(9,6) "
    cSQL = cSQL & vbCrLf & "Declare @Latitude Decimal(9,6) "
    cSQL = cSQL & vbCrLf & " "
    cSQL = cSQL & vbCrLf & "Select  @Longitude = Longitude, "
    cSQL = cSQL & vbCrLf & "        @Latitude = Latitude "
    cSQL = cSQL & vbCrLf & "From Zip_Codes "
    cSQL = cSQL & vbCrLf & "Where   ZipCode = @ZipCode "
    cSQL = cSQL & vbCrLf & " "
    cSQL = cSQL & vbCrLf & "Declare @Temp Table(ZipCode Integer, Longitude Decimal(9,6), Latitude Decimal(9,6)) "
    cSQL = cSQL & vbCrLf & " "
    cSQL = cSQL & vbCrLf & "Select  A.ZipCode, "
    cSQL = cSQL & vbCrLf & "        Zip_Codes.City, "
    cSQL = cSQL & vbCrLf & "        Zip_Codes.State "
    cSQL = cSQL & vbCrLf & "From    ( "
    cSQL = cSQL & vbCrLf & "        Select  ZiPCode, Longitude, Latitude "
    cSQL = cSQL & vbCrLf & "        From Zip_Codes "
    cSQL = cSQL & vbCrLf & "        Where   Longitude Between (@Longitude - 1) And (@Longitude + 1) "
    cSQL = cSQL & vbCrLf & "                And Latitude Between (@Latitude - 1) And (@Latitude + 1) "
    cSQL = cSQL & vbCrLf & "        ) A "
    cSQL = cSQL & vbCrLf & "        Inner Join Zip_Codes On A.ZipCode = Zip_Codes.ZipCode "
    cSQL = cSQL & vbCrLf & "Where   dbo.CalculateDistance(@Longitude, @Latitude, A.Longitude, A.Latitude) <= 50 "

    Call db.Execute(cSQL)
    
End Sub

This is a lot of code, I know, and the last one will need to be modified to use the tables you have and return the client information instead of zip codes, but you get the idea.

Either way, I've provided code that calculates distances in VB and SQL Server, so you should have the tools you need to get the job done. Let us know how you make out.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[trix13]

You are brilliant. Thank you so much this is going to help me so much. I appreciate all your help!!!

-trix
"Wiggle your big toe.