1

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

  • Welcome to GIS SE. As a new user please take the Tour. Please edit your question to contain a question. It is more likely to be answered if you try to implement the technology before asking. – Vince Nov 28 '16 at 11:03
  • What have you tried? The lack of a question mark makes it difficult to discern a question. – Vince Nov 28 '16 at 12:11
  • I have created the polygon using geometry::STGeomFromText from the min and max of lat log values and used the STIntersects function in where condition and it did not return any result, however when i query the same on lat and long values it returns me output. How to create a bounding box from lat long input values and compare them with a geometry filed to return only those entities in the bounding box?? – Rohan Nov 28 '16 at 12:27
  • Please edit the question in response to requests for clarification. Since the most likely cause is your point encoding, you should also include the code you used to transfer the coordinates to geometry. 80% of the time, a well-researched question will answer itself. – Vince Nov 28 '16 at 12:47

Your Answer

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

Browse other questions tagged or ask your own question.