Geek Answers Handbook

Dimensions for geofences or Lat & Long in the data warehouse

I have a DimPlace dimension that has a place name (entered manually by the user), as well as the latitude and longitude of the place (automatically fixed). Since Places are entered manually, the same place can be there several times with different names, in addition, two different places can be very close to each other.

We want to be able to analyze MPG between two "places", but we want to group them to make a large area - that is, using lat and long, we put all the different spellings of one location, as well as excellent, but very close locations in one record.

I plan to take a new dimension for this - something like DimPlaceGeozone. I am looking for a resource that will help with loading all the lat and long values ​​associated with ... something ?? Maybe a zip code or city name? Sometimes you may find a script to load common dimensions (e.g. DimTime). I would like something similar for lat and long values ​​in North America?

+6
source share
1 answer

In the past, I did something similar ... One stumbling block that I came across was that 2 locations located along the border can be physically closer to each other than 2 locations that are in the same same area.

, , 4 . , 2 , 1 "area", , .

, ...

IF OBJECT_ID('tempdb..#LatLngAreas', 'U') IS NOT NULL 
DROP TABLE #LatLngAreas;
GO

WITH 
    cte_Lat AS (
        SELECT 
            t.n,
            BegLatRange = -37.9 + (t.n / 10.0),
            EndLatRange  = -37.7 + (t.n / 10.0)
        FROM
            dbo.tfn_Tally(1030, 0) t
        ),
    cte_Lng AS (
        SELECT 
            t.n,
            BegLngRange = -159.7 + (t.n / 10.0),
            EndLngRange = -159.5 + (t.n / 10.0)
        FROM
            dbo.tfn_Tally(3050, 0) t
        )
SELECT 
    Area_ID = ROW_NUMBER() OVER (ORDER BY lat.n, lng.n),
    lat.BegLatRange, 
    lat.EndLatRange, 
    lng.BegLngRange, 
    lng.EndLngRange
    INTO #LatLngAreas
FROM
    cte_Lat lat
    CROSS JOIN cte_Lng lng;


SELECT 
    b3.Branch_ID,
    b3.Name,
    b3.Lat,
    b3.Lng,
    lla.Area_ID
FROM
    dbo.ContactBranch b3    -- replace with DimPlace
    JOIN #LatLngAreas lla
        ON b3.Lat BETWEEN lla.BegLatRange AND lla.EndLatRange
        AND b3.lng BETWEEN lla.BegLngRange AND lla.EndLngRange;

,

+2

More articles: