locked
Spatial Criteria Giving Inconsistent Results RRS feed

  • Question

  • I have two queries that use the presence of a Geography data type and a STDistance as part of the WHERE clause. When I include the spatial data type as part of the criteria I get inconsistent results in that sometimes the query returns results and sometimes it does not. I isolated the spatial criteria out of the WHERE clause and the queries seem to work just fine. I have created a spatial index on the table to optimize queries. I need the STDistance part of the criteria to help limit the results to a geographic area.

    Geocode is a nullable Geopgraphy data type. One of the queries is

    USE

    MLSArchive

    DECLARE

    @DATE Date = CONVERT(varchar(4),YEAR(DATEADD(YY,-1,GETDATE()))) + '-' + CONVERT(varchar(2),MONTH(GETDATE())) + '-01'

    DECLARE

    @DATE2 Date = CONVERT(varchar(4),YEAR(GETDATE())) + '-' + CONVERT(varchar(2),MONTH(GETDATE())) + '-01'

    DECLARE

    @Inventory TABLE

    (

    [Month] varchar(8),

    [List]

    int,

    [Sale]

    int
     

    )

    DECLARE

    @geo1 geography = geography::Parse('Point ('+CAST(@Longitude as nvarchar(15))+' '+CAST(@Latitude as nvarchar(15))+')')

    WHILE

    @DATE < @DATE2

    BEGIN

     

    INSERT INTO @Inventory

     

    SELECT SUBSTRING(DATENAME(m,@DATE),1,3)+'-'+SUBSTRING(CONVERT(varchar(4),YEAR(@DATE)),3,2) AS Mon,
     

     

    (SELECT COUNT(*)

     

    FROM dbo.Residential as r

     

    JOIN METABASE.dbo.vListStatuses AS ls ON ls.LMDCode=r.ListStatus and ls.MarketCode=r.MarketID

     

    WHERE ls.LCode NOT IN ('CAN','WTH','EXP','TOM')

     

    AND r.ListDate < DATEADD(mm,1,@DATE)

     

    AND (r.PendingDate IS NULL OR r.PendingDate >= DATEADD(MM,1,@DATE))

     

    AND (r.ClosedDate IS NULL OR r.ClosedDate >= DATEADD(MM,1,@DATE))

     

    AND r.GeoCode IS NOT NULL

     

    AND @geo1.STDistance(r.Geocode)/1609.344 <= @Distance

     

    AND LEFT(r.Zip,5) = LEFT(@Zip,5) AND r.Subdivision = @Subdivision

     

    ),
     

     

    (SELECT COUNT(*)

     

    FROM dbo.Residential AS r

     

    JOIN METABASE.dbo.vListStatuses AS ls ON r.MarketID = ls.MarketCode AND r.ListStatus = ls.LMDCode

     

    WHERE ls.LCode='SLD'

     

    AND r.ClosedDate BETWEEN DATEADD(YY,-1,@DATE) AND @DATE

     

    AND r.GeoCode IS NOT NULL

     

    AND @geo1.STDistance(r.Geocode)/1609.344 <= @Distance

     

    AND LEFT(r.Zip,5) = LEFT(@Zip,5) AND r.Subdivision = @Subdivision

     

    )

     

    SET @DATE = DATEADD(mm,1,@DATE)
     

    END

    SELECT

    [Month] , CONVERT(decimal(4,2), List) as List, CONVERT(decimal(4,2), Sale/12.0) as Sale

    ,

    CASE

    WHEN Sale > 0 THEN CAST(CAST(List as decimal(5,3)) * 12 / CAST(Sale as decimal(5,3)) as decimal(4,2)) ELSE NULL END as Inventory

    FROM

    @Inventory

    A valid result set is

    Month List Sale Inventory
    Jul-10 8.00 1.00 8.00
    Aug-10 7.00 1.08 6.46
    Sep-10 4.00 1.25 3.20
    Oct-10 4.00 1.50 2.67
    Nov-10 5.00 1.67 3.00
    Dec-10 5.00 1.75 2.86
    Jan-11 11.00 1.83 6.00
    Feb-11 9.00 1.58 5.68
    Mar-11 5.00 1.42 3.53
    Apr-11 3.00 1.58 1.89
    May-11 5.00 1.67 3.00
    Jun-11 9.00 2.00 4.50

    The next run results in the following invalid result set

    Month List Sale Inventory
    Jul-10 0.00 0.00 NULL
    Aug-10 0.00 0.00 NULL
    Sep-10 0.00 0.00 NULL
    Oct-10 0.00 0.00 NULL
    Nov-10 0.00 0.00 NULL
    Dec-10 0.00 0.00 NULL
    Jan-11 0.00 0.00 NULL
    Feb-11 0.00 0.00 NULL
    Mar-11 0.00 0.00 NULL
    Apr-11 0.00 0.00 NULL
    May-11 0.00 0.00 NULL
    Jun-11 0.00 0.00 NULL

    Tuesday, July 5, 2011 2:08 PM

All replies

  • I was using a query to fill the variables for this query and the variable filling query was returning more than one record. That's why I'm getting inconsistent data . There's nothing wrong with the spatial data type.
    Wednesday, July 6, 2011 1:44 PM