How to do search in sql server database for all properties (with long, lat) that fall within selected map area?

How to do search in sql server database for all properties (with long, lat) that fall within selected map area?

• mahima

  exactly!
  is that easy !

  Do you have to consider other variables like the zoom level, view type ie. 3d, 2d, birds eye
  the long1, long2, at1,lat2 would come from the map control boundary, rite
  same could be applied to drawing a rectangle over the map control (alt + left button drag), rite

  thanks
  steph

  
  

• SouLDragooN

  Are you after this

  CREATE PROCEDURE dbo.FindAddressesWithinBounds

  (

  @long1 float
  , @long2 float
  , @lat1 float
  , @lat2 float
  )

  AS

  SELECT
  a.[street number]
  , a.[street name]
  , a.[city]
  , a.[province]
  , a.[long]
  , a.[lat]

  FROM
  dbo.myaddresstablename a

  WHERE
  a.[Lat] >= @lat1 and a.[Lat] <= @lat2 and a.[Long] >= @long1 and a.[Long] <= @long2

  You have to ensure the @lat2 > @lat1 and @long2 > @long1

  John.

  
  
  

• Paul Marriott

  If you put your logic on the "onchangeview" event then whenever the map is panned, zoomed etc you can get the current bounds and make your query. This covers 2D and rectange. Birds eye I belive is quite different. 3D is a little interesting, becouse you can rotate the view you must ensure your bounds are the right way around for your query. Also when you tilt and see a bit of space the mapview gives a less then ideal set of bounds for the data.

  If you interested to see what i'm talking about in 3D mode look at this in 3d (IE with 3d installed click on link getmapview):

  http://www.soulsolutions.com.au/portals/0/ve/VE3DView.htm

  and the thread here

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=900771&SiteID=1

  John.

  
  
  

• km9

  If it helps this will work out the distance in miles between two lon/lat points.

  Lat1 = "58.64433"
  Long1 = "-3.03476"
  Lat2 = "50.06635"
  Long2 = "-5.71496"

  
  DistanceLong = 53*(Long2-Long1)
  DistanceLat = 69.1*(Lat2-Lat1)
  Distance=Sqr((DistanceLat*DistanceLat)+(DistanceLong*DistanceLong))

  The 53 and 63 look strange - but it takes into consideration that the earth is not round so just use these values !

  
  

• Rakesh Luhar

  Its a little old now but this will get you started:

  http://www.viavirtualearth.com/vve/Articles/Clustering.ashx

  Just don't use the clustering parts if you don't want to cluster!

  John.

  
  
  

• nsikes

  Jeff,

  How do i do both methods..actual sql or is there website that has sql code samples

  Thanks

  steph

  
  

• Ragavendra

  to be clearer,

  we have address table ith address which has already been geocoded.

  address table:
  street number
  street name
  city
  province
  long
  lat
  
  User will select map area, then click search properties button. That will call a stored proceure to find the properties (with address) that have are located in that selected map area.
  Im assuming that im passing lnog1, long2, lat1, lat2 (coordinates that ive captured from the selected map) to the stored procedure

  Im assuming this is common function, and would only have to copy logic into our existing stored procedure...

  thanks

  steph

  
  

• Kennon2005

  well,

  you need to provide a plenty more information.

  but i guess you got a sql table like this:

  ID, Name, Street, City, Zipcode, Latitude, Longitude, MainCategory, SubCategory.

  You can search your stuff via perimeter(distance from center of map) or by viewport(visible map view).

  searching via perimeter is a bit more complicated, you need to do some sin() cos() math in your sql select then,

  viewport search is easier, you only need to check that the lat/longs are in your bounding rectangle in the where part of the select.

  
  
  

• fasttrack

  im assuming that i will capture minx,miny,maxX,maxY coordinates from the map, and pass to my property search stored procedure, and search against my address data which has long and lat.

  What is sql to find matches within selected map area passed to sproc

  thanks

  steph

  
  

• cacu

  thanks very much for all your help...as you can tell Im new to the platform.

  what logic would i include in "If you put your logic on the "onchangeview" event "
  i assume this is standard process.

  thanks

  steph

  
  

• PDWLC

  i would like to share with you this function that will find that distance between two latlong points. It is described in detail at this link

  http://www.movable-type.co.uk/scripts/LatLong.html

  cheers.

  
  
  
• How to do search in sql server database for all properties (with long, lat) that fall within selected map area?