locked
AMR Support Manager RRS feed

  • Question

  • I am hoping someone can help with a query to extract points within a polygon.  Within a table (tblLLCompare) I have 927,000 records, the columns are Office, AccountNumber,Latitude,Longitude, GeoLocation

    [GeoLocation]

    = geography::STGeomFromText('POINT(' + CAST([Latitude] AS VARCHAR(20)) + ' ' +

     

    CAST([Longitude] AS VARCHAR(20)) + ')', 4326)

    I would like to select the AccountNumber,Latitude and Longitude from tblLLCompare when the points are within the polygon below:

    DECLARE

     

    @g geography;

    SET

     

    @g = geography::STPolyFromText(

    'POLYGON((-111.67920 40.23240,

    -111.67454 40.22737,

    -111.67099 40.22593,-111.66743 40.22580,

    -111.66757 40.23373,-111.67920 40.23240))'

     

    , 4326);

    I am a novice and not sure if I am on the right track, I haven't been able to write a select statement to return the data I am looking for

     

    Any help would be appreciated

     
    Friday, November 4, 2011 7:24 PM

All replies

  • Hi Mai,

    I will involve someone more familiar with this topic for a further look at this issue.  Thanks for your understanding.

    Best Regards,
    Stephanie Lv


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, November 8, 2011 9:43 AM
  • Your coordinates are transposed.

    [GeoLocation] = geography::STGeomFromText('POINT(' + CAST([Latitude] AS VARCHAR(20)) + ' ' + CAST([Longitude] AS VARCHAR(20)) + ')', 4326)

    should be:

    [GeoLocation] = geography::STGeomFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

    or, even better:

    [GeoLocation] = geography::Point(Latitude, Longitude, 4326)


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, November 9, 2011 9:40 AM
    Answerer
  • I went ahead and inserted three records with three different points. I then created a polygon that would include Rock Hill point. In the last query you can use the STIntersects function to bring back the Office and AccountNumber for the GeoLocation that intersects with a polygon. You should also probably create a spatial index on the table to help the query. The STIntersects will use the spatial index. Below is an example.

     

    use

    [mydb]

    go

    --create table

    if

    OBJECT_ID('dbo.tblLLCompare', 'U') is not

    null

     

    drop table dbo.tblLLCompare;
     

    go

    create

    table tblLLCompare

    (

    [ID]

    bigint identity(1,1) primary key,
     

    [Office]

    varchar(100),
     

    [AccountNumber]

    varchar(20),
     

    [Latitude]

    float,
     

    [Longitude]

    float,

    [GeoLocation]

    geography

    )

    go

    --create a spatial index on GeoLocation column

    create

    spatial index spidx_geography_geolocation ON tblLLCompare(GeoLocation

    );

    go

    --insert data with office information

    insert

    into [tblLLCompare] (Office, AccountNumber, Latitude, Longitude) values ('ROCK HILL', '123456', 34.8741, -81.0777

    )

    insert

    into [tblLLCompare] (Office, AccountNumber, Latitude, Longitude) values ('ATLANTA', '123457', 33.7683179, -84.3892299

    )

    insert

    into [tblLLCompare] (Office, AccountNumber, Latitude, Longitude) values ('NEW YORK', '123458', 40.75516871, -74.00227219

    )

    go

    --update the GeoLocation (geography) from latitude and longitude information

    update

    [dbo].[tblLLCompare]

    set

    [GeoLocation] = geography::Point([Latitude], [Longitude], 4326

    )

    go

    --see the table and STAsText()

    select

    *, GeoLocation.STAsText() as 'STAsText' from tblLLCompare

     

    --find the office and account number that reside in the polygon

    SELECT

    Office, AccountNumber

    FROM

    tblLLCompare

    WHERE

    GeoLocation

    .STIntersects(

    'POLYGON ((-81.077728271484375 34.874101838422334, -81.055068969726562 34.858327053995225, -81.028289794921875 34.892689663399118, -81.033782958984375 34.935481993559009, -81.05438232421875 34.941673772066125, -81.077728271484375 34.874101838422334))'

    )=1

    Hope this helps

    some,

    Bill

    MSFT SQL Server Support

    Monday, November 21, 2011 2:40 PM