I have entities saved in SQL table with Lat and long values. I added another column to store Geometry values.
Code for updating Geometry column UPDATE [dbo].[table] SET [GeoLocation] = geography::STGeomFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
Now i need to modify my current procedure which work on Longitude & Latitude columns to newly added Geometry column. The stored procedure basically takes input as @Minlat, @MaxLat, @MinLong & @MaxLong and returns the entities between these coordinates.These points are then displayed on UI
Current Procedure filter select Entity, * from Table WHERE Latitude BETWEEN @Min_lat AND @Max_Lat AND Longitude BETWEEN @Min_long AND @Max_Long
I need use the Geometry column in the above where condition so that it returns the same result.
I have created a polygon with the 4 inputs and used STIntersects function however it is not giving me any result. Not sure i should be using Geometry or geography time. The entities i show on map are static.
Code to create bounding box ** DECLARE @boundingRect Geometry SET @boundingRect = 'POLYGON((' + @minX + ' ' + @minY + ', ' + @maxX + ' ' + @minY + ', ' + @maxX + ' ' + @maxY + ', ' + @minX + ' ' + @maxY + ', ' + @minX + ' ' + @minY + '))' **New Procedure filter select Entity, * from Table WHERE GeoLocation.STWithin(@boundingRect) = 1
If i compare data for the select statments it is different. the STWithin does not select any records from table