Find the distances between stores with Lat/Long Coordinates in Python

Question

I have a SQL query that pulls the Lat/Long values of all stores. How do I find the distances between all stores in the column that is returned from the query? Is that even possible?

First, I imported the snowflake connector and established a connection to snowflake:

import snowflake.connector

cnx = snowflake.connector.connect(user='XX', password='xxxx', account='xxxxxxxx', warehouse='abc', database='xxx', schema='xxx', role='xxxxxx')

Then I assigned my query to the variable below:

query = """SELECT store_latitude,store_longitude
from randomdatabase.schema.latandlongs a
WHERE a.store_longitude IS NOT NULL AND a.store_latitude IS NOT NULL"""

then

store = pd.read_sql_query(query,cnx)

How can I find the distance between all lat/long values returned by the query? Best way to achieve this?

Answer 1

You could use something along those lines:

select 
   a.someid
   ,MIN(ST_DISTANCE(ST_MAKEPOINT(a.store_longitude,a.store_latitude), ST_MAKEPOINT(b.store_longitude,b.store_latitude))) AS Distance 
from randomdatabase.schema.latandlongs a
cross join randomdatabase.schema.latandlongs b
WHERE a.store_longitude IS NOT NULL AND a.store_latitude IS NOT NULL"""
group by a.someid