CODEWITHSUNDEEP
sqlsql-server-2008gis
Paul Riker
Paul Riker

Reputation: 797

Max Lat, Min Lat, Max Long, Min Long from Geometry field

I'm trying to figure out how to get the max and min values for lat and long points from a geometry data field. I'm guessing you have to first convert it to a string us STAsText but I'm not sure. Let me know if you have any better idea?

Upvotes: 0

Views: 1534

Answers (2)

Paul Riker
Paul Riker

Reputation: 797

I took a different approach. 1. I converted the geometry field to a string 2. Parsed the string and converted it to XML 3. Stored it in a separate field 4. Ran an xml query off of that field.

The geom field in my case is storing areas for cities. In some cases the areas do not overlap, so actually have separate areas.

Here is the query I used to convert to XML.

update city set geomxml =  '<city>' +  replace(replace(replace(replace
 (replace(replace(replace   
             (geom.ToString(),'POLYGON ',''),
                    '((','<area><point><long>'),
                    '(','<area><point><long>'),
                    '))','</lat></point></area>'),
                    '), ','</lat></point></area>'),
                    ', ','</lat></point><point><long>'),
                    ' ','</long><lat>') +
                    '</city>'

Here is the query to get the max and min for lat and long

select geomxml.query('max(//lat)') as maxlat, geomxml.query('min(//lat)') as minlat, geomxml.query('max(//long)') as maxlong, geomxml.query('min(//long)') as minlong
from city

Upvotes: 0

pickypg
pickypg

Reputation: 22332

You probably want to use the SQL Server extension methods "Lat" and "Long" combined with Max and Min.

At first I read this as getting the min/max lat/long pair, and this was my answer: I suspect you could determine this by figuring out which point has the largest, and smallest distance from (0, 0), or whatever location you were concerned about.

Upvotes: 1

Related Questions