0

I have an sql table that has some data on retail locations with lat, long coordinates. My application queries the table to pull the data using python. Here's some sample data:

table1

id  name   lat       long
1   post   37.788151 -122.407570
2   sutter 37.789551 -122.408302
3   oak    37.815730 -122.288810

I have a point coordinate 37.785980 and -122.410851, and I'd like to find rows from the table that are nearby, say within 0.5 mile radius from the coordinate.

Performance matters. I cannot query all rows due to poor performance, long time sending data to client application and storage / memory issues. Therefore, I need a SQL based solution.

Using SQL in python, how do I implement an efficient solution that does proximity search and returns rows that meet the distance condition? Here's how I pull data from sql in python.

# mysql connection
import pymysql
from sqlalchemy import create_engine
user = 'user1'
pwd = 'pwd'
host =  'xxxx.1.rds.amazonaws.com'
port = 3306
database = 'db1'
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(user,pwd,host,database))


# Readdata
con = engine.connect()

query = '''
        SELECT x
        FROM table1
        GROUP BY key
        '''

df = pd.read_sql(query, con)

con.close()

Happy to share more info if required for context.

1 Answer 1

0

I had the very same problem, this post by Paul Spiegel may help you

https://stackoverflow.com/questions/49372146/find-the-nearest-point-from-a-given-point-in-mysql

Copy-pasted Code from his answer:

drop temporary table if exists Placemark;
create temporary table Placemark(
    Name varchar(50),
    Coordinates point
);

insert into Placemark(Name, Coordinates) values
    ('p1', POINT(-118.2845938, 34.0252385)),
    ('p2', POINT(-118.2867610, 34.0221188)),
    ('p3', POINT(-118.2905912, 34.0227248)),
    ('p4', POINT(-118.284119 , 34.021846 )),
    ('p5', POINT(-118.2864676, 34.0186438)),
    ('p6', POINT(-118.2886342, 34.0203211)),
    ('p7', POINT(-118.2907290, 34.0193680)),
    ('p8', POINT(-118.2831326, 34.0192874)),
    ('p9', POINT(-118.2828242, 34.0205473));

SET @home = ST_GeomFromText('POINT(-118.2819136 34.0261177)');

SELECT 
    P.Name,
    ST_AsText(P.Coordinates) as Coordinates,
    ST_DISTANCE(@home, P.Coordinates) AS dist 
FROM Placemark P
ORDER BY dist
LIMIT 3

The result:

Name | Coordinates                    | dist
p1   | POINT(-118.2845938 34.0252385) | 0.00282072059587649
p4   | POINT(-118.284119 34.021846)   | 0.00480741199088132
p9   | POINT(-118.2828242 34.0205473) | 0.00564433773972052

Demo: http://rextester.com/GMPK57301

To define a POINT use either POINT(-118.2845938, 34.0252385) or ST_GeomFromText('POINT(-118.2845938 34.0252385)'). To see the value in a readable form use ST_AsText().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.