[fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Wed Aug 15 10:07:49 PDT 2012

Regarding the functionality, SqlServer 2008 doesn't support most of the spatial operators for the geography type. However, I found that they *are* supported by the SqlServer 2012 release. A recent fix in the Trunk added support for these spatial operators newly supported. 

-----Original Message-----
From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Martin Morrison
Sent: Wednesday, August 15, 2012 8:17 AM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

" I can see that used mainly when user wants more functionality offered by a geometry which is not available for geography."

This ***should*** never happen.  If a user wants the functionality offered by the geometry type, they should convert their data to a geometry type.   

Martin Morrison
Application Engineer
Engineering Design Systems, Inc.
3780 Peters Creek Rd Ext SW
Roanoke, VA  24018
540.345.1410
gis.edsi.com

-----Original Message-----
From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Romica Dascalescu
Sent: Tuesday, August 14, 2012 12:31 PM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

I know the differences between geometry and geography, however we should not assume data from a geometry column are lat-long based SRID. User can have any SRID wants in a geometry column and ignore the fact the data might be lat-long.
I can see that used mainly when user wants more functionality offered by a geometry which is not available for geography.

Thanks,
Romy.
________________________________________
From: fdo-internals-bounces at lists.osgeo.org [fdo-internals-bounces at lists.osgeo.org] on behalf of Dan Stoica [dan.stoica at autodesk.com]
Sent: Tuesday, August 14, 2012 12:27 PM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

I'm aware that SqlServer is not strict about inserting lat/long data into 'geometry' columns.

What I meant is different, please see "Differences Between the geometry and geography Data Types" at http://msdn.microsoft.com/en-us/library/bb964711.aspx

Thanks,
Dan.

-----Original Message-----
From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Romica Dascalescu
Sent: Tuesday, August 14, 2012 12:11 PM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Hi Dan,

There is no restrictions related to SRID when the column is a geometry column from MS part.
So in the provider we must accommodate this rule by checking the type of the column (geometry/geography) and not the SRID.

Thanks,
Romy.

________________________________________
From: fdo-internals-bounces at lists.osgeo.org [fdo-internals-bounces at lists.osgeo.org] on behalf of Dan Stoica [dan.stoica at autodesk.com]
Sent: Tuesday, August 14, 2012 12:05 PM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Nice finding, Romy. Thanks. I've missed to notice that in the snippet Jackie provided the table was created with a 'geometry' column.

I believe it's an user error not to use 'geography' type for lat/long. I didn't investigate, but is it possible to change the column type for an existing table?

-----Original Message-----
From: fdo-internals-bounces at lists.osgeo.org [mailto:fdo-internals-bounces at lists.osgeo.org] On Behalf Of Romica Dascalescu
Sent: Tuesday, August 14, 2012 11:39 AM
To: FDO Internals Mail List
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Hi Jackie,

I had a look in the code and you are right there is a problem, however the patch you provided will fix your issue but will break the geography.
The geometry convertor it works as it should for geography columns (it must switch X with Y), however for geometry properties with SRID between 4120 and 4999 will not (in geometry case there should not be any switch).
This happens mainly because the convertor will use SRID to "detect" it's a geometry or a geography column.

I will provide the right fix in the following days: we need to detect if a geometry column is geography or geometry and use that flag instead of the SRID. So we need additional methods in odbc interface to inform the geometry convertor we have lat-long or X-Y.

Thanks,
Romy.
________________________________________
From: fdo-internals-bounces at lists.osgeo.org [fdo-internals-bounces at lists.osgeo.org] on behalf of Jackie Ng [jumpinjackie at gmail.com]
Sent: Tuesday, August 14, 2012 12:10 AM
To: fdo-internals at lists.osgeo.org
Subject: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Hi All,

The SQL Server provider for FDO 3.7 introduced some new MS <-> FDO geometry format conversion code to GenericRdbms/Geometry/Fgf/AllGeometry_c.cpp that flips the X and Y coordinates of any geometry based on whether the given geometry SRID is lat-long based (SRID is between 4120 and 4999 inclusive).

We've been getting reported problems about such SQL Server geometries being rendered incorrectly in all products that use the FDO 3.7 version of the
provider:

 - MapGuide Open Source 2.4 RC1
 - Autodesk Infrastructure Map Server 2013
 - AutoCAD Map3D 2013

Thread(s):

 1.
http://osgeo-org.1560.n6.nabble.com/MGOS-SQLexpress-coordinate-flip-td4993496.html
 2.
http://forums.autodesk.com/t5/Autodesk-MapGuide-Enterprise/SQL-SERVER-SPATIAL-COORDINATES-FLIP/td-p/3576282

I've attached a patch to ticket #844 that basically disables such coordinate flipping. SQL Server geometries in the applicable SRID range (4120 - 4999) now render properly with this patch applied.

Please review. I do not know why there was a need to flip coordinates for these SRIDs in the provider, but whatever motivation there was for introducing this behaviour, it has resulted in this show-stopper of an issue.

- Jackie

--
View this message in context: http://osgeo-org.1560.n6.nabble.com/Patch-for-844-SQL-Server-provider-flipping-X-Y-coordinates-for-geometry-SRID-between-4120-and-4999-tp4995010.html
Sent from the FDO Internals mailing list archive at Nabble.com.
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals

_______________________________________________
fdo-internals mailing list
fdo-internals at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/fdo-internals