2

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?

1

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
  • Should be noted that with Geography there is not a bounding box returned from EnvelopeAggregate, but rather a bounding object made of circular arcs. Try SELECT Geography::EnvelopeAggregate(G) FROM (VALUES(Geography::Point(1,1,4326)),(Geography::Point(-1,-1,4326)),(Geography::Point(-2,1.5,4326)))G(G) UNION ALL SELECT G.STBuffer(10000) FROM (VALUES(Geography::Point(1,1,4326)),(Geography::Point(-1,-1,4326)),(Geography::Point(-2,1.5,4326)))G(G) and have a look at the spatial pane – MickyT Jul 3 '18 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.