Returning fields along with the MIN function 1

[Vulton]

This qry returns the wrong C_JobNum and C_PhaseNumber actualy it always returns the 1st record in the table rather than the record the Min statement is returning. Can someone please show me how to make this work correctly?

SELECT Min(Abs([C_Longitude]- (-117.61583))+Abs([C_latitude]- 33.967205)) AS NearestJobPhase, C_JobNum, C_PhaseNumber FROM dbo_TblContract GROUP BY C_JobNum, C_PhaseNumber

Thanks,
Mark

 

[gmmastros]

Try this...

SELECT Min(SQRT(([C_Longitude]- (-117.61583)) * ([C_Longitude]- (-117.61583)) + ([C_latitude]- 33.967205) * ([C_latitude]- 33.967205))) AS NearestJobPhase, 
       C_JobNum, 
       C_PhaseNumber 
FROM   dbo_TblContract 
GROUP BY C_JobNum, C_PhaseNumber

-George

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

 

[Vulton]

ok here is what I have ...

Select Min(Sqr((C_Latitude - " & SearchLatitude & ") ^ 2 + (C_Longitude - " & SearchLongitude & ") ^ 2)) As FakeDistance, 
            C_JobNum, 
            C_PhaseNumber 
From dbo_TblContract 
Group By C_JobNum, C_PhaseNumber

But I am getting this ...
SearchLatitude SearchLongitude Job Ph FakeDistance
33.99232 -117.35931 5020 9 122.182999892205
33.744774 -117.84869 5020 9 122.584760500754
33.744774 -117.84869 5020 9 122.584760500754
33.73485 -117.85459 5020 9 122.587701212604

5020 and 9 are from the 1st record in the table and not correct. It should look like this ...
SearchLatitude SearchLongitude Job Ph FakeDistance
33.99232 -117.35931 6832 0 122.182999892205
33.744774 -117.84869 6767 1 122.584760500754
33.744774 -117.84869 6767 1 122.584760500754
33.73485 -117.85459 6767 2 122.587701212604

Could this be an Access problem and not a SQL one?

Again thanks for the help,
Mark

 

[gmmastros]

First of all, I'm a little fuzzy because I was very sick yesterday, and still not feeling 100 %.

If I understand correctly, you have a search lat and lon, and you want to return the closes record in the table to the search values.

Try this query...

Select * 
From	dbo_TblContract 
Where	sqrt((Latitude - @Lat) * (Latitude - @Lat) + (Longitude-@Lon) * (Longitude - @Lon)) = 
(

Select 	Min(sqrt((Latitude - @Lat) * (Latitude - @Lat) + (Longitude-@Lon) * (Longitude - @Lon)))
From 	dbo_TblContract )

-George

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

 

[Catadmin]

Try adding a JOIN to a subquery (which contains the Min statement) to your code which matches the longitude and latitude to your jobID. I believe that's where your problem lies.

Happens to me all the time. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Vulton]

George
I hope your feeling better and thanks for the help. I ended up using your method with someone elses math for speed.

This is what I ended up with...

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MySQL As String

Public Function NearestGeoFence(SearchLatitude As Variant, SearchLongitude As Variant) As Variant

If SearchLatitude <> 0 Or SearchLongitude <> 0 Then

    Set MyDB = CurrentDb
    
    MySQL = "SELECT * From dbo_TblContract "
    MySQL = MySQL & "Where (Abs([C_Longitude] - " & SearchLongitude & ") "
    MySQL = MySQL & "+ Abs([C_latitude] - " & SearchLatitude & ")) "
    MySQL = MySQL & "= (Select Min(Abs([C_Longitude] - " & SearchLongitude & ") "
    MySQL = MySQL & "+ Abs([C_latitude] - " & SearchLatitude & ")) "
    MySQL = MySQL & "From dbo_TblContract)"
   
    Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    
    NearestGeoFence = MyRS.Fields("C_JobNum").Value & " " & MyRS.Fields("C_PhaseNumber").Value

End If

End Function

Catadmin I understand what your saying to use but I don't know how to do use a Join with a subquery. Do you think it would be faster than the WHERE statement I used?

Thanks,
Mark

 

[gmmastros]

Mark,

Unfortunately, your abs value trick may give the wrong results because at different latitudes, the difference in longitude results in different distances. It's kinda hard to explain.

Think of it this way... At the equator, 1 degree Longitude (along the equator) is much longer than 1 degree Longitude at the north (or south) pole. Imagine standing at the north pole. Then, move 1 foot to the left. Now walk in a circle around the north pole. You will have travelled 360 degrees longitude, but would only have travelled a distance of about 3 feet.

Let's use an example.

Suppose your search lat and lon are 0 and 0.

Also suppose you have a point at 0,10 and another point at 5, 6

Which point is closer? Let's see.

Abs(0 - 0) + Abs(10 - 0) = 10
Abs(5-0) + Abs(6-0) = 11

Your algorithm would put the 0,10 point closer.

Using the pythagorean theorem for both.

Sqrt( (0-0)^2 + (10-0)^2) = Sqrt(100) = 10
Sqrt( (5-0)^2 + (6-0)^2) = Sqrt(61) = 7.8

My algorithm would correctly put the 5,6 point closest.
Unfortunately, to get accurate results, you will need to use the slower formula.


-George

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

 

[Vulton]

Even if I am only conserned (for now) with points that are in southern California? Within say 300 miles of eachother max.

I'm new at this whole GPS phone locate the employee thing but that Square root is a killer on the speed.

Mark

 

[gmmastros]

Yes. Even if the points are all in Southern California. You will still have the problem.

There are ways to improve the problem. Bascially, your algorithm will prefer matches that are due North, south, east or west of the given search. By using the sqrt function, all directions, including NW, SW, SE, and NE will all have an equal playing ground.

That being said, there are ways to improve the situation. Mostly, this method will require a lot of extra coding, but will speed up the calculations. Here's the general idea.

First start off searching a relatively small area. Let's say +- .1 degree latitude by +- .1 degree longitude.

Like this...

Select * 
From   dbo_TblContract 
Where  sqrt((Latitude - @Lat) * (Latitude - @Lat) + (Longitude-@Lon) * (Longitude - @Lon)) = 
       (
       Select Min(sqrt((Latitude - @Lat) * (Latitude - @Lat) + (Longitude-@Lon) * (Longitude - @Lon)))
       From   dbo_TblContract 
       Where  Latitude >  @Lat - [!].1[/!]
              And Latitude < @Lat + [!].1[/!]
              And Longitude > @Lon -[!].1[/!]
              And Longitude < @Lon + [!].1[/!]
		)
        And Latitude >  @Lat - [!].1[/!]
        And Latitude < @Lat + [!].1[/!]
        And Longitude > @Lon -[!].1[/!]
        And Longitude < @Lon + [!].1[/!]

If this still takes a long time, then maybe try 0.5 instead. The problem with this approach is that you may not get any matches because all of matches are too far away. So, you'll need to code this such that if no points are returned, you increase the size of the search distance ([!].1[/!]).

You will also want to make sure you have an index on your table that includes latitude and longitude. With Access, I'm not sure how to do that, but I'm sure it won't be that hard to find either.

So... sure... it'll take extra coding, but the performance increase will be well worth it.



-George

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

 

[Vulton]

AWSOME,

Thank you again George I think that did it ... an acceptable speed with great results. I will have to play with the ".1" a bit to see what works best but your genius has helped me emensly and I learned a lot to.

Posting code again for people with the same needs some day..

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MySQL As String

Public Function NearestGeoFence(SearchLatitude As Variant, SearchLongitude As Variant) As Variant

If SearchLatitude <> 0 Or SearchLongitude <> 0 Then

    Set MyDB = CurrentDb
    
    MySQL = "Select * From dbo_TblContract "
    MySQL = MySQL & "Where  sqr((C_Latitude - " & SearchLatitude & ") * (C_Latitude - " & SearchLatitude & ") "
    MySQL = MySQL & "+ (C_Longitude-" & SearchLongitude & ") * (C_Longitude - " & SearchLongitude & ")) "
    MySQL = MySQL & "= (Select Min(sqr((C_Latitude - " & SearchLatitude & ") * (C_Latitude - " & SearchLatitude & ") "
    MySQL = MySQL & "+ (C_Longitude-" & SearchLongitude & ") * (C_Longitude - " & SearchLongitude & "))) "
    MySQL = MySQL & "From dbo_TblContract "
    MySQL = MySQL & "Where C_Latitude >  " & SearchLatitude & " - .1 "
    MySQL = MySQL & "And C_Latitude < " & SearchLatitude & " + .1 "
    MySQL = MySQL & "And C_Longitude > " & SearchLongitude & " -.1 "
    MySQL = MySQL & "And C_Longitude < " & SearchLongitude & " + .1) "
    MySQL = MySQL & "And C_Latitude >  " & SearchLatitude & " - .1 "
    MySQL = MySQL & "And C_Latitude < " & SearchLatitude & " + .1 "
    MySQL = MySQL & "And C_Longitude > " & SearchLongitude & " -.1 "
    MySQL = MySQL & "And C_Longitude < " & SearchLongitude & " + .1"

    Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    
    NearestGeoFence = MyRS.Fields("C_JobNum").Value & " " & MyRS.Fields("C_PhaseNumber").Value

End If

End Function

Thanks everyone,
Mark

 

[gmmastros]

The thing to consider is that if there are no matches, then you need to increase the .1, making it .2. Run the query again. If there are still no results, then try .3, etc... until you finally get an answer.

Thinking about this a little more.... There may be a more efficient way to do this.

MySQL = MySQL & "Select [!]Top 1[/!] * From dbo_TblContract "
MySQL = MySQL & "Where C_Latitude >  " & SearchLatitude & " - .1 "
MySQL = MySQL & "And C_Latitude < " & SearchLatitude & " + .1 "
MySQL = MySQL & "And C_Longitude > " & SearchLongitude & " -.1 "
MySQL = MySQL & "And C_Longitude < " & SearchLongitude & " + .1 "
MySQL = MySQL & "[!]Order By[/!] sqr((C_Latitude - " & SearchLatitude & ") * (C_Latitude - " & SearchLatitude & ") "
MySQL = MySQL & "+ (C_Longitude-" & SearchLongitude & ") * (C_Longitude - " & SearchLongitude & ")) "

The original method would first calculate the closest point and then would return the record where the distance was the same as the closest point.

This method only calculates the values 1 time, which should result in a faster query but yet still producing the same results.

I urge you to try it out. I think you'll be pleasantly surprised.

-George

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

 

[Vulton]

I tried the Top 1 Order By way and it takes about the same amount of time as the WHERE statement one.

 

[gmmastros]

That's probably because we are narrowing the search to +/- 0.1 degrees latitude and longitude. With more data, the TOP 1 query would perform a lot better. Trust me. In fact... don't trust me. Try it. Remove the filters on latitude and longitude and run the query both ways. The TOP 1 method should be several times faster. Logic holds that if the query is faster with a large data set, then it will also be faster with a small data set, too.

Anyway... I'm glad you finally got this working.

-George

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

 

[Vulton]

I am going to keep using the Top 1 Order By method just in case when I get to altering the ".1" it needs to be bigger or gone.

George really I can't thank you enough =)
Mark