0

The geography data type is new to me and I'm trying to get my head around how to use it. I have a simple question I'm unable to find an answer for.

I have a database containing coordinates (Lat, Lon; decimal(9,6)) and elevation (smallint) data. Getting highest/lowest/most Northerly/E/S/W is pretty straight forward:

SELECT MAX(Lat) FROM Tbl     etc.

Geography data is stored as an object and as far as I can tell, to get the most Northerly point, I first need to convert all data to Lat/Lon/Elev and then look for the point with the highest Lat value. That seems like a lot of trouble for something which imo is supposed to be really simple. With that in mind i'm inclined to keep the existing data and add the geography, but that doesn't feel right to me: storing the same info twice in the same table.

Is there a simple way of getting the most Northerly point from a set coordinates stored as geography data?

mindcopy
  • 1
  • Possible duplicate of [Get X Y values from Point Geometry or Geography](https://stackoverflow.com/questions/10856014/get-x-y-values-from-point-geometry-or-geography) Then you can get `MAX(obj.Lat)` – Juan Carlos Oropeza Nov 13 '17 at 13:11

2 Answers2

1

You store the data as a geographic object so you can do geografic stuff like distance intersections

In my case I also keep both the x,y and the geom. So I can use index on x, y and do my geometric functions on geom.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • That's the easy solution, but we're storing the same data twice in the same table, just in a different format. It's workable of course, and I'm all for pragmatism. However, it's not really answering my question. Thanks for the input anyway. – mindcopy Nov 13 '17 at 07:13
0

A combination of Juan's answer and his comment on your question seems to be the most correct answer. To reiterate, you can use

For GEOGRAPHY:

SELECT location.Lat as Lat, location.Long as Lon from myTable;

For GEOMETRY:

SELECT location.STY as Lat, location.STX as Lon from myTable;

but it's also a good idea to consider weighing up the storage cost of keeping the Lat and Long as columns on your table against the performance cost of calculating these values on the fly.

Tangles
  • 45
  • 8