X,Y coords reversed in WKT & WKB methods in Nov CTP?

John Cachat wrote:

Do FME and Manifold handle this as I'm about to?

Current versions of Manifold do not reverse the order of coordinates in GEOGRAPHY values, since it is still a question of whether the SQL Server team will tie this order to the order of axes for the EPSG code being used or whether they will simply always use YX coordinate order for all GEOGRAPHY values. As soon as this question is resolved, Manifold will do whatever coordinate translation would have to be done for seamless operation.

For those that are interested in details, there is an important nuance in the above question: Manifold coordinate system code composes coordinate system definitions based on the contents of EPSG tables - Although at present all ellipsoidal coordinate systems defined by EPSG have the Lat axis before the Long axis, it can be that in the future there will be an EPSG code with the order of axes reversed (to what most people would consider "normal" order, as in X followed by Y). In that case, the question becomes will SQL Server 2008 use YX regardless of what the EPSG code says or will it do XY as that code says? Manifold has the code ready for either case, whichever path Microsoft prefers - that's how it will be done.

Wednesday, November 21, 2007 5:26 PM

John Cachat

Thank you all for the information - it was very helpful. I was not aware of the waffling going on there... And I'm glad to hear you're considering the interop problem.

I do hope that the OGC considers this when describing WKB and WKT formats. There is no knowledge of the CRS inside a package of WKB or WKT data so it doesn't seem right that the receiver of that data should need to know the CRS of the data in order to properly decode it. It makes it more difficult to use these formats to exchange data between different systems (some of which may understand spatial references and some which may not). There's only 1 bit being used of the first byte of WKB data (to indicate the byte order) - the 2nd bit could be used to indicate the coordinate order...

Thanks again,

John

Saturday, November 24, 2007 5:16 AM

I loaded a shapefile into SQL Server 2008 using FME 2008 beta. The shapefile did not have a PRJ. FME loaded the data into a geometry column. I queried out the extent of the data - to build a spatial index - and I discovered the data was long/lat (GDA94). So, I added a geography column to my table and tried this:

update dbo.tas_lga set geog = geography: Tongue Tied TGeomFromText(geom.STAsText(),8311);

I got this error

A .NET Framework error occurred during execution of user defined routine or aggregate 'geography':

System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.

So, I discovered this issue by accident.

Now, my update tried to use WKT but the WKT obviously is writing longitude to x and latitude to y (standard for Oracle Spatial and PostGIS). So, unless I write my own function to swap long/lat how does one cast from one column to another without recourse to an external product like FME or Manifold GIS (I use both in my SQL Server 2008 testing)?

regards
Simon

Tuesday, November 27, 2007 5:42 PM

I'm running into this issue as well... and now it's got me second guessing the other spatial products I'm working with.

This becomes an even uglier problem when working with polygons and linestrings as a script to change the data starts to get very troublesome.

Wednesday, November 28, 2007 1:50 AM

Isaac, Steve (and others),

I think Microsoft needs to show more evidence backing up this interpretation of the specification. This interpretation will have a very high cost to customers in terms of interoperability and barrier to adoption of Microsoft technologies.

The rest of the industry has interpreted the standard as lon/lat; if Microsoft is going to interpret this differently, they should provide a bullet-proof explanation justifying why they are one of the only implementations using the lat/lon ordering in SQL.

My understanding is the majority of members of the OGC have interpreted this standard as lon/lat... so even if Microsoft is right in its interpretation, the committee has voted on the meaning of the standard with their keyboards. I think it would unreasonable for Microsoft to fight this compliance battle using its customer base--this should occur in the smokey-back-rooms of OGC meetings.

Can you show where this document, page, paragraph, and sentence, where it states the standard applies only to planar systems or otherwise excludes geographic systems? I have read the document several times and had my collegues have reviewed it as well... nothing has lead any of us to believe that geographic crs are excluded.

6.1 describes the architecture of the Geometry model, which clearly states that each geometric object is associated with a "Spatial Reference System"... it does not limit this to project coordinate systems.

Section 9 describes the WKT representation of Spatial Reference Systems. This covers both geographic and projected systems... Nowhere does it limit the specification to only planar systems.

Appendix B.8 of the SFA Part 1 states "latitude_of_origin" is "the latitude chosen as the origin of y-coordinates". false-easting/northing have a similar definition. This section contradicts any notion of using CRS ordering for X and Y, as it explictly defines X/Y in a projected coordinate system.

How does Microsoft resolve the conflict where a CRS may project the latitude into X instead of longitude? Do you defer to the definition provided in the standard, or do you defer to the best-practice?

As it's been pointed out, WKT as defined by the SFA spec does not use appropriate notation for non-planar systems... So if we were to assume Microsoft is correct and the SFA applies only to planar, then it would be non-standard to represent any geographic coordinate system using WKT as per SFA. In this case, Microsoft should defer to defacto standards.

If Microsoft chooses to go it alone in this implementation, then I would suggest making it absolutely clear that SQL Server implements the OGC 05-126 standard using the June 2005/06-135r1 best practice and that this standard is distinctly different from OGC 99-049.

In that case, then there needs to be an OGC 99-049 compatibility mode that implements the lon/lat convension. When using the OGC 99-049 standard, the lon/lat is the correct interpretation of the standard, as evidenced by the OGC certifications on other spatial products.

Failing to include an OGC 99-049 compliant implementation would create a serious interoperability problem that would prevent my organization from adopting SQL Server.

Isaac Kunen wrote:

Hi John,

We understand the interoperability problem, and we're looking into how to make bridging this gap easier. Let me try to elaborate on why we made this decision. As a starting point:

Most people think in lat/long, not long/lat. Perhaps this is only a minor concern; we could probably train those who aren't used to thinking the other way.
X/Y are not simply latitude/longitude. While many people think this way, there is really a fundamental difference between these linear planar measures and angular spherical ones. If you want to match lat/long with x/y, you can do so: reflecting your coordinates across the line X=Y should not affect any calculations.
On the flip side is the interoperablitiy question.

These taken together seem to throw doubt on the correct way to handle this. Standards, however, come into play:

As Steven mentions, the OGC SFS is a purely planar specification: nothing in it is defined on the round earth. It is, in fact, lacking when taken to the round earth. (Another interoperability concern is around ring orientation, which is required on the round earth. Which orientation is correct seems to vary across the industry and is not standardized by the OGC.)
The OGC does say that, at least going forward, coordinate order should match the CRS. See the mail Steven references, as well as the best-practices document here, which contains:

During the closing TC Plenary for June 2005 meeting, the members in attendance agreed that:

1. Going forward, for new specifications, coordinate values shall be listed in the axis order as specified by the referenced coordinate reference system (CRS).

2. Going forward, when a RWG is working on edits to an existing adopted specification related to CRS and axis order, coordinate values shall be listed in the axis order specified by the referenced coordinate reference system (CRS).

Finally, the standard EPSG list of CRSs, which we adopt for the geography type, uniformly uses latitude/longitude order for all geographic coordinate systems.

We realize that this will cause some problems for people, and we are looking into ways to mitigate them, but I hope this helps you understand our reasoning.

Cheers,
-Isaac

Wednesday, November 28, 2007 3:50 PM

Isaac KunenModerator

Hi James,

Let me first reiterate that I realize that we're doing something a bit different than the rest of the players and that this is causing some pain. We are listening to the input we're recieving on this, and we are looking at how we can ease the import/export of data between us and other systems.

That said, I disagree regarding the standards. I think they say very little about geographic coordinates, and what little they do say seem to back us up.

First, let's take a look at OGC 99-049. While I agree that they don't explicitly state that they exclude geographic systems, they do say that "features are based on 2D geometry with linear interpolation between vertices" [beginning of section 1]. This indicates to me that they are working on the plane.

Also, if we assume the specification covers geographic coordinates, we find that the specification is woefully inadequate. E.g.:

The specification does not define a geographic edge. An edge is defined by the linear interpolation between points, but they can't mean this to hold on the ellipsoid. They provide no definition whatsoever for the ellipsoid, and unlike on the plane such a definition is by no means trivial.
The specification includes no discussion of ring ordering, yet polygons are ambiguous without it. E.g., take the region desribed (in lat/long coordinates) by: POLYGON((0 0, 0 90, 0 180, 0 270, 0 0)). Does this mean the northern or southern hemisphere? (For more discussion of this, see here.)
The standard separates out interior and exterior rings. While this makes sense on the plane---the exterior ring contains all of its interior rings---it does not on the ellipsoid, where any ring in a polygon can be taken as the exterior ring.

Also, keep in mind that although we are trying to stick to 99-049 as much as possible with our geography type, we are not aiming for compliance; that is only a hard goal with our geometry type. We would argue that compliance with 99-049 for geographic systems doesn't make much sense, and we would love to see a standard that addressed geographic systems.

Finally, regarding 05-126, while the quote you pull is accurate, I don't see it as relevant: I don't think anyone is arguing that in (most) map projections, latitude is represented along the y-axis of the plot. I do find it interesting that section 6.4.2 states, "A Spatial Reference System, also referred to as a coordinate system, is a geographic (latitude-longitude), a projected (X,Y), or a geocentric (X,Y,Z) coordinate system." [emphasis mine]

Cheers,
-Isaac

Wednesday, November 28, 2007 6:52 PM

Isaac,

With OGC 99-049, the OGC has certified other systems for use with geographic coordinate systems using the lon/lat axis ordering for geographic systems. Even if your interpretation is correct by the letter of the standard, it is incorrect based on the practice, application and testing of the standard. The committee has demonstrated its intent to apply this standard to geographic systems.

Yes--you are absolutely right that 99-049 is woefully inadequate for geographic coordinates... If I made it sound like I believed otherwise, I appologize as that was never my intent.

However, inadequacy or ambiguity is not intent. I believe the actions of the OGC wrt 99-049 does show the intent of the OGC when implementing this standard--and nothing you've said shows otherwise (you have pointed out what I suspected--the OGC stnadards are half-baked and need a lot of work to be "good"). Unless you can show that the OGC incorrectly certified other systems or that their certification excluded the geographic portions of implementions, then I think Microsoft's position is still wrong.

The quote is quite relevant. I am pointing out there is ambiguity within the standard on geometric systems--not just in geographic systems. How will you address those ambiguities and/or conflicts? Or, more importantly, will you choose an implementation that is different from the rest of the industry?

If you are infact working towards 99-049 (Simple Features Specification for SQL) compliance, then the June 2005 meeting would not apply (as its a standard that pre-dates that meeting--updates don't count as new). If you are working towards 05-126/06-104r3 (Implementation Spec for Geo Info - Simple Feature Access), then then June 2005 meeting might apply since it is a new standard that superceded 99-049.

I still believe the best solution is to allow customers to choose whether they want to use the deprecated 99-049 as implemented by the rest of the industry or 06-104r3 as it is implemented in Katmai as of the Nov CTP.

Please be sure to reflect which standard you are supporting in your help docs, as they are claiming 06-104r3.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_Btrfmisc/html/c0c0f441-bf33-410c-9df0-544e3d05b944.htm

Friday, November 30, 2007 5:53 AM

I can't add much to the discussion about standards. Over the years, as a GIS Manager, I kept my eye on them to see whether they would be of use in the organisation I worked for, but found them only useful in the area of basic (I mean basic) interoperability.

Now what I expect from Katmai is functionality and flexibility from the persepective of a self-contained database. The problem I have based on my limited experience with the lat/long coordinate ordering in the geography type is less the ordering (Note: Google Maps uses Lat/Long - what does Virtual Earth use?) but that there are no functions available to map between geometry and geography or a flexible API to access the geometry/geography objects. For example, I cannot currently update the coordinates of a geometry/geography except via hacking WKT strings (which are 2D limited and don't include SRIDS). Note that I said UPDATE and not READ. Yes, we can READ coordinates via STPointN but we can't easily do something like upgrade a 2D object to 3D (or reverse) except by using WKT. Note, some object processing doesn't require one know if one is processing the outer or inner ring of a polygon and a part in a multi-part linestring.

My friend Anand Kannan in MapInfo sent me some T-SQL to flip the XY, Lat/Long which relies on string processing WKT. I looked at writing a similar function and came to the same conclusion: it would require the hacking of strings (Anand's script arrived the day after I had decided that I was not going to go down this route). THis is ugly, slow and has other difficulties (eg internationalisation and the use of commas as decimal points in Europe etc).

I have written lots and lots of additional functions of my own in Oracle's PL/SQL precisely because I can access every aspect of the storage format. I don't want to have to write C# (to read/write WKB or WKT) and deploy the code into the database if I can help it.

So, if we are stuck with lat/long ordering my main plea is to see a lot more power deployed into the database for T-SQL processing via a much richer API that is currently covered by any OGC or SQL/MM standard. I for one don't want to have to coordinate a fat-client GIS package (fat-client is not meant to be derogatory) and a database process in order to process data.

Just my 2c worth.

regards
Simon Greener

Saturday, December 01, 2007 12:13 AM

Simon Greener wrote:

(Note: Google Maps uses Lat/Long - what does Virtual Earth use?)

Simon,

Good to see you here! :-) But I must disagree with the above: The order of coordinates for "Latitude / Longitude" (also known as "lat/lon") projection in KML defined by Google is lon / lat.

See http://code.google.com/apis/kml/documentation/kml_tags_21.html

Let me switch gears to defend Microsoft, which I think is being unfairly distracted in this thread with discussion about parsing OGC mush.    That's not the point. The point is about a new type, GEOGRAPHY, that can make life easier for some users. That's a different mission than something like GEOMETRY provided for use with existing GIS data by experienced GIS people.

I get the feeling that some folks reading this thread are new to geospatial data and don't realize that the common English phrase "Latitude / Longitude" data is exactly opposite to the way the data is actually ordered in the world of GIS data. You are not alone if that is what you expected.

That's not intended as a criticism, because instead it is a very solid argument for having the coordinate ordering the way it is in the GEOGRAPHY type if that is indeed intended for non-GIS people.   If the point of having GEOGRAPHY is to have something for non-GIS types the (lat,lon) ordering makes perfect sense.

The most natural thing in the world for someone new to geospatial data is to think, nay, to *expect* that when someone says they have "Latitude / Longitude" data that the coordinate ordering for that data is indeed in (lat,lon) order just like the English phrase commonly used to describe it says it is. People familiar with geospatial data understand that the English phrase is just a phrase, not a mathematical description of coordinate axis ordering, but beginners usually do not know that. Manifold has sold more heavy-duty GIS to new users than all other GIS vendors put together, so I assure readers this is a very real phenomenon.

Part of what SQL Server can do by adding spatial extensions is broadening the benefit of geospatial technology to a much wider audience. Microsoft has the reach to do that as other vendors cannot. Reconciling the expectations of geospatial beginners (even if they may be experts in DBMS and not at all beginners in programming) with the accumulated practice within terabytes of GIS data is not easy, but having two types is a useful innovation that can make it happen.

If Microsoft can make life easier for people new to geospatial data with coordinate ordering in GEOGRAPHY that's different from legacy GIS data, well, that's perfectly OK and a good enough reason for me. Whatever it is, we will support it. Microsoft has also implemented GEOMETRY for those using GIS data so there is no downside for those who are working with GIS data.

As I have nagged before, if you are using GIS data you had better be storing it in GEOMETRY so you don't lose essential information, like datum, required for accuracy.   Use GEOMETRY with GIS data, whether it is "Latitude / Longitude" projection or not, and there is no issue whatsoever with coordinate ordering.   Therefore, my advice to Simon would be to use GEOMETRY and be happy you have that.

Regards to all,

Dimitri

A postscript for those who like discussions of technical minutia:

Just so everyone knows I'm not capriciously arguing that the ordering in GEOGRAPHY is OK because I don't understand that (lon, lat) ordering is universal, let me cite some examples and correct some misconceptions that may have crept into this thread, beginning with the notion that anyone should care what OGC does or misconception over what GML or EPSG specifies.

First, OGC "standards" are nearly useless in real life.   Isaac hit the nail on the head when he wrote "Also, if we assume the specification covers geographic coordinates, we find that the specification is woefully inadequate."   Well said!

Speaking of nearly-useless OGC "standards", that brings up GML, which has been famously described as "perhaps the worst format for geospatial data, second only to braille."   Given that GML represents perhaps less than a millionth of the geospatial data out there I don't think it matters at all in a practical sense what coordinate ordering GML uses. But, if we are splitting hairs and pretending that GML matters, I must respectfully disagree with the suggestion that GML specifies (lat, lon) ordering.

The GML standard states that the order of coordinates should follow that of the projection. Not very useful from a practical point of view. So although GML does include means to explicitly specify the order of coordinate axes, it does not explicitly choose (lat,lon) order over (lon, lat) order. In any event the use of GML for lat/lon data is so rare we have yet to see a real-world GML file with lat/lon data regardless of coordinate order.

Regarding EPSG, I also respectfully disagree this is normally treated as (lat,lon) ordering: The EPSG guys designed their system so that the axes used by a coordinate system are numbered. I have never heard of anyone (Oracle, etc., ) reflecting the order of these axes on actual coordinate values. I can see the point of SQL Server doing it as it does in GEOGRAPHY and I respect that 100%, but I think all the justification one needs is that is how it is done in SQL Server, as how it is done there becomes the de facto standard we will follow.

I have been challenged in the past for stating that (lon,lat) ordering is "universal" practice in GIS data, so let me be specific why I state this is the case: Other notable formats that use (lon,lat) ordering for so-called "Latitude / Longitude" projections:

1. Coordinates in TIGER/Line data sets produced by the US Census Bureau are in lat/lon. The order of coordinates is lon/lat.

2. Coordinates in NTAD data sets produced by the US Bureau of Transportation Statistics are in lat/lon. The order of coordinates is lon/lat.

3. Coordinates in GDF and other data sets produced by TeleAtlas, a large European mapping company, are in lat/lon. The order of coordinates is lon/lat.

4. Coordinates in VMap data sets produced by the US Department of Defense may be in several different coordinate systems. For lat/lon coordinate systems the order of coordinates is lon/lat.

5. Coordinates in SHP (ESRI "shapefiles"), MIF (MapInfo) and other files produced by current GIS packages may be in many different coordinate systems. For lat/lon coordinate systems the order of coordinates is lon/lat in those systems.

6. Coordinates in DGN (Intergraph, Bentley), DWG (Autodesk and many others), DXF and similar files produced by current CAD packages may be in many different coordinate systems. For lat/lon coordinate systems the order of coordinates is lon/lat.

7. Coordinates in WKB / WKT values stored in traditional databases such as Oracle, DB/2, PostgreSQL and MySQL (which have no equivalents to the GEOGRAPHY type but are nonetheless used to store a lot of data in current circulation) may be in many different coordinate systems. For lat/lon coordinate systems the order of coordinates is lon/lat.

8. Depicting coordinates in a raster (Virtual Earth screen displays being an example) nearly always follows XY notation, which for lat/lon rasters nearly always means lon/lat.

9. When people start using SQL Server 2008 for storing spatial data, many of them will use the GEOMETRY type even though their data is in lat/lon, for various reasons. For these people the coordinate order will again be lon/lat and that order will be preserved when that data is used in GIS products.

Whew! ... that's what I mean by "universal" usage, as the above formats comprise, what? 99.99%? of the data out there. If anyone can point to even a single repository of "Latitude / Longitude" data that uses (lat, lon) ordering instead of (lon, lat) ordering, I'd be very grateful to see the URL to that repository.

For all the above, Microsoft's choice of ordering for GEOGRAPHY is spot on if, indeed, this can help folks new to geospatial data who take the phrase "Latitude / Longitude" literally, as they usually do.   As proud as anyone might be about GIS usage to date, even 100% of all that data out there is not 1% of what is coming as geospatial technology goes mainstream, so what people new to the art expect is not something lightly to be discounted.

If it was me I probably would have been conventional and stuck to (lon, lat) ordering. But then there is no doubt I would have taken on the task of explaining over and over again to hundreds of millions of people in the future (not one of whom will be reading any documentation) that by "Latitude / Longitude" I really meant them to understand that they should be putting their coordinates in "Longitude / Latitude" order. Anyone who criticizes Microsoft in this choice should think twice about how they would handle that.

Saturday, December 01, 2007 12:33 AM

Dimitri,

Good posting.

To be open and honest I don't really care that much if it is long/lat or lat/long in geography. All I have want is it to be documented and for decent API tools inside the database to handle conversion between geography and geometry. I know Manifold GIS will handle the conversion properly as does FME: I just want something I can use in T-SQL.

Now, one minor point: in my post I said Google Maps uses LatLong ordering and not Google Earth's KML.

========================================== Google Maps API
function initialize() {
var map = new GMap2(document.getElementById("map_canvas"));
map.setCenter(new GLatLng(-43.01405600008761, 147.2050559998929), 13);
}
========================================================
Yet, in Google Earth's KML, the ordering is Longitude/Latitude!
========================================== example KML
<Placemark>
<name>Simon Greener, The SpatialDB Advisor</name>
<description>Simon Greener
The SpatialDB Advisor
Solutions Architect
Digital Mapping Consultant
Spatial Databases - Oracle Spatial (main), PostGIS, MySQL, SQLServer
http://www.spatialdbadvisor.com</description>
<LookAt>
<longitude>147.2050559998929</longitude>
<latitude>-43.01405600008761</latitude>
<altitude>204</altitude>
<range>404</range>
<tilt>0</tilt>
<heading>-3.870049067063236e-014</heading>
</LookAt>
<styleUrl>#msn_icon30_copy2</styleUrl>
<Point>
<extrude>1</extrude>
<coordinates>147.2050559998929,-43.01405600008761,204</coordinates>
</Point>
</Placemark>
=====================================================

(This is the world of the non-GIS user and even it is swapped!)

regards
Simon

Saturday, December 01, 2007 1:39 AM

Isaac KunenModerator

Hi Folks,

This is a great thread. [Don't worry, I'm not shutting if down. Smile ]

I think Dimitri articulates one of our reasons very well: when the non-GIS crowd thinks of spatial data, they think lat/long---specifically lat then long. We are absolutely trying to bring this to bring this type to a larger audience, and while this ordering goes contrary to what the experts expect, it doesn't cause nearly the confusion it can cause for people who aren't used to working with this kind of information. (Pain, yes...)

That said, the simple fact that we are breaking with common practice makes interoperability a problem, and Simon is quite right to point out that ideally one shouldn't have to go through a third-party product (FME, Manifold, etc.) or even SSIS for something this simple.

At this point, I see a few options:

We could swap our lat/long interpretation to long/lat throughout. This has the advantage of aligning with the rest of the GIS industry. On the other hand, anyone building on the early CTPs will have to switch over, and we may have problems with non-geographers. Additionally, I'll keep blowing against the wind by noting that, despite industry practice, we'll be out of line with what few standards seem to exist on this point.
We could add a few additional methods that read/write to/from WKT and WKB with long/lat coordinate order. I'd be inclined to impliment only four new methods; I would not add a new version of STPolyFromText, for example, only the general geometry version. This seems like the least disruptive change to me, but still seems to satisfy the interoperability scenarios.
We could do (1) and (2): swap the behavior of the default methods as well as introduce a limited number of new methods that operate on lat/long order. My feeling is that this offers little benefit over (2), and has most of the disadvantages of (1).
We could do nothing.

I think (2) is probably the best option, but I'd like to hear your opinions. Just so everyone is clear: at this point in the release I can't promise anything beyond option (4), but that doesn't mean the door is closed.

Cheers,

-Isaac

Saturday, December 01, 2007 6:24 AM

John Cachat

This thread seems to have exploded into a much broader discussion than what I had originally intended. It's been very interesting to read the different opinions. But, I have to say that my original concern was really just about the interoperability issues.

I just want to be able to get my (geographic) data into SQL Server to take advantage of all this great new functionality.

Right now, I'm really not terribly concerned with all of the nuances of the OGC standards ("did it say geographic or not?") or of anyones's bickering on interpretations of ordering coordinates based on the description of the CRS being used. As I understand the PURPOSE of the PORTIONS of the standards that describe WKB and WKT, they are intended to provide a "well-known" (*gasp*, that's what WK means!) way to store and exchange coordinates. They are packages of data that have no understanding of projections, ellipsoids, SRIDs, CRS, EPSG, etc - just pairs of numbers stored in a way that other systems know how to decode them. The accepted/practiced/implemented/understood/taught "standard" is x,y; lon,lat; horizontal,vertical; across,up; wide,tall.

If MS wants to store their coordinates as lat,lon that's perfectly fine with me. And if they want to provide a more user friendly manner of expressing coordinates (as lat/lon), that's fine with me too. I don't understand why the emphasis is on making things more readable for people that say "lat/lon" when they are not the ones writing the code (anyone writing code can be taught that lon=x and lat=y with a sticky note on their monitor - worked for me!). Nor why we should change the ordering of coordinates when we have been taught to express them as x,y since the 2nd grade. But this is all fine too - I can adapt. The problem is that the other systems out there, and the data in them, can't...

Isaac, #2 would be great. As long as there is a way to read/write lon/lat ordered coordinates, I'm happy. And just the 4 new methods would suffice.

Thanks,

John

btw, I'm also of the opinion that WKB and WKT are insufficient for the purpose of storage and exchange. I think the SRID (or maybe even the complete description of the CRS) should be part of those packages. Without that information, the receiver of the data really can't tell exactly what it's got. But, this is what we have to work with...

Saturday, December 01, 2007 7:10 AM

Isaac,

I have no great concerns about the current Lat/Long ordering in geography (now that I remember, about 10 years ago when first working with Oracle Locator/Spatial I asked why the coordinate ordering was Longitude/Latitude - in the end, I just accepted what was given) so option 1 would be more comfortable for me I can't say I really mind either way.

Option 2 is best. Would it not be the case that if WKT supported the SRID and dimension information then we would have enough to be able to swap between them? I note that PostGIS's EWKB/EWKT add 3dm,3dz,4d coordinates support and embedded SRID information. I also note that Feature Data Objects has done something similar with their FGF binary and text formats. Perhaps the addition of functions something like this (perhaps not with the ST prefix):

geography/geometry.STAsEWKB(); geography/geometry.STFromEWKB();
geography/geometry.STAsEWKT(); geography/geometry.STFromEWKT()

might work. The FromEWKT/FromEWKB would detect the SRID, work out it was geographic or planar and then do the coordinate swap. Since you must be communicating with the OGC and SQL standards bodies can you find out what their current thinking is about WKT with SRIDs and 3/4D data?

BTW Anand@mapinfo ended up not using his WKT mashup to swap the xy coords. It turns out Spatialware has an affine function in it so he used that (a very neat solution):

set textsize 20000000
insert into block_group_text ([BLOCKGROUP], [BKG_KEY], [St_FIPS],
[MI_STYLE], [SW_MEMBER], GEOMTEXT)
exec sp_spatial_query '
SELECT [BLOCKGROUP], [BKG_KEY], [St_FIPS], [MI_STYLE],
[SW_MEMBER],
HG_AsText( HG_Affine (SW_GEOMETRY, 0.0, 1.0, 0.0, 1.0, 0.0, 0.0)
)
FROM [dbo].[BLOCK_GROUPS]

regards
Simon

Monday, December 03, 2007 6:21 PM

We agree Option 2 is best, but whatever Microsoft decides is fine by us.

I cannot resist agreeing with John Cachat's comment: "btw, I'm also of the opinion that WKB and WKT are insufficient for the purpose of storage and exchange." - very true.

Monday, December 03, 2007 9:45 PM

Isaac KunenModerator

Thanks much---we'll see what we can do.

FWIW, the sentiment regarding WKT/WKB is one that has been repeatedly voiced in our internal discussion.

Cheers,
-Isaac

Monday, December 03, 2007 11:15 PM

I second everyone's comments about the poor functionality of WKT/WKB. This is why I pointed out EWKT/EWKB in PostGIS and FGF in Feature Data Objects eg:

SRID=4326;MULTIPOINTM(0 0 0,1 2 1) -- XYM with SRID

Such an encoding would give one all the information one needed when converting between geometry and geography.

regards
Simon

Tuesday, December 04, 2007 12:55 AM

I think it's no secret that I think option 1 would be the best if you go forward with the older standard (99-xxxx)... Keep in mind that we all downloaded the CTP and agreed to the many warnings/notices/etc that things could change, etc... so at this point in the game, a slight change of this nature is reasonable.

If you choose to be the leading implementation of the latest standard (06r-xxx, I have yet to find a certified implementation on the newest standard yet--MS could set the bar!), then option 2 would be the best. The implementation of a "general" method that takes all types of WKT/WKB would be fine--no real need for the type-specific methods.

Using natural language semantics is a very weak argument for why we should settle on a particular notation. CS is already full of contradictory semantics... including SQL and MS SQL Server. There's a lot of other notations that should be changed in the name of consistency with language (graphics = height/width or y/x, south = positive, list[0] == list[1], etc).

Personally, I think ordering is a *very* minor obstacle to the average person using the spatial features... Most people barely know what latitude and longitude really are, so a minor issue like ordering is the least of their misunderstandings. I think the bigger problem will be getting them to grok the difference between geographic vs geometric... most people I've encountered (including many I consider way smarter than me) don't even realize that a 2D map is a total lie.

If you can teach that to the average Joe Gunchy, then I commend you.

Tuesday, December 04, 2007 6:45 AM

Morten NielsenAnswerer

Isaac: I'd like to question your argument that non-geographers actually thing lat/long and not the opposite. Where do you get that fact from? Did you actually do a survey? On the contrary geographers are probably the few people who would think differently, but they are also the ones that know that computers use the X/long then Y/lat order. Everything we learned in school was in this order too, and my take on it is that most people are used to it this way, no matter what coordinate system we talk about.

I know you argue that Spherical is very different (and of course it is), but it really isn't for this case. I mean, how often do you see a representation of Earth with Greenwich at the top of the map and the Dateline at the bottom? We all think of north as up and east as left when we try to imagine the earth's spherical coordinate system. Of course map projections distort the earth in many ways, but rarely it deviates from north being (somewhat) up (polar projections is an exception, but then again any projection is a planar coordinate system and besides the point here).

I think consistency is more important here. I can't recall a single application that actually stores geographic data in lat/long instead of long/lat. At most its being used for a formatted output, but that's a completely different story.

Furthermore the fact that I had to write a bunch of code just to rotate my geometry (or alternatively extend my WKB/WKT converters) just to support both of your datatypes is just a waste of both my time and the CPU and unnessesarily complicates the code. And before that I had to spend a lot of time actually realizing that you chose to do things differently with Geography than with Geometry.

John Cachat puts it very well that Well-known Binary/Text is well... "Well-known", and Microsoft shouldn't just go and change that well-known format to their liking. Maybe the OGC standard is slightly vague here when it comes to spherical coordinate systems, but all the other WKB/WKT readers agreed on this one.

I also agree completely with James: The new GIS user will quickly get past the problem of swapping lat and long. That's not their hurdle. They will instead have to struggle with stuff like ring ordering, datums, projections etc (which makes me think they will quickly run scared over to the Geometry type instead). The real problem here is reuse of existing code, easy of development and consistency.

I vote for option (1) of Isaac's options; that is: always long/lat for WKB and WKT. I don't see any reason to have multiple methods for doing more or less the same thing. Keep it simple and don't mess the API up with a bunch of overloads. You can always add methods later if you REALLY need them, but you can never remove them again later.

Tuesday, December 04, 2007 7:23 PM

JasonFollas

The most flexible solution might be to simply add an additional (and optional, if you have that ability) parameter on the methods to indicate which lat/long ordering is being used in the WKT/WKB. Regardless of which order you finalize on, this approach would allow for conversions to/from the "other" system using the exact same method names, so long as the developer is aware of the requirements of that other system. I also like the parameter idea, over separate methods, because this can be data-driven at runtime without using IF/ELSE logic.

And, personally, I would be less concerned with breaking someone's code developed against a CTP than being inline with a majority of the existing implementations. Such is the expected nature and risk of developing against a CTP release.

-Jason Follas

MVP - SQL Server

Tuesday, December 04, 2007 7:26 PM

Morten NielsenAnswerer

Adding an extra parameter to the WKB/WKT methods will break the OGC compatibility, and OGC compatibility is what this whole discussion is about.

Wednesday, December 05, 2007 12:09 AM

Responding to Morten:

"Isaac: I'd like to question your argument that non-geographers actually thing lat/long and not the opposite. Where do you get that fact from? Did you actually do a survey?"

I think that was me that first ventured that argument so let me respond. Yes, that happens all the time and it is based upon our experience at Manifold. We far outsell (in numbers of units) all other GIS vendors put together in terms of providing sophisticated GIS to new users so we often encounter viewpoints from people who are completely new to geospatial work, however sophisticated they are in other technology. Believing that "Latitude / Longitude" or "lat/lon" means the latitude coordinate comes first and then the longitude coordinate is common, even with programmers.

That happens even with very sophisticated organizations sometimes since, frankly, if you are ramping up a major project you will find it nearly impossible to find gifted programmers who are also highly experienced in geospatial work. They will all be geospatial beginners and will *not* know what the conventions are in GIS software or other geospatial usages. When they go out to research "how it should be done" they will at times run across "Latitude / Longitude" and think that's the way it should be literally. Many are not native English speakers so it's especially easy in that case for them to think that is a literal description.

For that matter, there is nothing particularly mathematically pre-ordained about lat/lon (or, should I write lon/lat?) usages. To take the most notorious example, NASA uses different coordinate systems for the planets than the rest of us: It's common to have 0 to 360, +/- 180 and other representations of longitude, a mix of decimal degrees and degrees-minutes-seconds and so forth. And then again there's that darned datum and other matters, such as the epsilon implied by data and computations and other such factors ignored even by WKB/WKT. Nothing "well known" about it! :-)

All I'm saying is that these are real considerations and you can make an argument either way for how to deal with them. It is a tribute to the very broad set of audiences Microsoft serves that they are aware of this, and also a very good sign that there is fast reaction to feedback from the CTP.

Wednesday, December 05, 2007 12:26 AM

Morten NielsenAnswerer

Thanks Dimitri. I knew you wouldn't miss a chance to mention Manifold yet again :-) I know you guys are set up to do it both ways, and I can easily make the code that does the same thing. The real question is why should I even have to bother with it? It should just work!

Even though some people get lat/long confused, I don't buy the argument that ignorance should be the reason to break with industry standards. This is somewhat the same as if we chose to swap divisor and dividend just because some people couldn't figure out what comes on top of what.

The problem with novice users is not whether they think lat comes before long, but because they can't figure out which one is north/south and which is the east/west.

Shouldn't we not give the users a little more credit? I mean, it's not exactly people with the IQ of a doorknob who will be working with direct database access. They will figure this out very quickly and the error message is very helpful. What confused me about the error message was that I was inputting data exactly as I did with GEOMETRY, and suddenly that didn't work for GEOGRAPHY.

My main argument is that this is inconstent with the geometry type and how everyone else does it, and this is prone to cause more confusion.

Thursday, December 06, 2007 12:49 PM

Stevepmo

As a current SQL Server DBA and a former GIS programmer I have been awaiting the release of the spatial support eagerly. Having read the whole thread I cannot really see any reason for Microsoft to deviate from the standard industry practice of using long/lat. It just seems so unnecessary, I can't see any good technical justification in the thread above.

Option 2 is a minimum & personally I would like to see Option 1.

I see this as a bit analagous to the QWERTY keyboard layout, we all know that the original reason that this layout was choosen is long superceded (to avoid jamming on a manual typewriter) & we all know that better layouts are possible (faster typing speeds & fewer mistakes) but to support the existing population of keyboard users we continue with the existing established layout.

regards

Stephen

Friday, December 07, 2007 5:49 AM

Morten Nielsen wrote:

Even though some people get lat/long confused, I don't buy the argument that ignorance should be the reason to break with industry standards.

Well, perhaps a bit harsh. :-) I hear your point but I don't think choosing one way or the other depends upon ignorance or being confused about what's lat, long, x or y or "industry standards." GEOGRAPHY is an innovation with legitimate roots in a serious desire to broaden geospatial data utilization. Whether it uses XY or YX coordinate ordering doesn't matter in any real technical sense as dealing with it either way programmatically is trivial.

FWIW, while I accept that reasonable people can have different opinions about the logic behind it, coordinate order in GEOGRAPHY makes perfect sense to me the way it is. If anyone feels strongly otherwise because of "industry standards" in GIS, OK, no problem. There is GEOMETRY to use for a perfect match to legacy data, GIS paleoformats, OGC aspirations to relevancy and all that.

In related news, Manifold has today released an update that automatically swaps coordinate order on the fly when reading from or writing into GEOGRAPHY type. No big deal, easy to do. :-)

Friday, December 07, 2007 6:04 AM

Morten NielsenAnswerer

Dimitri: I'm sure Manifold is the best system in the world since it can swap a set of coordinates, but you are completely missing the point here. It's not about whether I can swap them - as you say that is a trivial task - or whether its string representation is written with latitude first. It's about why the two geometry types should be treated differently. It's about why I should even be bothered with the extra code and overhead, or why I should make my implementation dependent on which geometry type I'm using. It's basically about making a clean logical API that follows a set of standards. I know that you guys at Manifold loves to make everything different to create a way too steep learning curve, but I happen to like simple, logical and consistent. Having Geography and Geometry be different at places where it doesn't make sense is not! Of course in areas where planar and spheric is inheritently different, it makes sense to have different implementations, but axis order is not one of those cases. All I'm interested in is that Sql Server gets it right, because once the RTM is out this will never change, and I honestly believe they got this one completely wrong.

Friday, December 07, 2007 8:25 AM

Stevepmo

But Geography is for spherical coordinates and Geometry for Planar ones, in what way are these interchangeable ?

Friday, December 07, 2007 5:34 PM

Morten NielsenAnswerer

I'm perfectly aware of the difference between the two types (I've written a projection library from scratch that converts between spherical, geocentric and planar projections and blogged about the coordinate system types), but as I argumented earlier, there are several things that are conceptually the same.

KISS is all I'm saying.

Friday, December 07, 2007 5:43 PM

Please explain Dimitri. How could I substitute the geometry type for geography if I insisted on using the lon/lat ordering? How would I go about getting the correct answers from my distance calculations when using geometry?

You are correct--coding a flip-bit that will alter the ordering is quite trivial--in well engineering software applications... but not everyone has that luxury. And that is the crux of this issue for me.

So when I export my WKT/WKB representations and send them to the analysts and modelers to import into their code, how do I indicate to them which ordering the fields are in? I guess I have to kludge in another field or some indication in the file name... And who will take on the expense of modifying the huge bodies of home-grown code out there that's depending on the well-known ordering in WKT/WKB? My matlab skills are pretty rusty... and the analysts/modelers are too overworked to spend time doing that as well. I guess that "trivial" modification is going to cost me weeks of time looking through spagetthi code.

I guess all of those existing efforts as well who don't have funding for developers are just gonna have to do without... sure, a script to fix the data is easy to write, but we have charge our time per project. No account code, no script. No script, no data. Since this is so trivial, is Manifold offering to create/write/distribute/maintain such custom scripts, free of charge?

And everytime I get a database export that's missing the indicator (or the lineage of what database it came from is missing), I guess I'll have to call and explain to them over and over and over the importance of data lineage--not just real-world sources, but also the technology where the data was stored. I hope someone out there has some good relationships with the IC/DOD community because I'm not influential enough to get them to change that.

Trivial in difficulty? Yes. Trivial in effort? Probably. Trivial in cost? No.

Saturday, December 08, 2007 12:39 AM

Dimitri,

In many ways, there is nothing preordained about Latitude/Longitude just as there is nothing preordained about Easting/Northing or X/Y. But obviously Manifold's many, many customers ;-) have made the switch because then they look at the coordinates of a selected feature in manifold (can't find an image upload for this post), Manifold displays them in two columns X/Longitude and Y/Latitude.

I've changed my mind: I vote for switching the coordinate ordering: option 1.

regards
Simon

Saturday, December 08, 2007 1:39 AM

Morten NielsenAnswerer

Simon: You actually bring up a REALLY good point. I can't believe I forgot about this. For UTM projections (which is using a planar coordinate system), it's common to write Northing before Easting, but hey guess what... no one chose to swap X and Y for that projection when we input it, so why should we do that for longitude/latitude?

Saturday, December 08, 2007 7:45 AM