title: Alternative way to get Max, Min for Lat and Long from Geography field?
body: <p>I have geography field of irregular shapes. Geography field can vary from hundred to thousands of Lat/Long points that define that shape. In regards to size it could be from several US. Postal Codes to a size of entire US State. In order to have increased performance I have build Spacial index on that field. On frequent basis I have to find  vehicles based on Lat/Long point that are within specific zone.</p>
  
  <p>My original approach was this.</p>
  
  <pre><code>WITH    LastP
            AS ( SELECT vlp.ID
                     ,GEOGRAPHY::STPointFromText('POINT(' + CAST(vlp.Long AS VARCHAR(20)) + ' '
                                                 + CAST(vlp.Lat AS VARCHAR(20)) + ')', 4326) AS LastKnownPoint
                  FROM LastPosition AS vlp )
      SELECT lp.ID
             ,zn.ZONE
          FROM dbo.GeogZone AS zn WITH ( NOLOCK )
          JOIN @zones AS z
              ON zn.Zone = z.Zone
          JOIN LastP AS lp
              ON lp.LastKnownPoint.STWithin(zn.ZoneGeog) = 1
  </code></pre>
  
  <p>I was getting all records from my table <code>LastPosition</code> and than I converted Lat/Long into <code>Geography</code> point and later <code>JOIN</code> using <code>STWithin</code> function. This process works great but can be very slow. I have tried to adjust Spacial indexes but it did not make big changed.</p>
  
  <p>To increase performance I want to introduce the following process.</p>
  
  <p>From Geography type I will extract <code>NorthLat, SouthLat, EastLong, WestLong</code>
  Now I can limit the number of results before I do compare in the following matter.</p>
  
  <pre><code>WITH    LastP
            AS ( SELECT vlp.ID
                     ,GEOGRAPHY::STPointFromText('POINT(' + CAST(vlp.Long AS VARCHAR(20)) + ' '
                                                 + CAST(vlp.Lat AS VARCHAR(20)) + ')', 4326) AS LastKnownPoint
                  FROM LastPosition AS vlp 
                  WHERE (vlp.Long BETWEEN @WestLong and @EastLong) AND (vlp.Lat BETWEEN @SouthLat AND @NorthLat))
      SELECT lp.ID
             ,zn.ZONE
          FROM dbo.GeogZone AS zn 
          JOIN @zones AS z
              ON zn.Zone = z.Zone
          JOIN LastP AS lp
              ON lp.LastKnownPoint.STWithin(zn.ZoneGeog) = 1
  </code></pre>
  
  <p>Here is the code for building the box.</p>
  
  <pre><code>DECLARE @geomenvelope GEOMETRY;
  
  DECLARE @BoundingBox AS TABLE
      (
       SouthLat DECIMAL(10, 8)
      ,NorthLat DECIMAL(10, 8)
      ,EastLong DECIMAL(10, 8)
      ,WestLong DECIMAL(10, 8)
      );
  
  SELECT @geomenvelope = GEOMETRY::STGeomFromWKB(zn.ZoneGeog.STAsBinary(), zn.ZoneGeog.STSrid).STEnvelope()
      FROM dbo.GeogZone AS zn
      WHERE zn.Zone = 'CA-1'
  
  INSERT INTO @BoundingBox (SouthLat,NorthLat,EastLong,WestLong)
          SELECT @geomenvelope.STPointN(1).STY 
                 ,@geomenvelope.STPointN(3).STY 
                 ,@geomenvelope.STPointN(1).STX 
                 ,@geomenvelope.STPointN(3).STX 
  
  SELECT *
      FROM @BoundingBox
  </code></pre>
  
  <p>My question: Is there an alternative (easier) way to get East, West, North, South Points from my Geography Field?</p>