SAP SQL Anywhere

Question

It seems that SQLAnywhere gives incorrect results when converting from WGS84 to RT90 (a common Swedish coordinate system)

There are a couple of RT90 variants depending on location but “RT90 2.5 gon V” SRID 3021 appears to be standard for Sweden Nationally

Here is a test case:-

Search for benarp5389 in www.hitta.se
Verktyg -> Koordinater (lower right corner of map)
Move “scope” to the building
WGS84 55.85914 13.77290
RT90 6194116/1372743

Sybase Central, Interactive SQL

SELECT (NEW ST_Point( 13.77290, 55.85914 , 4326 ).ST_Transform( 3021 ));
Point (1372571.20112 6194110.71864)

So, in summary www.hitta.se 1372743 6194116 SQLAnywhere 1372571 6194110

Is this a bug in SQLAnywhere or am I doing something wrong?

PS I have tried all the RT90 SRIDs and none give the correct answer, and some actually give a DB error

PS I have verified that www.hitta.se gives correct results by crosschecking with other sites such as latlong.mellifica.se

Answer 1

2

It all depends on how you define the 3021 spatial reference system. We include the definitions from EPSG as of some point in time. Our current definition corresponds to EPSG's current definition (at this moment in time):

PROJCS["RT90 2.5 gon V",
GEOGCS["RT90",
    DATUM["Rikets_koordinatsystem_1990",
        SPHEROID["Bessel 1841",6377397.155,299.1528128,
            AUTHORITY["EPSG","7004"]],
        AUTHORITY["EPSG","6124"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.01745329251994328,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4124"]],
UNIT["metre",1,
    AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",0],
PARAMETER["central_meridian",15.80827777777778],
PARAMETER["scale_factor",1],
PARAMETER["false_easting",1500000],
PARAMETER["false_northing",0],
AUTHORITY["EPSG","3021"],
AXIS["Y",EAST],
AXIS["X",NORTH]]

I looked at the source code on latlong.mellifica.se and found that it does not match what EPSG does in the following fields:

central_meridian = 15.806284529
scale_factor = 1.00000561024
false_northing = -667.711
false_easting = 1500064.274

These parameters appear to match SRID 3847, defined as follows:

PROJCS["SWEREF99 / RT90 2.5 gon V emulation",
GEOGCS["SWEREF99",
    DATUM["SWEREF99",
        SPHEROID["GRS 1980",6378137.0,298.257222101,
            AUTHORITY["EPSG","7019"]],
        TOWGS84[0.0,0.0,0.0,0.0,0.0,0.0,0.0],
        AUTHORITY["EPSG","6619"]],
    PRIMEM["Greenwich",0.0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.017453292519943295],
    AXIS["Geodetic latitude",NORTH],
    AXIS["Geodetic longitude",EAST],
    AUTHORITY["EPSG","4619"]],
PROJECTION["Transverse Mercator",
    AUTHORITY["EPSG","9807"]],
PARAMETER["central_meridian",15.806284529444449],
PARAMETER["latitude_of_origin",0.0],
PARAMETER["scale_factor",1.00000561024],
PARAMETER["false_easting",1500064.274],
PARAMETER["false_northing",-667.711],
UNIT["m",1.0],
AXIS["Northing",NORTH],
AXIS["Easting",EAST],
AUTHORITY["EPSG","3847"]]

Please try your test with SRID 3847 and see if that resolves your problem.

If it does not resolve your problem, then you need to discover the exact spatial reference system definition you are trying to match, and then you can create a new spatial reference system (or alter an existing one) to satisfy your needs. See http://dcx.sap.com/index.html#sa160/en/dbreference/create-spatial-reference-system-statement.html*d5e46887 for details.

UPDATE: The 3847 definition is not included in the SQLA data set. The transform definition is also missing from the EPSG web site. You can, however, add it as follows:

CREATE OR REPLACE SPATIAL REFERENCE SYSTEM "SWEREF99 / RT90 2.5 gon V emulation"
IDENTIFIED BY 3847
LINEAR UNIT OF MEASURE "meter"
TYPE PLANAR
COORDINATE X BETWEEN 1392985.9154 AND 1570798.5948
COORDINATE Y BETWEEN 6208510.3345 AND 7546254.0298
definition 'PROJCS["SWEREF99 / RT90 2.5 gon V emulation",
    GEOGCS["SWEREF99",
        DATUM["SWEREF99",
            SPHEROID["GRS 1980",6378137.0,298.257222101,
                AUTHORITY["EPSG","7019"]],
            TOWGS84[0.0,0.0,0.0,0.0,0.0,0.0,0.0],
            AUTHORITY["EPSG","6619"]],
        PRIMEM["Greenwich",0.0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.017453292519943295],
        AXIS["Geodetic latitude",NORTH],
        AXIS["Geodetic longitude",EAST],
        AUTHORITY["EPSG","4619"]],
    PROJECTION["Transverse Mercator",
        AUTHORITY["EPSG","9807"]],
    PARAMETER["central_meridian",15.806284529444449],
    PARAMETER["latitude_of_origin",0.0],
    PARAMETER["scale_factor",1.00000561024],
    PARAMETER["false_easting",1500064.274],
    PARAMETER["false_northing",-667.711],
    UNIT["m",1.0],
    AXIS["Northing",NORTH],
    AXIS["Easting",EAST],
    AUTHORITY["EPSG","3847"]]'
TRANSFORM DEFINITION '+proj=tmerc +lat_0=0 +lon_0=15.80628452944445 +k=1.00000561024 +x_0=1500064.274 +y_0=-667.711 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs';

The WKT and proj4 string were obtained from http://epsg.io/3847.

I have verified that this SRS gives the answer you expect for the example provided.

permanent link

answered 16 Apr '15, 11:19

Phil Mitchell
1.9k●18●31
accept rate: 27%

edited 17 Apr '15, 10:23

SRID 3847 does not exist as a choice in SQL Anywhere 16. Create new spatial reference system would be interesting but in sybase central you can choose FILE -> NEW-> Spatial Reference System and then you can choose "Specify the well known text definition" should this be as described in

http://dcx.sap.com/index.html#sa160/en/dbreference/create-spatial-reference-system-statement.html*d5e46887

So where can I find all the necessary data needed for this definition? Which is optional? Or can I use the definition you stated above?

Again RT90 is used in Sweden but also SWEREF 99 so I can not help to belive that there has been some kind of mixup....

(17 Apr '15, 05:18) M G

Replies hidden

Hm, if I try to paste the definition by Phil or try to insert the according .PRJ file taken from http://spatialreference.org/ref/epsg/3847/prj/, I get the error message "No matching predefined spatial reference systems where found.", which prevents the creation of a new SRS... - puzzling.

(17 Apr '15, 06:28) Volker Barth

Just to add SWEREF 99 is the official reference frame used in Sweden today.

RT 90 was previously used for map production in sweden

and according to Lantmäteriet (in Sweden)

"WGS 84 and SWEREF 99 differ by a few decimetres (increasing with time as the European plate moves). Thus SWEREF 99, WGS 84 and the previous frame SWEREF 93 can be interchangeable for many applications."

see

https://www.lantmateriet.se/en/Maps-and-geographic-information/GPS-and-geodetic-surveys/Reference-systems/Three-dimensional-systems/SWEREF-99/

up in the corner you can choose other languages (English is one of them)

so beginning of the defination "PROJCS["SWEREF99 / RT90 2.5 gon V emulation" makes me a little hesitant....

(17 Apr '15, 07:30) M G

I believe that Sybase Central is attempting to modify an existing Spatial Reference System. This is useful if you just want to tweak a couple of parameters that don't really have anything to do with the projection itself. For example, you might want to modify the storage format without redefining everything else.

You can create a brand new spatial reference system as described in my updated answer. I also included a link to where to find information about this spatial reference system (and many others).

(17 Apr '15, 10:26) Phil Mitchell

The data on spatialreference.org is missing the proj4 projection string. So it's not really useful. http://epsg.io/3847 provides all of the required information.

As to the error, see my previous comment. You need to use the actual CREATE SPATIAL REFERENCE SYSTEM statement to create a new one. Perhaps Sybase Central should be enhanced to allow creation of a brand new SRS. I will make the suggestion.

(17 Apr '15, 10:30) Phil Mitchell

From http://epsg.io/3021: "Replaces RT38 2.5 gon V (CRS code 3027) from 1990. From 2003 replaced by SWEREF systems (CRS codes 3006-3018)"

And from http://epsg.io/3847: "Approximates RT90 2.5 gon V (CRS code 3021) to an accuracy of 0.2m."

On both of those links, you can see the definitions, and realize that they are completely different on the parameters I identified in my answer. More importantly, the proj.4 projection string is completely different.

So there's a good bet that any data you're looking at that is newer than 2003 will use 3847. But that's a political thing, I think. You really should try to get confirmation as to what SRS your data is using. If you take a reasonable guess and the data doesn't match, then you need to look further.

(17 Apr '15, 10:45) Phil Mitchell

Note that RT90 ( http://epsg.io/4124 ) is a spherical projection. So when www.hitta.se just labels the coordinates as "RT90" and then give what are clearly planar coordinates, they are already providing incomplete information.

(17 Apr '15, 11:10) Phil Mitchell

I have been in contact with the Swedish Map authority https://www.lantmateriet.se/ . They have given me a test case:-

Point ID: 723738 SWEREF 99 lat long: Lat= 55.785505 Long= 13.67793 RT 90 2,5 g V 0:-15 : X= 6186099.173 Y= 1366546.522

SWEREF 99 and WGS84 are essentially the same as for our purposes

Here is the WGS84 to RT90 conversion in SQLA SELECT (NEW ST_Point( 13.67793, 55.785505 , 4326 ).ST_Transform( 3021 )); Point (1366374.20086 6186094.289)

As you can see SQLA does not produce correct results

The people at Lantmateriet also may have found the reason why SQLA is producing the incorrect values:- "However, I think me and my colleague have found the reason why your transformation doesn´t give you the correct results. The transformation is made on the ellipsoid for RT 90, which is Bessel 1841, and I(t) should be made on the ellipsoid for SWEREF 99 (WGS 84), which is GRS 1980 (WGS 84)."

So any input on this?

(22 Apr '15, 03:38) M G

Replies hidden

1

Isn't that why Phil has pointed out that SRID 3847 would be more fitting here (and that is based on "GRS 1980" according to his description) than SRID 3021 (which is based on "Bessel 1841")?

Just a very wild guess on my part...

(22 Apr '15, 04:02) Volker Barth

I think they just told you what I have been telling you all along. EPSG defines the spatial reference systems. Not us. The Swedish Map authority should be able to provide you with an EPSG SRID. I think you will find that it is 3847.

(24 Apr '15, 10:38) Phil Mitchell

just an update:

This is the latest answer from Lantmäteriet:

"Maybe, this is somewhat confusing but our system RT 90 has two EPSG codes 3021 and 3847.

EPSG code 3021 defines a system on Bessel 1841 (a = 6377397.155 m ; f = 1/299.1528128) with TM-parameters:

Central meridian = 15°48'29.8" E

Scale reduction factor = 1.0

False Northing = 0

False Easting = 1 500 000 m

EPSG code 3847 defines a system on GRS 1980 (a = 6378137 m ; f = 1/298.257222101…) with TM-parameters:

Central meridian = 15°48'22.624306" E

Scale reduction factor = 1.00000561024

False Northing = -667.711 m

False Easting = 1500064.274 m

You can see “the 3847”-system as an emulation of RT 90 in the “SWEREF 99-world”, we have used it for transformation of old data into the new system. The co-ordinate differences has an rms of around 6-7 cm with a maximum error of 23 cm. With this type of “transformation”, we can change from RT 90 to SWEREF 99 without any height information."

(29 Apr '15, 05:25) M G

More comments hidden

showing 5 of 11 show all flat view

SAP SQL Anywhere

SQL Anywhere coordinate conversion WGS84 to RT90 does not work

Follow this question

Related questions