cancel
Showing results for 
Search instead for 
Did you mean: 

Spatial Data and ST_Transform

justin_willey
Participant
0 Kudos
3,995

I am having a bit of trouble getting to grips with some aspects of the spatial data support in SA. In this (hopefully) simple project, I am trying to convert latitude & longitude data to the British National Grid system.

I have loaded up the definition of the OSGB36 system - EPSG 27700 using Sybase Central which executed this:

CREATE SPATIAL REFERENCE SYSTEM "OSGB 1936 / British National Grid" IDENTIFIED BY 27700 ORGANIZATION 'EPSG' IDENTIFIED BY 27700 TYPE PLANAR AXIS ORDER 'x/y/z/m' SNAP TO GRID 0.00001 COORDINATE X BETWEEN 1393.01957762812 AND 605500.277028523 COORDINATE Y BETWEEN 21478.1346584389 AND 1223382.6240472 POLYGON FORMAT 'EvenOdd' STORAGE FORMAT 'Internal' DEFINITION 'PROJCS["OSGB 1936 / British National Grid",
    GEOGCS["OSGB 1936",
        DATUM["OSGB_1936",
            SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],
            AUTHORITY["EPSG","6277"]],
        PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],
        AUTHORITY["EPSG","4277"]],
    UNIT["metre",1,AUTHORITY["EPSG","9001"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",49],
    PARAMETER["central_meridian",-2],
    PARAMETER["scale_factor",0.9996012717],
    PARAMETER["false_easting",400000],
    PARAMETER["false_northing",-100000],
    AUTHORITY["EPSG","27700"],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH]]' TRANSFORM DEFINITION '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs +towgs84=446.448,-125.157,542.060,0.1502,0.2470,0.8421,-20.4894 +no_defs';

and created a table for the data:

CREATE TABLE "DBA"."TestSpatial" (
    "ID" BIGINT NOT NULL DEFAULT AUTOINCREMENT,
    "Descrip" CHAR(100) NULL,
    "WS84Pos" ST_Point(SRID=4326) NULL,
    "OSGBPos" ST_Point(SRID=27700) NULL,
    PRIMARY KEY ( "ID" ASC )
) IN "system";

I then insert a point that falls within the valid OSGB area:

insert into TestSpatial(Descrip,WS84Pos) values ('Keptie St',new st_point(56.560067, -2.595182,4326));

These co-ordinates came from Google Maps which uses WGS84 so 4326 should be the right SRID.

But when I run:

 select WS84Pos.St_Transform(27700) from TestSpatial where ID=1;
I get
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.

Value 8431787.122429 out of range for coordinate x (SRS bounds [1393.019578, 605500.277029] exceeded by more than 50%) SQLCODE=-1484, ODBC 3 State="HY000"

the correct answer would be approx 363516 741058

There is another question on the forum about issues with ST_Transform. In that case the problem was with the definition of the reference system. In this case the parameters used by Sybase Central seem to agree with all the published descriptions of OSGB that I can find.

Am I going about this the right way?

Many thanks.

View Entire Topic
justin_willey
Participant

I hadn't had a chance to look at the spatial capabilities for a while but, I when came back to this a year later the answer turned out to be rather embarrassingly simple: the point I was inserting had its co-ordinates the wrong way round:

insert into TestSpatial(Descrip,WS84Pos) values ('Keptie St',new st_point(56.560067,-2.595182,4326));

should have been

insert into TestSpatial(Descrip,WS84Pos) values ('Keptie St',new st_point(-2.595182,56.560067,4326));

So I was trying to find the British Ordnance Survey grid reference for somewhere nice and warm (if a little pirate-infested) between Somalia and the Seychelles instead of that of a rather good fish & chip shop in Arbroath in north-east Scotland. With the correct latitude and longitude, I get an answer very close to what I expected: 363516.289 741058.37718.