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

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Jackie Ng
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
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Romica Dascalescu
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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Dan Stoica
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Romica Dascalescu
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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Dan Stoica
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Romica Dascalescu
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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Jackie Ng
Or it could be a various bunch of non-FDO tools are creating SQL Server tables with geometry instead of geography (that SQL Server will happily accept), which is why this problem isn't happening with FDO-centric data management tools and applications.

- Jackie
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Martin Morrison
In reply to this post by Romica Dascalescu
" 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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals

_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Dan Stoica
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Dan Stoica [[hidden email]]
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: [hidden email] [mailto:[hidden email]] 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: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Tuesday, August 14, 2012 12:10 AM
To: [hidden email]
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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals

_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Jackie Ng
In reply to this post by Romica Dascalescu
Hi Romy,

Any status on this fix?

- Jackie
Reply | Threaded
Open this post in threaded view
|

Re: Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Romica Dascalescu
Hi Jackie,

Ohh, I have the fix, I have the code change, I have the review done. I just have to drop it.
I've been busy a bit I will do it shortly.

Thanks,
Romy.
________________________________________
From: [hidden email] [[hidden email]] on behalf of Jackie Ng [[hidden email]]
Sent: Friday, September 21, 2012 12:57 AM
To: [hidden email]
Subject: Re: [fdo-internals] Patch for #844: SQL Server provider flipping X/Y coordinates for geometry (SRID between 4120 and 4999)

Hi Romy,

Any status on this fix?

- 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-tp4995010p5003412.html
Sent from the FDO Internals mailing list archive at Nabble.com.
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals
_______________________________________________
fdo-internals mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-internals