# Creating Keys for Latitude/Longitude

• ### Question

• Hi guys

I'm still learning SQL so please be gentle :)

I'm trying to create a latitude and longitude key column on my fact table to join to a city dimension table.  Unfortunately, my fact table has precise GPS coordinates, e.g. -40.780751, and my dimension table simply lists the center point for zip codes, e.g. -40.7870750.

Fact table (2 million rows):

 Lat Lon Count 41.3569 -73.6258 1 41.2686 -73.1607 1 41.7214 -73.3446 2 41.1311 -73.7512 1

Dimension table (2,000 rows):

 zip city state latitude longitude 10560 North Salem NY 41.32797 -73.6131 10562 Ossining NY 41.17684 -73.8474 10566 Peekskill NY 41.28826 -73.9193 10567 Cortlandt Manor NY 41.30081 -73.8908 10570 Pleasantville NY 41.13237 -73.7892 10559 Mount Vernon NY 41.11901 -73.733

My current thought process is to perform a cross product between the two tables to determine the minimum distance between each lat/long using Pythagoras' Theorem ( e.g. below ) and then bring that into my fact table somehow.  This is very taxing on my PC.  Does anyone have a better suggestion?

```		SELECT top 1000
t1.lat AS fctlat,
t1.lon AS fctlong,
t2.latitude AS dimlat,
t2.longitude AS dimlong,
SQUARE( ABS( t1.lat - t2.latitude ) ) + SQUARE( ABS( t1.lon - t2.longitude ) ) as distance

FROM fct_Tbl t1

CROSS JOIN ( SELECT latitude, longitude FROM dim_zipcode WHERE [State] = 'NY' ) t2```

Thanks,

Simon

• Edited by Tuesday, April 5, 2016 6:40 PM
• Moved by Tuesday, April 5, 2016 6:50 PM Better answer can be here
Tuesday, April 5, 2016 6:40 PM