USP_CONSTITUENTDATAPOINTS_WITHINBOUNDS

CREATE procedure dbo.USP_CONSTITUENTDATAPOINTS_WITHINBOUNDS
(
  @NORTHLATITUDE decimal(13,6),
  @SOUTHLATITUDE decimal(13,6),
  @WESTLONGITUDE decimal(13,6),
  @EASTLONGITUDE decimal(13,6),
  @IDSETIDS nvarchar(max) = null
) with execute as owner
as
  set nocount on;

  declare @SQL nvarchar(max) = '';
  declare @IDSETSQL nvarchar(max) = '';

  declare @IDSETTABLE table (IDSETID uniqueidentifier);
  declare @DELIMITER as nvarchar(max) = ',';

  if len(@IDSETIDS) > 0
  begin
    -- Parse each set ID into a table var.

    declare @XML xml;
    set @XML = N'<root><r>' + replace(@IDSETIDS, @DELIMITER, '</r><r>') + '</r></root>';

    insert into 
      @IDSETTABLE(IDSETID)
    select
      r.value('.','uniqueidentifier') as IDSETID
    from
      @XML.nodes('//root/r') as records(r);

    -- Aggregate set id selection statements, concatenated with 'union'

    select 
      @IDSETSQL = @IDSETSQL + dbo.UFN_BUILDSELECTSQL(IDSETID) + ' union'
    from
      @IDSETTABLE;

    -- Strip trailing 'union' string.

    if len(@IDSETSQL) > 0
      set @IDSETSQL = substring(@IDSETSQL, 0, len(@IDSETSQL) - 5);
  end

  if len(@IDSETSQL) > 0
  begin
    set @SQL = '

    with CONSTIT_CTE as
    (
    '
    + @IDSETSQL +
    '
    )
    select
      ADDRESSCOORDINATES.LATITUDE, 
      ADDRESSCOORDINATES.LONGITUDE,
      -- Each datapoint is weighted the same.

      1.0 as [RELATIVEINTENSITY]
    from
      dbo.ADDRESSCOORDINATES
    inner join
      dbo.ADDRESS on ADDRESS.ID = ADDRESSCOORDINATES.ADDRESSID
    inner join
      CONSTIT_CTE on CONSTIT_CTE.ID = ADDRESS.CONSTITUENTID
    where
      ADDRESS.ISPRIMARY = 1
    and
      (ADDRESSCOORDINATES.LATITUDE >= @SOUTHLATITUDE and ADDRESSCOORDINATES.LATITUDE <= @NORTHLATITUDE)
    and
      (ADDRESSCOORDINATES.LONGITUDE >= @WESTLONGITUDE and ADDRESSCOORDINATES.LONGITUDE <= @EASTLONGITUDE)
    '

    declare @PARAMETERDEFINITION nvarchar(1000);
    set @PARAMETERDEFINITION = N'@NORTHLATITUDE decimal(13,6), @SOUTHLATITUDE decimal(13,6), @WESTLONGITUDE decimal(13,6), @EASTLONGITUDE decimal(13,6)';

    exec sp_executesql @SQL, @PARAMETERDEFINITION, @NORTHLATITUDE = @NORTHLATITUDE, @SOUTHLATITUDE = @SOUTHLATITUDE, @WESTLONGITUDE = @WESTLONGITUDE, @EASTLONGITUDE = @EASTLONGITUDE;
  end