USP_DATALIST_CONSTITUENTDENSITY

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

    declare @idtable table (id uniqueidentifier);
    declare @SQL 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 
            @SQL = @SQL + dbo.ufn_buildselectsql(idsetid) + ' union'
        from 
            @idsettable

        -- Strip trailing 'union' string.

        if LEN(@SQL) > 0
            set @SQL = substring(@SQL, 0, len(@SQL) - 5);

        -- Insert the Ids obtained from dynamic sql into a table var.

        insert into @idtable
        exec (@SQl);
    end

    select 
        ADDRESSCOORDINATES.LATITUDE, 
        ADDRESSCOORDINATES.LONGITUDE,
        -- Each datapoint is weighted the same.

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