locked
need check lat and lon with polygon RRS feed

  • Question

  • i have geomaster table with geo boundry column defined as follow format of data,  stored as varchar value


    SRID=4326;POLYGON((-79.6919040913008 43.5905928537878 0,-79.6918978370142 43.5905700000015 0,-79.6919040913008 43.5905471461998 0,-79.6919219020003 43.5905277716839 0,-79.691948557584 43.5905148260369 0,-79.6919800000023 43.5905102801291 0,-79.6920114424117 43.5905148260369 0,-79.6920380980043 43.5905277716839 0,-79.6920559086949 43.5905471461998 0,-79.6920621629815 43.5905700000015 0,-79.6920559086949 43.5905928537878 0,-79.6920380980043 43.5906122282892 0,-79.6920114424117 43.5906251739086 0,-79.6919800000023 43.5906297198076 0,-79.691948557584 43.5906251739086 0,-79.6919219020003 43.5906122282892 0,-79.6919040913008 43.5905928537878 0))

    need to create procedure by passing lat. and long.  find given lat and long match which geoboundry  data and get corresponding columns from geomaster table

    is it possible in sql 2014

    Friday, October 21, 2016 2:43 AM

All replies

  • Use STPolyFromText to create a polygon from the varchar value (you need to strip the "SRID=4326;" from the string first).

    Use MakePoint or other equivalent methods to create a point from your lat-long values.

    Use STIntersects to check for intersection.

    There is documentation and examples of all methods for geometry type here:  https://msdn.microsoft.com/en-us/library/bb933960.aspx and geography type here: https://msdn.microsoft.com/en-us/library/cc280766.aspx

    Friday, October 21, 2016 4:52 PM
  • Hi Vaishu,

    Did you mean you wish to check whether a point is within a polygon? If so, you could use STWithin(). Here’s an example:
    declare @GeoMaster table (ID int, geoboundry varchar(max), SRID int) ;
    insert into @GeoMaster
    VALUES 
    ('1', N' POLYGON((-79.6919040913008 43.5905928537878 0,-79.6918978370142 43.5905700000015 0,-79.6919040913008 43.5905471461998 0,-79.6919219020003 43.5905277716839 0,-79.691948557584 43.5905148260369 0,-79.6919800000023 43.5905102801291 0,-79.6920114424117 43.5905148260369 0,-79.6920380980043 43.5905277716839 0,-79.6920559086949 43.5905471461998 0,-79.6920621629815 43.5905700000015 0,-79.6920559086949 43.5905928537878 0,-79.6920380980043 43.5906122282892 0,-79.6920114424117 43.5906251739086 0,-79.6919800000023 43.5906297198076 0,-79.691948557584 43.5906251739086 0,-79.6919219020003 43.5906122282892 0,-79.6919040913008 43.5905928537878 0))', N'4326'),
    ('2', N' POLYGON((-79.6919040913008 43.5905928537878 0,-79.6918978370142 43.5905700000015 0,-79.6919040913008 43.5905471461998 0,-79.6919219020003 43.5905277716839 0,-79.691948557584 43.5905148260369 0,-79.6919800000023 43.5905102801291 0,-79.6920114424117 43.5905148260369 0,-79.6920380980043 43.5905277716839 0,-79.6920559086949 43.5905471461998 0,-79.6920621629815 43.5905700000015 0,-79.6920559086949 43.5905928537878 0,-79.6920380980043 43.5906122282892 0,-79.6920114424117 43.5906251739086 0,-79.6919800000023 43.5906297198076 0,-79.691948557584 43.5906251739086 0,-79.6919219020003 43.5906122282892 0,-79.6919040913008 43.5905928537878 0))', N'4326');
    declare @p geometry;
    SET @p = geometry::STGeomFromText('POINT(-79.6919640913008 43.5905722282892)', 4326);
    select * from @GeoMaster GM where @p.STWithin(geometry::STGeomFromText(GM.geoboundry,4326)) = 1;



    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Sunday, October 23, 2016 11:43 AM