Would like to create site with similar functionality tohttp://www.realtysouth.com/consumer/listing/ListingSearch.aspx?SearchType=map&ListingType=1&ListingSearchID=&RECoJumpSearchID=&action=&defaultlevel=
for our site
How to do search in sql server database for all properties (with long, lat) that fall within selected map area?
thanks
steph
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
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.
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 !
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
Jeff,
How do i do both methods..actual sql ? or is there website that has sql code samples?
Thanks
steph
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.
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
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.
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
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.
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.
