Home Forums SQL Server 2008 T-SQL (SS2K8) returning rows within a certain distance in miles from city using longitude and latitude RE: returning rows within a certain distance in miles from city using longitude and latitude

  • Serg,

    The actual life tables are similar to what I initially posted, with the exception that dim_lead doesn't actually contain latitude and longitude columns. I'm don't know why I included lat/long in the original sample ddl, because I handled for the association of leads to lat/long in a temporary query.

    Dim_leads = all attributes of leads

    Dim_geography = zip, city, state, latitude, longitude

    Goal: return the leads and cities in which they dwell that are within given distance (report parameter #1) to given city center (report parameter#2) and state (report parameter #3).

    Table definitions and data:

    create table dim_lead

    (

    dim_lead_key int,

    date_created datetime,

    [contact_first_name] varchar(20),

    [contact_last_name] varchar(20),

    lead_id int,

    [contact_city] varchar(20),

    [contact_state] varchar(2),

    [contact_zip] varchar(10),

    sub_status varchar(5));

    insert into dim_lead values

    ('2014-11-12 00:00:00.000', 'Leah', 'Loan', 1234, 'Boulder', 'CO', '80302', 'R'),

    ('2014-11-12 00:00:00.000', 'Mary', 'Morris', 4321, 'Boston', 'MA', '02133', 'R'),

    ('2014-11-12 00:00:00.000', 'Nancy', 'Nham', 3241, 'Colorado Springs', 'CO', '80949','R'),

    ('2014-11-12 00:00:00.000', 'Olga', 'Ofram', 2314, 'Denver', 'CO', '80201', 'R'),

    ('2014-11-12 00:00:00.000', 'Patty', 'Phillips', 3421, 'Greeley', 'CO', '80631', 'R'),

    ('2014-11-12 00:00:00.000', 'Queen', 'Quon', 2143, 'New York', 'NY', '10025', 'R'),

    ('2014-11-12 00:00:00.000', 'Rebecca', 'Rhode', 2431, 'Pueblo', 'CO', '81001', 'R');

    CREATE TABLE [dbo].[dim_geography](

    [dim_geography_key] [int] IDENTITY(1,1) NOT NULL,

    [zip] [varchar](10) NOT NULL,

    [city] [varchar](50) NULL,

    [state] [varchar](2) NULL,

    [country] [varchar](2) NULL,

    [county] [varchar](40) NULL,

    [latitude] [float] NULL,

    [longitude] [float] NULL,

    [rn] [bigint] NULL

    ) ON [PRIMARY]

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (47683, N'02133', N'Boston', N'MA', N'US', N'Suffolk', 42.358631, -71.056702, 1)

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (65413, N'80302', N'Boulder', N'CO', N'US', N'Boulder', 40.015739, -105.279243, 1)

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (254402, N'80949', N'Colorado Springs', N'CO', N'US', N'El Paso', 38.83345, -104.821808, 1)

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (111044, N'80201', N'Denver', N'CO', N'US', N'Denver', 39.740009, -104.992264, 1)

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (60117, N'80631', N'Greeley', N'CO', N'US', N'Weld', 40.423279, -104.694458, 1)

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (2851992, N'10025', N'New York', N'NY', N'US', N'New York', 40.71455, -74.007118, 1)

    INSERT [dbo].[dim_geography] ([dim_geography_key], [zip], [city], [state], [country], [county], [latitude], [longitude], [rn]) VALUES (204835, N'81001', N'Pueblo', N'CO', N'US', N'Pueblo', 38.263859, -104.612373, 1)

    SET IDENTITY_INSERT [dbo].[dim_geography] OFF

    I am using eririkur's cross join query because it doesn't exclude leads having a city = NULL...but it takes 1 1/2 = 2 minutes given the volume of leads it must search.

    query

    ;WITH LEAD_DATA AS

    (

    SELECT

    DL.contact_first_name

    ,DL.contact_last_name

    ,DL.contact_city

    ,DL.contact_state

    ,DL.contact_zip

    ,DL.sub_status

    ,Geography::Point(COALESCE(DL.latitude,0),COALESCE(DL.longitude,0), 4326) AS GEO_POINT

    FROM dbo.dim_lead DL

    )

    ,GEO_DATA AS

    (

    SELECT

    DG.city

    ,DG.state

    ,Geography::Point(COALESCE(DG.latitude,0),COALESCE(DG.longitude,0), 4326) AS GEO_POINT

    FROM dbo.dim_geography DG

    WHERE DG.city = 'Denver'

    AND DG.state = 'CO'

    )

    ,FINAL_SET AS

    (

    SELECT

    LD.contact_first_name + CHAR(32) + LD.contact_last_name AS FULL_NAME

    ,LD.contact_city

    ,LD.contact_state

    ,LD.contact_zip

    ,LD.sub_status

    ,GD.city

    ,GD.state

    --,(LD.GEO_POINT.STDistance(GD.GEO_POINT) * 0.001) AS DIST_METER

    ,(LD.GEO_POINT.STDistance(GD.GEO_POINT) * 0.0006213) AS DIST_MILE

    FROM LEAD_DATA LD

    CROSS JOIN GEO_DATA GD

    )

    SELECT

    FS.FULL_NAME

    ,FS.contact_city

    ,FS.contact_state

    ,FS.contact_zip

    ,FS.sub_status

    ,FS.city

    ,FS.state

    ,FS.DIST_MILE

    FROM FINAL_SET FS

    WHERE FS.DIST_MILE < 250;