[GIS] Query MSSQL geography data type against a bounding box

sql serversql-server-spatial

I'm currently storing geographic locations as data type geography in my MSSQL database and performing radius queries against it without issue.

Is it possible to query against it using a polygon bounding box of points so that the results returned fit within the bounding box?

Best Answer

You should be able to do this with the EnvelopeAggregate function. I only tested using geometry data type, but let me know if this works for geography too...

Here is the SQL syntax for selecting a group of points and intersecting them against the bounding box of another set of points:

;

WITH cte_pts
AS (
    SELECT *
    FROM points_table 
    )
    , cte_box
AS (
    SELECT geometry::EnvelopeAggregate(pb.geom) AS geom
    FROM points_for_box AS pb
    WHERE pb.column = 'value'

    )
SELECT *
FROM cte_pts AS p
JOIN cte_box AS b
    ON p.geom.STIntersects(b.geom) = 1
Related Question