0

I need to get a list of nearest neighbor merchant locations based on a Latitude and longitide and a distance parameter. For this, i have created a table using t the query below:

**create table location.ML_POINTS_5K(
    MERCHANT_ID         INT(15), 
    STREET_ADDRESS      VARCHAR(50), 
    CITY                VARCHAR(50), 
    STATE               VARCHAR(25), 
    ZIPCODE             VARCHAR(11), 
    COUNTRY             CHAR(2),      
    ACCESSIBILITY       CHAR(1),      
    LOCATION_GEO_CODES_SDO  POINT NOT NULL, 
    BRAND_ID INT(7) NOT NULL,
    SPATIAL INDEX(LOCATION_GEO_CODES_SDO),
    PRIMARY KEY(MERCHANT_ID),
    FOREIGN KEY(BRAND_ID) REFERENCES BRAND(BRAND_ID)    
) ENGINE=MyISAM;**

Now, i also inserted some records into this table using the follwing sample query to make sure , the point geometry is inserted OK.

**INSERT INTO LOCATION.ML_POINTS_5K VALUES(11111,'111 678 St','Warren', 'OH', '11111','US','Y',GeomFromText('POINT(-82.7071458 39.90564701)'),1004);**

Now i need to construct a select query in MYSQL that will return all locations in this table, from a specified Latitude, Longitude and Distance.

For ex, if i have a latitude of 39.34 and longitude of -84.56 , a distance of 5 miles, how can i construct a query to get all the location records from the table that i created above within a distance of 5 miles.?..

Adam Porad
  • 14,193
  • 3
  • 31
  • 56
user2052129
  • 231
  • 4
  • 11

1 Answers1

0

I think it can be done with something like this:-

SELECT MERCHANT_ID, STREET_ADDRESS, CITY, STATE, ZIPCODE, COUNTRY, (GLength(LineStringFromWKB(LineString(LOCATION_GEO_CODES_SDO, GeomFromText('POINT(-84.56 39.34)'))))) * (1000000 / 1604) AS distance
FROM ML_POINTS_5K
WHERE ((GLength(LineStringFromWKB(LineString(LOCATION_GEO_CODES_SDO, GeomFromText('POINT(-84.56 39.34)'))))) * (1000000 / 1604)) < 5

Default distance is in 100s of km I think, hence the need to manipulate it to get it in miles.

Code based on this:-

http://www.tech-problems.com/calculating-distance-in-mysql-using-spatial-point-type/

Kickstart
  • 21,403
  • 2
  • 21
  • 33
  • Thanks a lot. This does return some records. Now the main open question is what is the exact metric used in the calculation of distance. I know you hinted at the default distance been in the 110s of km, but is that documented anywhere. cant seem to find it anywhere. – user2052129 Jun 11 '13 at 19:49
  • Only place I can see it is in the comments at the bottom of that page. However I would like to check in a bit more detail. – Kickstart Jun 11 '13 at 22:40