breeze351
asked on
sql query returning closest value
I'm not sure if is the correct forum and the question may be a little long as I'm trying to give you as much info that I can.
I'm working on a real estate system.
I have a page that asks the user for a street number
and a street name. The end result displays all the address on a avenue between 2 streets.
This in Manhattan so everything above 14th street is a grid.
The file that I'm querying looks something like this:
Bld # Street North South
590 Lexington 42 41
591 Lexington 42 41
595 Lexington 41 40
596 Lexington 41 40
What I'm trying to do is, if the user keys in "592 Lex" find the closest number to (in this case "591") and display the addresses on that block ( 42nd to 41st).
Is there a way to use a sql query and tell it that I want the closest value to the address number?
Thanks
PS.
The code does work if I get a hit on a legit address. The problem with real estate is that you never know how they assign building numbers.
I'm working on a real estate system.
I have a page that asks the user for a street number
and a street name. The end result displays all the address on a avenue between 2 streets.
This in Manhattan so everything above 14th street is a grid.
The file that I'm querying looks something like this:
Bld # Street North South
590 Lexington 42 41
591 Lexington 42 41
595 Lexington 41 40
596 Lexington 41 40
What I'm trying to do is, if the user keys in "592 Lex" find the closest number to (in this case "591") and display the addresses on that block ( 42nd to 41st).
Is there a way to use a sql query and tell it that I want the closest value to the address number?
Thanks
PS.
The code does work if I get a hit on a legit address. The problem with real estate is that you never know how they assign building numbers.
Do you use LIKE for finding a partial match on the street name? If so, you would expect multiple streets returned sometimes. In this case do you want just the closest number for each street to be found?
ASKER
Chaau:
Your solution does will not work. I already explored it.
1) I would have to send some out that I can trust to actually find the latitude and longitude for every building in the data base.
2) The google maps rely on the same thing. You can have a building on a side street and google puts it on the main street.
Terry:
It's not a partial address that I'm looking for.
If I query:
STREET LIKE "LEXINGTON" AND BLDN LIKE "575%"
There is no "575" how does it return "591" as the closest address?
The more I stare out this and your reply might make sense.
1). STREET LIKE "LEXINGTON" AND BLDN LIKE "5%"
This gives me everything on "Lexington" that starts with a "5".
2), Loop through the query results.
3). Save the building ID from the file.
4). After the building ID is greater than "575", I now have the last block.
Does this make sense?
Your solution does will not work. I already explored it.
1) I would have to send some out that I can trust to actually find the latitude and longitude for every building in the data base.
2) The google maps rely on the same thing. You can have a building on a side street and google puts it on the main street.
Terry:
It's not a partial address that I'm looking for.
If I query:
STREET LIKE "LEXINGTON" AND BLDN LIKE "575%"
There is no "575" how does it return "591" as the closest address?
The more I stare out this and your reply might make sense.
1). STREET LIKE "LEXINGTON" AND BLDN LIKE "5%"
This gives me everything on "Lexington" that starts with a "5".
2), Loop through the query results.
3). Save the building ID from the file.
4). After the building ID is greater than "575", I now have the last block.
Does this make sense?
If bldn is a numeric value you can use a range to find the values close to the requested, eg
bldn >= 591-10 and bldn <= 591+10ASKER
Close but no cigar.
Building # is numeric. But your query is going to return multiple address #'s between the range of "581" and "591". I'm looking for the closest one to "585".
The language that I was working in, you could query a file with a key of "585". It would return a partial result, then you could either read back or forward. The original read was just for positioning in the file. This is what I'm trying to do with a sql statement.
I could do it you're way, but then I would have to create an array, save all the data and figure it out from there.
Building # is numeric. But your query is going to return multiple address #'s between the range of "581" and "591". I'm looking for the closest one to "585".
The language that I was working in, you could query a file with a key of "585". It would return a partial result, then you could either read back or forward. The original read was just for positioning in the file. This is what I'm trying to do with a sql statement.
I could do it you're way, but then I would have to create an array, save all the data and figure it out from there.
All you need to do is to order by abs(585-bldn).
that way the first record will be the closest one to the searched value ordered by its distance from the searched value
that way the first record will be the closest one to the searched value ordered by its distance from the searched value
ASKER
Okay, you lost me. How does the "Order by" find the closest # to 592?
"SELECT * FROM MAPFILE WHERE STREET LIKE "LEXINGTON" AND bldn >= 591-10 and bldn <= 591+10 ORDER BY ABS(582)"
This is what I got from your last 2 posts.
So if I've got this correct. I take the "592" that was keyed in, subtract one to build the "AND" statement and then use the "592" in the "ORDER" by and it will return "591" in the query?
If my sql is wrong let me know.
"SELECT * FROM MAPFILE WHERE STREET LIKE "LEXINGTON" AND bldn >= 591-10 and bldn <= 591+10 ORDER BY ABS(582)"
This is what I got from your last 2 posts.
So if I've got this correct. I take the "592" that was keyed in, subtract one to build the "AND" statement and then use the "592" in the "ORDER" by and it will return "591" in the query?
If my sql is wrong let me know.
ORDER BY ABS(582) will not find, but ORDER BY ABS(591-bldn) will. Modify the statement like this:
BTW, the range value (10 in the query above) should be made a value relative to the borough (is it how the suburbs are called in New York?). E.g. for Manhattan the value can be as big as 50 (e.g. you can have as many as 100 street numbers per 100 yards on a street). For some outer suburbs the value can decrease a little, representing the average number of street numbers per linear 100 yards per street. You can even put it to your suburbs table
SELECT * FROM MAPFILE WHERE STREET LIKE "LEXINGTON" AND bldn >= 591-10 and bldn <= 591+10 ORDER BY ABS(591-bldn)SELECT ABS(591-bldn) AS proximity, * FROM MAPFILE WHERE STREET LIKE "LEXINGTON" AND bldn >= 591-10 and bldn <= 591+10 ORDER BY ABS(591-bldn)BTW, the range value (10 in the query above) should be made a value relative to the borough (is it how the suburbs are called in New York?). E.g. for Manhattan the value can be as big as 50 (e.g. you can have as many as 100 street numbers per 100 yards on a street). For some outer suburbs the value can decrease a little, representing the average number of street numbers per linear 100 yards per street. You can even put it to your suburbs table
ASKER
Let me try to explain this a little better.
1. As of right now I'm only working with data in Manhattan. For outer boroughs and other cities the data that I'm querying has the correct coordinates (crossing, north, south east and west streets)
2. The value that the user keys in is "592".
3. You can't tell how many #'s that there are on any given block by footage. On one side of the street there could be 7 and on the other 1. For example Macy's takes up the entire side of a street and has one address.
4. All I'm looking for is a query to get me close to an address number.
5. Google is not an option. They have no idea where a building is. They'll get you close but it is not accurate.
1. As of right now I'm only working with data in Manhattan. For outer boroughs and other cities the data that I'm querying has the correct coordinates (crossing, north, south east and west streets)
2. The value that the user keys in is "592".
3. You can't tell how many #'s that there are on any given block by footage. On one side of the street there could be 7 and on the other 1. For example Macy's takes up the entire side of a street and has one address.
4. All I'm looking for is a query to get me close to an address number.
5. Google is not an option. They have no idea where a building is. They'll get you close but it is not accurate.
Can you try this?
http://sqlfiddle.com/#!9/6b38f/9
SELECT *
FROM (SELECT t1.*,abs(t1.Bld - @Bld) Abs_Bld
FROM test t1,(SELECT @Bld := 592 ) t2) t3
ORDER BY Abs_Bld,Bld
LIMIT 1http://sqlfiddle.com/#!9/6b38f/9
Ordering by the ABS(591-bldn) will give a list of rows starting from the closest to 591. Obviously if you want 592 you need to order by the ABS(592-bldn).
Let me show it in example. Say you have these street numbers: 580, 581,585,593,595.
ABS(592-bldn) will give these values for each of the above numbers: 12,11,7,1,3. Now, when you order by ABS(592-bldn) the order will be: 593,595,585,581,580. You see, the first record is the closest one.
Have you actually tried to apply my sort order?
Let me show it in example. Say you have these street numbers: 580, 581,585,593,595.
ABS(592-bldn) will give these values for each of the above numbers: 12,11,7,1,3. Now, when you order by ABS(592-bldn) the order will be: 593,595,585,581,580. You see, the first record is the closest one.
Have you actually tried to apply my sort order?
ASKER
chaau:
I haven't tried it today. My internet has been screwing up for the last 2 days. I originally thought it was the router. Turns out it's the modem. Verizon is sending me a new one to be delivered on Monday.
Hopefully, if I can stay connected over the weekend and I can test the code.
Thanks, I'll keep in touch
Glenn
I haven't tried it today. My internet has been screwing up for the last 2 days. I originally thought it was the router. Turns out it's the modem. Verizon is sending me a new one to be delivered on Monday.
Hopefully, if I can stay connected over the weekend and I can test the code.
Thanks, I'll keep in touch
Glenn
ASKER
Chaau
I tried your first query and it does work. However, when I tried the second query I get an error.
Here's the error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM mapfile WHERE STRT LIKE "LEXINGTON%" AND BNMB >= 591-10 and BNMB <= 591+1' at line 1
This is the code:
SELECT ABS(591-BNMB) AS proximity, * FROM mapfile WHERE STRT LIKE "LEXINGTON%" AND BNMB >= 591-10 and BNMB <= 591+10 ORDER BY NOR, ABS(591-BNMB)
If I take out this code from the select "ABS(591-BNMB) AS proximity, " it does work.
Glenn
I tried your first query and it does work. However, when I tried the second query I get an error.
Here's the error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM mapfile WHERE STRT LIKE "LEXINGTON%" AND BNMB >= 591-10 and BNMB <= 591+1' at line 1
This is the code:
SELECT ABS(591-BNMB) AS proximity, * FROM mapfile WHERE STRT LIKE "LEXINGTON%" AND BNMB >= 591-10 and BNMB <= 591+10 ORDER BY NOR, ABS(591-BNMB)
If I take out this code from the select "ABS(591-BNMB) AS proximity, " it does work.
Glenn
ASKER
Chaau:
I've been playing with this. The keyed in data is "583 Lexington"
The data from the file and the difference from 583 is as follows:
590 Lex = 7
591 Lex = 8
575 Lex = 8
My query is:
SELECT * FROM mapfile WHERE STRT LIKE "LEXINGTON%" AND BNMB >= 583-10 and BNMB <= 583+10 ORDER BY ABS(583 - BNMB) ASC LIMIT 1
It returns "590 Lex". Is there something that I might be missing on this?
Glenn
I've been playing with this. The keyed in data is "583 Lexington"
The data from the file and the difference from 583 is as follows:
590 Lex = 7
591 Lex = 8
575 Lex = 8
My query is:
SELECT * FROM mapfile WHERE STRT LIKE "LEXINGTON%" AND BNMB >= 583-10 and BNMB <= 583+10 ORDER BY ABS(583 - BNMB) ASC LIMIT 1
It returns "590 Lex". Is there something that I might be missing on this?
Glenn
I think 590 is the closest. What do you expect it to return?
ASKER
That is what I wanted. I have the query that I need.
Last question on this. What's the difference between:
ORDER BY ABS(583 - BNMB)
ORDER BY ABS(BNMB - 583)
I've been googling this and I've seen it written both ways.
Last question on this. What's the difference between:
ORDER BY ABS(583 - BNMB)
ORDER BY ABS(BNMB - 583)
I've been googling this and I've seen it written both ways.
It's exactly the same; ABS forces the same result in both cases. ABS means "Absolute value", which basically means removing the minus sign, if present.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is from a long time ago. I thought that I closed it.
As an alternative, you could first run a location search using a third-party tool, e.g. Google Map API (web-services) to get an approximate location, and then compare it with your data