returning nearest point

Hello

Using asp ado I am accessing a database with x and y coordinate values. the user enters an x and y coordinate value and I would like the nearest location in the database to be returned. The locations in the database are randomly distributed so sometimes the nearest x,y location might be near the location entered by the user, other times further away.

What I have so far is a SQL query along the lines of the following:

SELECT * FROM mydb WHERE xCoord > (('" & xVal & "')-100) AND xCoord < (('" & xVal & "')+100) AND yCoord > (('" & yVal & "')-100) AND yCoord < (('" & yVal & "')+100)"

what this does is return all locations in a 100units square surrounding the users input location, I have considered 'SELECT TOP 1' in the SQL query with a combination of 'order by' statements but this does not return the nearest point as the TOP1 is not necessarily the nearest location

The solution I believe is in returning the results (with x,y coordinate values) into a JavaScript array and then comparing each of them agains the user input coords using
d = sqrt[(x1-x2)2+ (y1-y2)2]

Any ideas how I would get this to work or any other possibly more simpler suggestions or solutions?

Thank you

mo

 

You could do it in the query, though I couldn't tell you whether it would be better to do it there or on the web server.

Select TOP 1 SQRT(POWER((xCoord - '" & xVal & "'), 2) + POWER((yCoord - '" & yVal & "'), 2)) AS 'Distance' FROM mydb WHERE xCoord > (('" & xVal & "')-100) AND xCoord < (('" & xVal & "')+100) AND yCoord > (('" & yVal & "')-100) AND yCoord < (('" & yVal & "')+100) ORDER BY 'Distance'

 

druidelder

Thank you for your response, I have made a fair bit of progress and almost have it working apart from one area(more of a lack of knowledge I suspect) that I am hoping yourself or anyone might be able to assist with, should this not work I will definately give your suggestion a go as it looks as if it might be an more logical solution.

my question (hopefully fairly straight forward), when outputting database content using the Do until rs.EOF line how can I get a record id for each result?

for example

Do until rs.EOF
%>
<tr><td><%=rs("fruit")%></td></tr>
<%  rs.MoveNext
Loop %>

Code (markup):

might give me the following:

apples
pears
bannanas
strawberries

how do I get:

1 apples
2 pears
3 bannanas
4 strawberries

where the number alongside the fruit is a generated id

thanks

mo

 

Does the table from which you are pulling have a field that is identified as the identity? If so, make sure your query asks for it and then your code could look like:

<td><%=rs("id")%>&nbsp;<%=rs("fruit")%>

That would output the actual ID. If you just want them numbered in sequence then it would look something like:

dim count int
set count = 0
Do Until rs.EOF
count = count + 1
%>
<tr><td><%count%>&nbsp;<%=rs("fruit")%></td></tr>
<% rs.MoveNext
Loop %>

May not be exact, I haven't done old style ASP in years.

 

Thanks druidelder thats exactly what I was after. thank you for your advice and time.

If anyone is interested, I think I may have arrived at a working solution feel free to contact me but please dont expect a text book code sample

regards

mo