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. https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-reference-identifiers-srids?view=sql-server-ver15 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 POLYGON(( SouthWest-long SouthWest-lat, SouthEast-long SouthEast-lat, NorthEast-long NorthEast-lat, NorthWest-long NorthWest-lat, SouthWest-long SouthWest-lat )) POLYGON(( Xmin Ymin, //SouthWest Xmax Ymin, //SouthEast Xmax Ymax, //NorthEast Xmin Ymax, //NorthWest Xmin Ymin //SouthWest ))
But how to create spatial index against shape column?
SELECT top 100 OBJECTID ,Location_No ,Geotype <!--- must exclude geometry column, otherwise, you will get error as : coldfusion.runtime.JSONUtils$JSONSerializeBinaryException: JSON serialization failure: Unable to serialize binary data to JSON ,Shape ---> ,Shape.STSrid as srid ,Shape.STGeometryType() as spatialType ,Shape.STAsText() as wkt FROM tblGeocoding where Shape.STIntersects(geometry::STGeomFromText('#bbox#', 2229)) = 1