I know how to do spatial query, Shape is my geometry type column.

bbox is well know text format of a bounding box

srid = 2229, you must specify this srid in where intersect clause

                  ---- Must specify SRID 2229 ( state plane california zone 5), query geometry srid must match the against source srid, if srid not match, will always return null
                  ---- by default, in all spatial query here, we use 2229 as srid. so no need to specify 2229 in url paramerter, it default and enforce to use 2229
                  ---- This method always returns null if the spatial reference IDs (SRIDs) of the geometry instances do not match. https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type?view=sql-server-ver15
                  ---- seems like sql server do not have ability auto re-project from 4326 to 2229, while postgis can auto re-project from 4326 to 2229
                  ---- in postgis, you can do bbox=STGeomFromText('polygon((lat lng, lat long.....)),' 4326), postgis will auto re-project to 2229.
                  ----- sql server, if not match , you always get null.

               Spatial Reference Identifiers (SRIDs)
                   A spatial column can contain objects with different SRIDs. However, only spatial instances with the same SRID can be used when performing operations with SQL Server spatial data methods on your data. The result of any spatial method derived from two spatial data instances is valid only if those instances have the same SRID that is based on the same unit of measurement, datum, and projection used to determine the coordinates of the instances. The most common units of measurement of a SRID are meters or square meters.
                   If two spatial instances do not have the same SRID, the results from a geometry or geography Data Type method used on the instances will return NULL. For example, for the following predicate term to return a non-NULL result, the two geometry instances, geometry1 and geometry2, must have the same SRID:

 bbox=POLYGON((6460000 1840000,6500000 1840000,6500000 1880000,6460000 1880000,6460000 1840000))

                  ----bbox(bounding box) polygon is rectangula anti-clock-wise as 

                              SouthWest-long SouthWest-lat, 
                              SouthEast-long SouthEast-lat, 
                              NorthEast-long NorthEast-lat,
                              NorthWest-long NorthWest-lat,
                              SouthWest-long  SouthWest-lat

                               Xmin Ymin,       //SouthWest
                               Xmax Ymin,       //SouthEast
                               Xmax Ymax,       //NorthEast
                               Xmin Ymax,       //NorthWest
                               Xmin Ymin        //SouthWest

But how to create spatial index against shape column?

                                        top 100



                                            must exclude geometry column, 
                                            otherwise, you will get error as : 
                                                    JSON serialization failure: Unable to serialize binary data to JSON 


                                            ,Shape.STSrid as srid 
                                            ,Shape.STGeometryType() as spatialType
                                            ,Shape.STAsText() as wkt

                                        FROM tblGeocoding

      where Shape.STIntersects(geometry::STGeomFromText('#bbox#', 2229)) = 1

This is my working example:

 run sql: 

                CREATE SPATIAL INDEX spatialIndex_on_geometry_col
                    ON [sidewalk_repair_testing].[dbo].[tblGeocoding](Shape)
                            BOUNDING_BOX= (xmin=6359662, ymin=1715073, xmax=6512913, ymax=1942255) 


              you can use GUI by click expand table tblGeocoding, right click indexs, click new index...  , choose spatial index...
              click general choose spatial column 'Shape', click spatial, define the x-min, y-min, x-max, y-max ( you must provide 4 value here) 

            Consider that use existing geometry may not calculate the ideal bounding box, since you can add new geometry later, bounding box could expanding.
            Be safe, use the city boundary as reference calculate the bounding box.

                        DECLARE @g geometry;     
                        SET @g = geometry::STGeomFromText('Polygon( *** replace with city boundary shape *** )', 0);        // city boundary shape
                        SELECT @g.STEnvelope().ToString(); 

             How to determine x-min, y-min, x-max, y-max?


                  For sql server 2008 and later

                     method 1: 

                                            ROUND(MIN(Shape.STEnvelope().STPointN(1).STX), 0) AS MinX,
                                            ROUND(MIN(Shape.STEnvelope().STPointN(1).STY), 0) AS MinY,
                                            ROUND(MAX(Shape.STEnvelope().STPointN(3).STX), 0) AS MaxX,
                                            ROUND(MAX(Shape.STEnvelope().STPointN(3).STY), 0) AS MaxY
                                    FROM [sidewalk_repair_testing].[dbo].[tblGeocoding];

                                    MinX    MinY    MaxX    MaxY
                                    6359662 1715073 6512913 1942255

                    method 2:

                        ENVELOPE as  ( SELECT Shape.STEnvelope() as envelope from [sidewalk_repair_testing].[dbo].[tblGeocoding] ),
                        CORNERS  as  ( SELECT envelope.STPointN(1) as point from ENVELOPE  UNION ALL select envelope.STPointN(3) from ENVELOPE  )
                                MIN(point.STX) as MinX,
                                MIN(point.STY) as MinY,
                                MAX(point.STX) as MaxX,
                                MAX(point.STY) as MaxY
                        FROM  CORNERS;

                            MinX    MinY    MaxX    MaxY
                        6359662.26417563    1715072.56881957    6512913.11139515    1942254.71825552

                For SQL server 2012 and later

                 method 3:

                            geometry::EnvelopeAggregate(Shape).STPointN(1).STX AS MinX,
                            geometry::EnvelopeAggregate(Shape).STPointN(1).STY AS MinY,
                            geometry::EnvelopeAggregate(Shape).STPointN(3).STX AS MaxX,
                            geometry::EnvelopeAggregate(Shape).STPointN(3).STY AS MaxX
                        FROM [sidewalk_repair_testing].[dbo].[tblGeocoding];


                                        MinX      MinY  MaxX    MaxX
                                        6359662.26417563     1715072.56881957   6512913.11139515    1942254.71825552

                            FROM [sidewalk_repair_testing].[dbo].[tblGeocoding]

                            will list each geometry's bounding box

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.