locked
Creating Keys for Latitude/Longitude RRS feed

  • 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 Simon Nuss Tuesday, April 5, 2016 6:40 PM
    • Moved by Naomi N Tuesday, April 5, 2016 6:50 PM Better answer can be here
    Tuesday, April 5, 2016 6:40 PM

Answers

All replies