-2

SQL 2008

I have two tables. One table (A) have around 4000 locations with lat lng. Another table (B) having 800 locations with lat lng.

I need each lat lng of Table B with all corresponding lat lngs within 15 Km of radius.

I am using sql 2008 and very new to geographical queries.

Rohit Lal
  • 15
  • 7
  • Welcome to Stackoverflow. This is a site for asking "what is wrong with what I have" (where you provide a [Minimal, Complete, and Verifiable Example](http://stackoverflow.com/help/mcve))... it is not a code writing service, and you will get a better response when you provide the evidence of your own work. Please read the [ask] section in the help, and this [excellent article](http://whathaveyoutried.com). – freefaller Apr 25 '16 at 07:32
  • Agree with your point..I do not need any code just little bit hint how to do it. – Rohit Lal Apr 25 '16 at 07:37
  • ===alter table OD_LNT_LNG add [p] as geography::Point(Latitude, Longitude, 4326) persisted; create spatial index LNT_LNG on OD_LNT_LNG ([p])=== I have tried to add geo column but it showing some error "Computed column 'p' in table 'LNT_LNG' cannot be persisted because the column type, 'geography', is a non-byte-ordered CLR type" Pls help – Rohit Lal Apr 25 '16 at 07:40
  • Is there anyone to help on that – Rohit Lal Apr 25 '16 at 09:10

1 Answers1

0
        /*
            Assuming Your tables are like so
        */
        IF OBJECT_ID('#xLocation1') IS NOT NULL
            DROP TABLE #xLocation1
        CREATE TABLE #xLocation1 (
             Id         INT IDENTITY(1,1) CONSTRAINT PK_Location_1 PRIMARY KEY--Reqire this for Geog Spatial Index
            ,LocationId INT
            ,Latitude   FLOAT   NULL
            ,Longitude  FLOAT   NULL
            ,Radius     INT     NULL
            ,GeogPoint  GEOGRAPHY   NULL
        )


        IF OBJECT_ID('#xLocation2') IS NOT NULL
            DROP TABLE #xLocation2
        CREATE TABLE #xLocation2 (
             Id         INT IDENTITY(1,1) CONSTRAINT PK_Location_2 PRIMARY KEY--Reqire this for Geog Spatial Index
            ,LocationId INT
            ,Latitude   FLOAT   NULL
            ,Longitude  FLOAT   NULL
            ,Radius     INT     NULL
            ,GeogPoint  GEOGRAPHY   NULL
        )


        DECLARE @Radius INT = 15 --KM

        /*
            Create GEOGRAPHY POINT datatypes
        */

        UPDATE #xLocation1
        SET
            GeogPoint   = GEOGRAPHY::STGeomFromText('POINT(' + CAST(ISNULL(Longitude,'') AS VARCHAR(20)) + ' ' + CAST(ISNULL(Latitude,'') AS VARCHAR(20)) + ')', 4326)

        UPDATE #xLocation2
        SET
            GeogPoint   = GEOGRAPHY::STGeomFromText('POINT(' + CAST(ISNULL(Longitude,'') AS VARCHAR(20)) + ' ' + CAST(ISNULL(Latitude,'') AS VARCHAR(20)) + ')', 4326)


        /*
            CREATE SPATIAL INDEXes
        */
        CREATE SPATIAL INDEX [SDX_Location1_GeogPoint_x1] ON #xLocation1 ( [GeogPoint] ) 
        USING GEOGRAPHY_GRID 
        WITH 
        (     GRIDS=(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH) 
                , CELLS_PER_OBJECT = 64 
                , PAD_INDEX = OFF 
                , SORT_IN_TEMPDB = OFF 
                , DROP_EXISTING = OFF 
                , ALLOW_ROW_LOCKS = ON 
                , ALLOW_PAGE_LOCKS = ON 
        ) 

        CREATE SPATIAL INDEX [SDX_Location2_GeogPoint_x2] ON #xLocation2 ( [GeogPoint] ) 
        USING GEOGRAPHY_GRID 
        WITH 
        (     GRIDS=(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH) 
                , CELLS_PER_OBJECT = 64 
                , PAD_INDEX = OFF 
                , SORT_IN_TEMPDB = OFF 
                , DROP_EXISTING = OFF 
                , ALLOW_ROW_LOCKS = ON 
                , ALLOW_PAGE_LOCKS = ON 
        ) 

        /*
            Find where locations from each table are within @Radius of each other
        */
        SELECT *
        FROM 
            #xLocation1 X
        INNER JOIN
            #xLocation2 P ON X.GeogPoint.STDistance(P.GeogPoint) <= @Radius
Mazhar
  • 3,797
  • 1
  • 12
  • 29