CODEWITHSUNDEEP
sqlsql-servergisgeospatialspatial-query
user13341837
user13341837

Reputation: 13

SQL Server Spatial Select Where STContains

I'm working on a spatial database SQL Server and am having a hard time querying a row where the geography contains a given lat/long.

I'm able to get this query to work:

DECLARE @polygon Geography;
select @polygon = (
    select 
        geog4269 
    from census_tracts 
    WHERE namelsad10 = 'Census Tract 9801.02'
);
set @polygon = @polygon.ReorientObject();
select @polygon.STContains(
    geography::Point(18.4102591, -66.0732014, 4269)
);

However, I want to be able to select the row that contains a given lat/long with something like the following: 

select 
    *
from census_tracts 
WHERE geog4269.ReorientObject().STContains(
    geography::Point(18.4102591, -66.0732014, 4269)
) = 1

I'm getting a .NET Framework exception when I run that saying to use MakeValid to avoid it, but adding .MakeValid() doesn't fix the issue.

This is the exception message: 

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
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.SqlGeography.ThrowIfInvalid()
   at Microsoft.SqlServer.Types.SqlGeography.ReorientObject()
.

When I use the following query:

select 
    *
from census_tracts 
WHERE geog4269.MakeValid().ReorientObject().STContains(
    geography::Point(18.4102591, -66.0732014, 4269)
) = 1

The geographies don't get reoriented (every geography says it contains all points).

Has anyone run into something similar before or can point out where I'm going wrong? Thanks for the help!

Upvotes: 1

Views: 1418

Answers (1)

Michael Entin
Michael Entin

Reputation: 7744

My guess is for some geographies MakeValid also fixes the orientation of the polygons. If the polygons is invalid, MakeValid has to make a guess about what was the intended shape, and fix it using some heuristics. The results may vary depending on what exactly was wrong with the data - and sometime garbage in, garbage out applies.

I would avoid using both MakeValid() and ReorientObject() in the query. This is both error prone, and slow (as it prevents spatial index usage).

Instead, fix the actual data by updating the geographies to be the intended ones.

Quick and dirty way is to invert only those that need inverting, something like

update census_tracts 
set geog4269 = 
    IF (geog4269.MakeValid().STArea() < 1e14, -- is it small?
        geog4269.MakeValid(),
        geog4269.MakeValid().ReorientObject());

Upvotes: 1

Related Questions