Solved

sql query returning closest value

Posted on 2013-11-19
21
288 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
  • 9
  • 7
  • 2
  • +1
21 Comments
 
LVL 24

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
 
LVL 24

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 24

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 24

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 40

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 24

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 24

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 24

Accepted Solution

by:
chaau earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now