USP_DATALIST_CONSTITUENTPROXIMITYRECTANGLE

Returns a list of constituents within a given rectangle from a set of coordinates.

Parameters

Parameter	Parameter Type	Mode	Description
@NORTHWESTLATITUDE	decimal(16, 13)	IN	Northwest latitude
@NORTHWESTLONGITUDE	decimal(16, 13)	IN	Northwest longitude
@SOUTHEASTLATITUDE	decimal(16, 13)	IN	Southeast latitude
@SOUTHEASTLONGITUDE	decimal(16, 13)	IN	Southeast longitude

Definition

Copy



CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROXIMITYRECTANGLE(@NORTHWESTLATITUDE decimal(16,13),
    @NORTHWESTLONGITUDE decimal(16,13),
    @SOUTHEASTLATITUDE decimal(16,13),
    @SOUTHEASTLONGITUDE decimal(16,13))
as
    set nocount on;

    select CONSTITUENT.ID,
        CONSTITUENT.NAME,
        ADDRESS.ID [ADDRESSID],
        ADDRESS.ADDRESSBLOCK,
        ADDRESS.CITY,
        STATE.ABBREVIATION [STATE],
        ADDRESS.POSTCODE,
        COUNTRY.ABBREVIATION [COUNTRY],
        case when ADDRESSCOORDINATES.LATITUDE is null then POSTCODECOORDINATES.LATITUDE else ADDRESSCOORDINATES.LATITUDE end LATITUDE,
        case when ADDRESSCOORDINATES.LONGITUDE is null then POSTCODECOORDINATES.LONGITUDE else ADDRESSCOORDINATES.LONGITUDE end LONGITUDE,
        null as GROUPID,
        null as GROUPNAME
    from dbo.CONSTITUENT
    inner join dbo.ADDRESS on CONSTITUENT.ID = [ADDRESS].CONSTITUENTID and ADDRESS.ISPRIMARY = 1
    left join dbo.POSTCODECOORDINATES on ADDRESS.POSTCODE = POSTCODECOORDINATES.POSTCODE
    left join dbo.ADDRESSCOORDINATES on ADDRESS.ID = ADDRESSCOORDINATES.ADDRESSID
    left join dbo.STATE on [STATE].ID = ADDRESS.STATEID
    left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
    where case when ADDRESSCOORDINATES.LATITUDE is null then POSTCODECOORDINATES.LATITUDE else ADDRESSCOORDINATES.LATITUDE end <= @NORTHWESTLATITUDE 
        and case when ADDRESSCOORDINATES.LATITUDE is null then POSTCODECOORDINATES.LATITUDE else ADDRESSCOORDINATES.LATITUDE end >= @SOUTHEASTLATITUDE
        and case when ADDRESSCOORDINATES.LONGITUDE is null then POSTCODECOORDINATES.LONGITUDE else ADDRESSCOORDINATES.LONGITUDE end >= @NORTHWESTLONGITUDE
        and case when ADDRESSCOORDINATES.LONGITUDE is null then POSTCODECOORDINATES.LONGITUDE else ADDRESSCOORDINATES.LONGITUDE end <= @SOUTHEASTLONGITUDE
        and (ADDRESSCOORDINATES.PENDINGGEOCODE is null or ADDRESSCOORDINATES.PENDINGGEOCODE = 0)
        and (ADDRESSCOORDINATES.INVALIDGEOCODE is null or ADDRESSCOORDINATES.INVALIDGEOCODE = 0)
    order by CONSTITUENT.KEYNAME;