?
Solved

sql query returning closest value

Posted on 2013-11-19
21
Medium Priority
?
305 Views
Last Modified: 2014-05-28
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.
0
Comment
Question by:breeze351
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 2
  • +1
21 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39661197
I think the best option for you will be to implement a geospacial support in your database. For all real estate in your table you would record the longitude/latitude coordinates, and in your search function you would search using some pre-defined distance. I am not sure how big is your existing data and how difficult it will be to enhance your existing code base.
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
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39661235
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?
0
 

Author Comment

by:breeze351
ID: 39663591
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?
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 25

Expert Comment

by:chaau
ID: 39663660
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+10

Open in new window

0
 

Author Comment

by:breeze351
ID: 39663955
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.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39664119
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
0
 

Author Comment

by:breeze351
ID: 39667323
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.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39667375
ORDER BY ABS(582) will not find, but ORDER BY ABS(591-bldn) will. Modify the statement like this:
SELECT * FROM MAPFILE WHERE STREET LIKE "LEXINGTON" AND bldn >= 591-10 and bldn <= 591+10 ORDER BY ABS(591-bldn)

Open in new window

To understand how the ABS(591-bldn) works, add it to the select statement and see for yourself (this is something that can be called as proximity):
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)

Open in new window

The value 591 in the query above is the value from the user search box, and I assume is a passed in value.
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
0
 

Author Comment

by:breeze351
ID: 39667757
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39667874
Can you try this?
SELECT *
  FROM (SELECT t1.*,abs(t1.Bld - @Bld) Abs_Bld 
          FROM test t1,(SELECT @Bld := 592 ) t2) t3
 ORDER BY Abs_Bld,Bld 
 LIMIT 1

Open in new window


 http://sqlfiddle.com/#!9/6b38f/9
0
 
LVL 25

Expert Comment

by:chaau
ID: 39667888
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?
0
 

Author Comment

by:breeze351
ID: 39670329
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
0
 

Author Comment

by:breeze351
ID: 39719093
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
0
 

Author Comment

by:breeze351
ID: 39719381
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
0
 
LVL 25

Expert Comment

by:chaau
ID: 39720302
I think 590 is the closest. What do you expect it to return?
0
 

Author Comment

by:breeze351
ID: 39720357
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.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39720431
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.
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39732866
Great to be of any help. Especially that you used my solution and accepted other expert's answer. Good luck with your project
0
 

Author Comment

by:breeze351
ID: 40096582
This is from a long time ago.  I thought that I closed it.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question