Search for surrounding suburbs query?

[ozboss]

Hello,

Being new to ASP .net and visual studio express 2008 I am not sure how to create a webform that allows the user to select a suburb from a drop down list to search an Access 2007 database of suburbs. I would like to include in the result suburbs that are with a certain radius of the select suburb.

I have the table of suburbs (including latitude and longitudes), a form designed with appropriate dropdown list and a gridview.

the form works fine for an individual suburb, or if the user select --All suburbs-- from the list.

Now to my question...Sorry...

Can some point mein the right direction on how to create a surrounding suburbs search?

I hope this makes sense.

Thanks

[BeemerBiker]

I am guessing you pick one suburb as the starting point and then specify a radius. You would then construct a containment polygon (just that circle in your example). If you pick 30 degrees for each point on the circumference, then you will get a 12 sided polygon or 13 points (each a lat/long) for a closed polygon. Any good high school trig book will have the formulas to calculate the point pairs. You just need to come up with a function to determine if any point from the surburb set of lat/longs lies within your containment polygon (ie: the sets intersect). This kind of assumes that the suburb's point collections are also organized as closed polygons.

Fortunately for me, I used functions in Esri's MapObjects to do just that
;-)

To keep things really simple, you could just use a great circle bearing function to see if the "center" of the suburb is within "X" nautical miles of the origin of your circle. If that is good enough, email me and I will send you a GCB function written in VB-6

HTH

[ozboss]

thanks for reply but I have managed to sort it out.


Here is the code:

            Dim radius As Double
            radius = 5
            radius = (radius * 0.621371192) ^ 2
            Dim long2 As Double
            Dim lat2 As Double
 
            If tb_suburb.Text <> "-- All Suburbs --" Then
 
 
 
                Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & vbCr & vbLf & " Data Source=j:\ymca_good\rluwebsite\App_Data\RLU_Standalone.mdb;" & vbCr & vbLf & " User ID=Admin;Password="
                Dim sql As String = "SELECT * FROM subpcstate " & vbCr & vbLf & " WHERE suburb = '" & tb_suburb.Text & "'"
                Dim conn As New OleDbConnection(connectionString)
                Dim cmd As New OleDbCommand(sql, conn)
 
                ' Open the connection, and execute the SQL statement. 
                cmd.Connection.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader()
                If reader.Read() Then
                    long2 = (reader.GetDouble(4))
                    lat2 = (reader.GetDouble(5))
               End If
 
                ' Close the reader and the connection 
                reader.Close()
                cmd.Connection.Close()
                DS_search.SelectCommand = "SELECT GroupName, OrgName, Region, Suburb, keywords, Disability_keywords, RecordID, Address, Address2, Postcode, activities.activity,longitude,latitude   FROM full_activities where (69.1*([longitude]-" & long2 & ") * cos(" & lat2 & "/57.3))^2 + (69.1*([latitude]-" & lat2 & "))^2 <" & radius & " and (((GroupName LIKE '%' + @groupname + '%') or (keywords LIKE '%' + @keywords + '%')) and (orgname like '%' + @orgname + '%'))order by suburb,groupname"
            Else
                DS_search.SelectCommand = "SELECT GroupName, OrgName, Region, Suburb, keywords, Disability_keywords, RecordID, Address, Address2, Postcode, activities.activity  FROM full_activities where ((GroupName LIKE '%' + @groupname + '%') or (keywords LIKE '%' + @keywords + '%')) and (orgname like '%' + @orgname + '%') and (suburb like '%' + @suburb + '%') and (region like '%' + @region + '%') and (disability_keywords like '%' + @disability_keywords + '%') order by suburb,groupname"
            End If

[ozboss]

Sorry. I will make sure I dont forget in the future. Thanks for correcting.