Geography

 -- ============================================= 
-- Author:  cao you gang 
-- Create date: <Create Date,,> 
-- Description: <Description,,> 
-- ============================================= 
CREATE PROCEDURE [dbo].[P_FSRS_ReportDetail_SearchByLatLon]  
      @p_caseid bigint, 
   @p_latitude decimal(30,20), 
   @p_longitude decimal(30,20), 
   @p_radius float 
AS 
BEGIN 
 DECLARE @centre geography  
  SET @centre = geography::STGeomFromText('POINT('+CAST(@p_longitude AS NVARCHAR(40))+' '+CAST(@p_latitude AS NVARCHAR(40))+')', 4326); 
 --SET @centre = geography::STGeomFromText('POINT('+CONVERT(nchar,@p_latitude)+' '+CONVERT(nchar,@p_longitude)+')', 4757); 
  
    SELECT  
      t.ID, 
      t.CaseID, 
      t.Title, 
      t.Remark, 
      t.[Length], 
      t.Depth, 
      t.Assignee, 
      t.Zone, 
      (select c.Code from T_FSRS_Code c where c.ID= t.PubCategoryID) as PubCategoryID, 
      -- t.PubCategoryID, 
     -- t.PublicCategoryID, 
      t.Lanes_Affected, 
      t.Compound_Affected, 
      t.House_Affected, 
      t.Flood_Status, 
      t.Media_Around, 
      (select top 1 s.Code from T_PFAS_Station s where s.ID=t.StationID and s.Deleted=0) as StationID, 
      t.Action_Taken, 
      CONVERT( decimal(30,20), t.Latitude) as Latitude, 
      CONVERT( decimal(30,20), t.Longitude) as Longitude, 
      CONVERT( decimal(30,20), t.Latitude) as LatitudeDeci,     
      CONVERT( decimal(30,20), t.Longitude) as LongitudeDeci,       
      t.[Primary], 
      t.Map_Status, 
      t.[Status], 
      t.Radius, 
      t.ReporterID, 
      t.Deleted, 
      t.Created_By, 
      t.Created_Time, 
      t.Last_Updated_By, 
      t.Last_Updated_Time 
    FROM T_FSRS_ReportDetail t 
 where t.Deleted = 0 
 and( t.[Primary] = 0 or t.[Primary] is  NULL) 
 and t.CaseID = @p_caseid 
 and LOWER(t.Map_Status) = LOWER('VFD') 
 and @centre.STDistance(geography::STGeomFromText('POINT('+CAST(t.Longitude AS NVARCHAR(40))+' '+CAST(t.Latitude AS NVARCHAR(40))+')', 4326)) <= @p_radius 
  
 --and @centre.STDistance(geography::STGeomFromText('POINT('+CONVERT(nchar,t.Latitude)+' '+CONVERT(nchar,t.Longitude)+')', 4757)) <= @p_radius 
 union 
     SELECT  
      t.ID, 
      t.CaseID, 
      t.Title, 
      t.Remark, 
      t.[Length], 
      t.Depth, 
      t.Assignee, 
      t.Zone, 
        (select c.Code from T_FSRS_Code c where c.ID= t.PubCategoryID) as PubCategoryID, 
      
      t.Lanes_Affected, 
      t.Compound_Affected, 
      t.House_Affected, 
      t.Flood_Status, 
      t.Media_Around, 
       (select top 1 s.Code from T_PFAS_Station s where s.ID=t.StationID and s.Deleted=0) as StationID, 
      t.Action_Taken, 
      CONVERT( decimal(30,20), t.Latitude) as Latitude, 
      CONVERT( decimal(30,20), t.Longitude) as Longitude, 
      CONVERT( decimal(30,20), t.Latitude) as LatitudeDeci,     
      CONVERT( decimal(30,20), t.Longitude) as LongitudeDeci,       
      t.[Primary], 
      t.Map_Status, 
      t.[Status], 
      t.Radius, 
      t.ReporterID, 
      t.Deleted, 
      t.Created_By, 
      t.Created_Time, 
      t.Last_Updated_By, 
      t.Last_Updated_Time 
    FROM T_FSRS_ReportDetail t 
 where t.Deleted = 0 
 and( t.[Primary] = 0 or t.[Primary] is  NULL) 
 and  t.CaseID = @p_caseid 
 and  CONVERT(decimal(30,20), t.Latitude) =  @p_latitude 
 and  CONVERT(decimal(30,20),  t.Longitude) = @p_longitude 
 and  LOWER(t.Map_Status) = LOWER('VFD') 
   order by t.Last_Updated_Time desc   
 print(@p_latitude) 
 print(@p_longitude) 
END 

///

 

 

ALTER PROCEDURE [dbo].[P_FSRS_Region_GetAreaByLanLon]
   @p_latitude decimal(30,20),
   @p_longitude decimal(30,20),
   @p_areaName varchar(50) OUTPUT
AS
BEGIN
 
 DECLARE @g geometry;
 print('POINT('+CAST(@p_longitude AS VARCHAR(40))+' '+CAST(@p_latitude AS VARCHAR(40))+')');
 SET @g =  'POINT('+CAST(@p_longitude AS VARCHAR(40))+' '+CAST(@p_latitude AS VARCHAR(40))+')';

 set  @p_areaName =(select r.name from T_FSRS_Region r where r.area.STIntersects(@g)=1)
 --select @p_areaName
END