Spatial Data Error even when using MakeValid

  • tacy.highland

    Hall of Fame

    Points: 3199

    Hello,

    I have a spatial query that pulls in the points of a polygon from a table, then pulls in trip data (picks and drops) and compares if either pick or drop is inside the polygon using STIntersects.  Everything along the way checks out, as far as whether the data is valid or not, but the final query to Intersect just errs out with:

    Msg 6522, Level 16, State 1, Line 147

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":

    System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.

    System.ArgumentException:

    at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()

    at Microsoft.SqlServer.Types.SqlGeometry.STIntersects(SqlGeometry other)

    The query I used is as follows:

    /* Create temp table for trip addresses */
    CREATE TABLE #Points (
    LDate INT
    ,BookingID INT
    ,PGeoLocation Geometry
    ,PLat Float
    ,PLon Float
    ,DGeoLocation Geometry
    ,DLat Float
    ,DLon Float
    )


    INSERT INTO #Points
    SELECT S.LDate
    ,b.BookingID
    ,NULL PGeoLocation
    ,CAST(bl1.lat AS FLOAT)/1000000 PLat
    ,CAST(bl1.lon AS FLOAT)/1000000 PLon
    ,NULL DGeoLocation
    ,CAST(bl2.lat AS FLOAT)/1000000 DLat
    ,CAST(bl2.lon AS FLOAT)/1000000 DLon
    FROM ...[tables]
    WHERE s.LDate BETWEEN @SLDate AND @ELDate
    AND CAST(bl1.LON AS FLOAT)/1000000 <> 99.999999
    AND CAST(bl2.LON AS FLOAT)/1000000 <> 99.999999

    --select * from #points

    /* Convert addresses Lats and Lons to points */
    UPDATE #Points
    SET PGeoLocation = geometry::STGeomFromText('POINT(' + CAST(PLat AS VARCHAR(20)) + ' ' +
    CAST(Plon AS VARCHAR(20)) + ')', 4326)
    UPDATE #Points
    SET DGeoLocation = geometry::STGeomFromText('POINT(' + CAST(DLat AS VARCHAR(20)) + ' ' +
    CAST(Dlon AS VARCHAR(20)) + ')', 4326)

    UPDATE #Points
    SET PGeoLocation = PGeoLocation.MakeValid() WHERE PGeoLocation.STIsValid() = 0;

    UPDATE #Points
    SET DGeoLocation = DGeoLocation.MakeValid() WHERE DGeoLocation.STIsValid() = 0;



    /* Create the Polygon out of the Polygon Lats and Lons */
    DECLARE @Polygon1 INT = 7146
    DECLARE @BuildString NVARCHAR(MAX)

    SELECT @BuildString = COALESCE(@BuildString + ',', '') + CAST(CAST(lat AS FLOAT)/1000000 AS NVARCHAR(50)) + ' ' + CAST(CAST(lon AS FLOAT)/1000000 AS NVARCHAR(50))
    FROM mmsdata1.dbo.PolygonVerts
    WHERE polyid = @Polygon1
    ORDER BY vertSequence DESC

    SET @BuildString = 'POLYGON((' + @BuildString + '))'

    DECLARE @PolygonFromPoints geometry = geometry::STPolyFromText(@BuildString, 4326)

    /*pull all data with geolocation whether address is in or out of poly*/
    SELECT *
    ,CASE WHEN @PolygonFromPoints.STIntersects(PGeoLocation) = 1 THEN 'Y' ELSE 'N' END AS P_InPolygon1
    ,CASE WHEN @PolygonFromPoints.STIntersects(DGeoLocation) = 1 THEN 'Y' ELSE 'N' END AS D_InPolygon1
    FROM #points

    Just looking at the code, is there anything I'm missing here?  I've been googling and the only thing I can find even closely related to what I'm seeing is that there may be a problem with the actual polygon itself.  Maybe bad data in that table? Maybe it was drawn poorly?  But, if that is the case here, how do I get around that?  I don't think redrawing the polygon is a viable answer here so I think I'm stuck with the data I have.

    Or could it be some other issue?

    Any thoughts?

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply