Using geography type value with STContains function

Question

Ok first, I'm storing Lat/Long location in a column of type geography in my SQL Server table.

I try to get stores in a specific bounding box using STContains method, the only way I found to use the geography point, was by concatening the Lat/Long with STR convertion:

DECLARE @boundingBox geography;  
SET @boundingBox = geography::Parse('POLYGON((...))');

...
SELECT Store.Id, Store.Name, Store.Location.Lat, Store.Location.Long, 
  @boundingBox.STContains(
    geography::Parse('POINT(' + STR(Store.Location.Lat, 20, 12) + ' ' 
                              + STR(Store.Location.Long, 20, 12) + ')'))

It works, but it's ugly and asking if there's a cleaner way to write this.

there is a Point() method… https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/point-geography-data-type?view=sql-server-ver15 — lptr, Aug 10 '21 at 16:36
In your SELECT statement, are you selecting *from* anything? Presumably it's a table or view. If so, can it be changed to have a geography column that is the point in question? — Ben Thul, Aug 10 '21 at 20:43
@BenThul Yeah I'm selecting from a table (Store) where I keep Lat/Long in a Location column (geography type). Value is save in Insert like this : geography::Point({Lat}, {Long}, 4326) — Jonathan Anctil, Aug 11 '21 at 03:53
@BenThul I put the STContains function in the SELECT statement to view the result (0 or 1). — Jonathan Anctil, Aug 11 '21 at 03:55
@JonathanAnctil - if that's the case, you're jumping through a lot more hoops than you need to. You should be able to just do `@boundingBox.STContains(Store.Location) = 1`. — Ben Thul, Aug 11 '21 at 16:35
@BenThul This is what I thought, `@boundingBox.STContains(Store.Location)` always return 0. — Jonathan Anctil, Aug 12 '21 at 01:41
Do you have the same issue if using `geography::STPolyFromText(` instead of `geography::Parse `? https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stpolyfromtext-geography-data-type?view=sql-server-ver15 — Dawood Awan, Aug 12 '21 at 07:40
Another thing to check is if you think the point should be in the box but the query says otherwise, check to see whether you have a ring orientation problem with your bounding box. You may have accidentally defined a world-sized area with a tiny hole in it (which, not coincidentally, is the size and shape of your intended box). — Ben Thul, Aug 12 '21 at 21:13

score 0 · Answer 1 · answered Aug 12 '21 at 08:04

I setup 2 test Polygons geo and geo2. One using Parse method and the other using STPolyFromText method.

declare @geo geography 
            = geography::STPolyFromText('POLYGON ((73.250684 34.198599, 73.250598 34.199324, 73.250343 34.200021, 73.249927 34.200663, 73.249369 34.201226, 73.248688 34.201688, 73.247912 34.202031, 73.247069 34.202243, 73.246193 34.202314, 73.245317 34.202243, 73.244474 34.202031, 73.243698 34.201688, 73.243017 34.201226, 73.242458 34.200663, 73.242043 34.200021, 73.241788 34.199324, 73.241701 34.198599, 73.241788 34.197874, 73.242043 34.197177, 73.242458 34.196535, 73.243017 34.195972, 73.243698 34.19551, 73.244474 34.195167, 73.245317 34.194956, 73.246193 34.194884, 73.247069 34.194956, 73.247912 34.195167, 73.248688 34.19551, 73.249369 34.195972, 73.249927 34.196535, 73.250343 34.197177, 73.250598 34.197874, 73.250684 34.198599, 73.250684 34.198599))', 4326)
declare @geo2 geography 
            = geography::Parse('POLYGON ((73.250684 34.198599, 73.250598 34.199324, 73.250343 34.200021, 73.249927 34.200663, 73.249369 34.201226, 73.248688 34.201688, 73.247912 34.202031, 73.247069 34.202243, 73.246193 34.202314, 73.245317 34.202243, 73.244474 34.202031, 73.243698 34.201688, 73.243017 34.201226, 73.242458 34.200663, 73.242043 34.200021, 73.241788 34.199324, 73.241701 34.198599, 73.241788 34.197874, 73.242043 34.197177, 73.242458 34.196535, 73.243017 34.195972, 73.243698 34.19551, 73.244474 34.195167, 73.245317 34.194956, 73.246193 34.194884, 73.247069 34.194956, 73.247912 34.195167, 73.248688 34.19551, 73.249369 34.195972, 73.249927 34.196535, 73.250343 34.197177, 73.250598 34.197874, 73.250684 34.198599, 73.250684 34.198599))')

declare @outsidePoint geography
            = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326),
        @insidePoint geography
            = geography::STPointFromText('POINT(73.2438096 34.1989505)', 4326)
select 
    geo = @geo, 
    geoString = @geo.ToString(),
    IsValid = @geo.STIsValid(),
    doesContainOutsidePoint = @geo.STContains(@outsidePoint),
    doesIntersectOutsidePoint = @geo.STIntersects(@outsidePoint),
    doesContainInsidePoint = @geo.STContains(@insidePoint),
    doesIntersectInsidePoint = @geo.STIntersects(@insidePoint)

select 
    geo = @geo2, 
    geoString = @geo2.ToString(),
    IsValid = @geo2.STIsValid(),
    doesContainOutsidePoint = @geo2.STContains(@outsidePoint),
    doesIntersectOutsidePoint = @geo2.STIntersects(@outsidePoint),
    doesContainInsidePoint = @geo2.STContains(@insidePoint),
    doesIntersectInsidePoint = @geo2.STIntersects(@insidePoint)

Both of them seem to work for me - with the following results:

I can query from my Polygon table in the DB using both the STContains and STIntersects methods of the geography type.

So if .STContains is returning 0 for you that means the Point is not inside the box. Maybe you could post a sample Polygon and Point where it is returning 0 but should return 1 that might help.

Using geography type value with STContains function

1 Answers1