DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Rounding Latitude / Longitude accurately

  1. #1
    Join Date
    Jan 2004
    Posts
    17

    Rounding Latitude / Longitude accurately

    I am trying to round latitude / longitude positions to the nearest 50m accurately using Oracle Spatial. Currently I am converting each position to British National Grid to obtain coordinates in meters. This then allows me to round the X and Y coordinate to the nearest 50m. I then convert the result back to lat / lon format. This works fine for a series of lat / lon positions in Britain because the rounded positions overlay well with the original data. However, for other areas the rounded values become skewed and the overall series of positions is rotated. Obviously this is because I am using the wrong coordinate system.

    I would like to be able to round the positions to the nearest 50m without doing a coordinate system transform (stay in WGS84). Since ‘SDO_DISTANCE’ returns the distance in meters (accurate?) I am wondering if it does a conversion within the function to get the lat lon into x/y coordinates. Looking at the function within MDSYS does not help much as it is in hex.

    A simplified example of what I am doing is shown in the code below. Any help is much appreciated.


    Code:
    CREATE OR REPLACE PROCEDURE usingSpatial
     AS
    	-- stores lat / lon of each position	
    	pos1Lat FLOAT;
    	pos1Lon FLOAT;
    	pos2Lat FLOAT;
    	pos2Lon FLOAT;
    	-- distance between 2 points
    	theDistance FLOAT;
    	-- spatial
        latlon MDSYS.SDO_GEOMETRY;
        x FLOAT;
        y FLOAT;
        xy MDSYS.SDO_GEOMETRY;	
    	
        BEGIN
    
    	pos1Lat := 51.413481;
    	pos1Lon := -0.182495;
    	pos2Lat := 51.415825;
    	pos2Lon := -0.189712;
    	theDistance := 0;
    	
    	-- 8307 = WGS84
    	-- 81989 = British National Grid
    	
    	-- this gives the distance between the 2 positions in meters
    	theDistance := NVL(MDSYS.SDO_GEOM.SDO_DISTANCE
    		(mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(pos1Lon, pos1Lat, NULL), NULL, NULL),
                    mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(pos2Lon, pos2Lat, NULL), NULL, NULL),
                    0.0001),0);
    
    	-- my current method of rounding a position to a 50m grid
        
    	-- set up spatial instance
        latlon := MDSYS.SDO_GEOMETRY(2001, 8307, mdsys.sdo_point_type(pos1Lon, pos1Lat, NULL), NULL, NULL);
        -- convert to British National Grid
        xy := MDSYS.SDO_CS.TRANSFORM(latlon, 81989);
        
        x := xy.sdo_point.x;
        y := xy.sdo_point.y;
        
        --Round to nearest 50m
        
        x := 50 * ROUND(x / 50);
        y := 50 * ROUND(y / 50);
        
        xy := MDSYS.SDO_GEOMETRY(2001, 81989, mdsys.sdo_point_type(x, y, NULL), NULL, NULL);
        
        -- convert back to lat/lon format
        latlon := MDSYS.SDO_CS.TRANSFORM(xy, 8307);	
    
    
    	dbms_output.put_line('The actual distance is ' || theDistance);
    	dbms_output.put_line('Position 1 rounded ' || latlon.sdo_point.x || ' ' || latlon.sdo_point.y);
    END;
    /

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Rounding Latitude / Longitude accurately

    Originally posted by cheesyhoward
    However, for other areas the rounded values become skewed and the overall series of positions is rotated.
    Is the error of the order of 50m? (or does Rome end up near Minsk).

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (further thoughts) Does this make sense? Why round in one coordinate system to present the result in another?

    10°15'30" becomes 10°15'30.15485" rounded to the nearest 50m ! ! ! (I made up the numbers!)

  4. #4
    Join Date
    Jan 2004
    Posts
    17
    I am now selecting the correct UTM zone based on the first lat / lon position in the table. I then do a transformation to the chosen UTM zone, round to the nearest 50m and then do another transformation back to lat / lon. It works great.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width