CODEWITHSUNDEEP
sql-serverstringgeospatialgeography
A.Steer
A.Steer

Reputation: 409

Formatting a geographical STBuffer output to reduce length of the returned Latitude and Longitude string

I am using the geography datatypes in SQL server to output a string of longitude's and latitude's with a set distance (1 meter in this instance) from a input longitude and latitude.

DECLARE @g geography  
SET @g = geography::STGeomFromText('point(-0.124663 51.499840)', 4326)    

select 
    replace(replace(replace(@g.STBuffer(1).ToString(),'POLYGON ((',''''),'))',''''),', ',''',''')

I am using the replace formatting on the select statement to format the string so it can then be used in a where clause of another SQL statement to allow me to select any values in another table.

The geography statement outputs the following string:

'-0.12465280384915144 51.499846347274094','-0.12465331513696513 51.499846651882976','-0.12465384975647235 51.499846940466881','-0.12465440641972776 51.499847212330586','-0.12465498378568056 51.499847466819169','-0.12465558046340523 51.499847703319517','-0.12465619501545253 51.499847921261889','-0.12465682596131217 51.49984812012125','-0.1246574717809797 51.499848299418524'

This is a list of Longitudes and latitude as a comma separated list. This issue I am having is the string that is being outputted has a longer scale for each number than the resultant set of data that I am looking up against.

If I manually change the string output to the below, my second SQL query finds the relevant data, so I need to find a way to set each value returned in the geography statement to have a scale of 6.

'-0.124652 51.499846','-0.124653 51.499846','-0.124653 51.499846','-0.124654 51.499847','-0.124654 51.499847','-0.124655 51.499847','-0.124656 51.499847','-0.124656 51.499848','-0.124657 51.499848'

Upvotes: 0

Views: 60

Answers (0)

Related Questions