By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,617 Members | 1,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,617 IT Pros & Developers. It's quick & easy.

Search for surrounding suburbs query?

P: 3
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
Apr 3 '09 #1
Share this Question
Share on Google+
3 Replies


BeemerBiker
P: 87
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
Apr 3 '09 #2

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


Here is the code:
Expand|Select|Wrap|Line Numbers
  1.             Dim radius As Double
  2.             radius = 5
  3.             radius = (radius * 0.621371192) ^ 2
  4.             Dim long2 As Double
  5.             Dim lat2 As Double
  6.  
  7.             If tb_suburb.Text <> "-- All Suburbs --" Then
  8.  
  9.  
  10.  
  11.                 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="
  12.                 Dim sql As String = "SELECT * FROM subpcstate " & vbCr & vbLf & " WHERE suburb = '" & tb_suburb.Text & "'"
  13.                 Dim conn As New OleDbConnection(connectionString)
  14.                 Dim cmd As New OleDbCommand(sql, conn)
  15.  
  16.                 ' Open the connection, and execute the SQL statement. 
  17.                 cmd.Connection.Open()
  18.                 Dim reader As OleDbDataReader = cmd.ExecuteReader()
  19.                 If reader.Read() Then
  20.                     long2 = (reader.GetDouble(4))
  21.                     lat2 = (reader.GetDouble(5))
  22.                End If
  23.  
  24.                 ' Close the reader and the connection 
  25.                 reader.Close()
  26.                 cmd.Connection.Close()
  27.                 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"
  28.             Else
  29.                 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"
  30.             End If
Apr 3 '09 #3

P: 3
Sorry. I will make sure I dont forget in the future. Thanks for correcting.
Apr 5 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.