Member Avatar for xecure

I have a database full of user. This is roughly how the database looks:

| username | sex | birthdate  | zip_code |
|----------+-----+------------+----------|
| coolUser |  M  | 02-14-1987 |  90210   |
| blueUser |  F  | 06-16-1982 |  10011   |
|     .    |  .  |     .      |    .     |
|     .    |  .  |     .      |    .     |
|     .    |  .  |     .      |    .     |

A user will be able to search the database for another user based on their sex, age and distance. I have another database with a list of zipcodes in the U.S. along with with their latitude and longitude. I have written distance function where it calculates the distance of 2 zipcodes based on their latitude and longitude values. I understand I can sort the users by sex and age by something like this:

SELECT * FROM userList WHERE sex = 'M' AND birthdate > 4-12-1986

Which brings me to my actual question: Is there any possible way to calculate the distance of the 2 users via an SQL query? Or do I have to sort the user based on age and sex first and then get that list and then filter it by distance? If I go with the second method how do I properly display the first 10 results, and have a pagination feature?

  • 3 Contributors
  • 2 Replies
  • 427 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by Sogo7

Dani AI

Generated

Brief summary tied to the thread: is right to start from lat/lon values rather than zip_code strings; was also right that an inexpensive pre-filter (a rectangle) saves a lot of work, and is correct that precomputing can be useful for low-change data — but there are better, scalable options built into modern MySQL that avoid full table scans and giant pairwise tables.

A practical pattern that scales

  • store each user location as a spatial POINT (SRID 4326) and add a SPATIAL INDEX on that column;
  • use an indexed MBR (bounding-box) test to quickly reject far-away rows, then compute an exact spherical distance for the remaining candidates and ORDER / LIMIT for pagination.

Example pattern (illustrative only):

-- keep coordinates in a POINT column and index it
ALTER TABLE users ADD COLUMN location POINT NOT NULL SRID 4326;
CREATE SPATIAL INDEX idx_loc ON users(location);

-- search: cheap indexed MBR filter, then exact distance for final test/sort
SELECT username, zip_code,
  ST_Distance_Sphere(location, ST_GeomFromText('POINT(:lon :lat)',4326)) AS meters
FROM users
WHERE sex = 'M'
  AND MBRContains(ST_GeomFromText(:bbox_wkt,4326), location)
HAVING meters <= :radius_meters
ORDER BY meters
LIMIT 10 OFFSET 0;

Why this works and important cautions

  • MySQL provides ST_Distance_Sphere for accurate spherical distances (meters). (dev.mysql.com)
  • Spatial index usage depends on using MBR/box-aware functions in WHERE (MBRContains / MBRWithin) so the optimizer can use the SPATIAL INDEX; compute the precise distance afterward. (docs.oracle.com)
  • In practice ST_Distance_Sphere alone won’t drive the spatial index; combine it with an MBR prefilter. (stackoverflow.com)

Operational notes

  • MySQL 8 tightened SRID handling: make your column SRID-restricted and rebuild indexes after upgrades so the optimizer will use them. Also verify axis-order behavior for SRID=4326 on your version (lon/lat vs lat/lon) before mass updates. (dev.mysql.com)
  • For pagination: LIMIT/OFFSET is fine for small pages; for deep paging use keyset pagination (order by distance with a stable tiebreaker).
  • If traffic is heavy, precompute nearest-N zipcodes (not full pairwise matrix) or use a geosearch engine / PostGIS for advanced KNN features.

This gives you precise results, uses indexes to keep the work small, and avoids exploding storage costs from full pairwise tables.

Recommended Answers

All 2 Replies

Member Avatar for sv3tli0

If you have an exact latitude and longitude for all zipcodes I think you have to do:

1st - SELECT the latitude and longitude of the user who is searching (with query or with user session information)..
2nd - When user set the distance and search - your script must calculate acceptable area for this.

$lt_min; $lt_max; $lg_min; $lg_max;

Something like from 43.0000 to 44.0000 and from -103.0000 to -102.0000..
This can be made if you have a calculator (range of 50km= 0.000500x0.000500) and etc.. and geting user 0.000000x0.000000 you can set the 4 vars with lt_min=0.000-0.005 lt_max=0.000+0.005 etc..

3rd - After you have this acceptable ranges you must make query which will get all zipcodes which are with in this acceptable ranges..
I will use the vars from my previous code..

$query="SELECT * FROM ZIPCODE WHERE 
latitude>$lt_min AND latitude<$lt_max AND 
longitude>$lg_min AND longitude<$lg_max";
 
$all_zips='('; 
$sql=mysql_query($query);
while($res=mysql_fetch_array($sql)){ $all_zips.=$res['zipcode'].','; }
$all_zips=substr($all_zips, 0, -1);
$all_zips.=')';

After that you make your query as :

SELECT * FROM userList WHERE sex = 'M' AND birthdate > 4-12-1986 AND zip_code IN $all_zips

This way your users will get results. Now here is a problem that this search will be made within a Rectangle range which is not 100% correct because your real range of 5km is with circle form not with rectangle.
To fix that you must make a calculation with your function in the WHILE at my upper code for each entry.. something like:

//FROM UP ! 
while($res=mysql_fetch_array($sql)){ $all_zips.=$res['zipcode'].','; }
//MUST BECOME
while($res=mysql_fetch_array($sql)){ 
if(your_function($res['lat'],$res['long'],$user['lat'],$user['long'],$search_distance)==true) $all_zips.=$res['zipcode'].',';
 }

Your function must return TRUE or FALSE depending if some zipcode position is within the search distance of user position

Edited by sv3tli0 because: n/a
Member Avatar for Sogo7

It would make more sense to store the distance value between zip codes as a seperate 'look-up' table of pre-computed results as this information will never change.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.