For UTM, the linear units are in meters.
Can I change the linear units supported (kilo meter) in sdo_dist_units while transforming ?
Yes, you should be able to. If the coordinate system you transform to has different units defined, Oracle should do that for you automatically.
Do I need to create user defined coordinate system for this ?
Can I use sdo_cs.transform with user defined coordinate system defined with changed units?
That really depends on your coordinate systems. Do you know which CS you data is in, and which one you need to transform to? They may already exist in Oracle's standard library, so you don't have to create your own.
Can I update geometry SRID directly ? If yes in which situation ?
Yes you can:
update <tablename> t set t.<geometry_column_name>.sdo_srid = <your SRID number>;
But that will only work if the SDO_ORDINATES are already in the correct format. It will NOT work correctly if you want to go from geographic to projected for example. Look at the (approximate ;-) ) location where I live: Google reports that (in their own geographical coordinate system) as
Whereas the same location (in my country's local projected coordinate system) is
Simply changing the SRID in this case would not work, because the numbers in the SDO_ORDINATES are way too different. They are also in different units, the Lat/Lon is degrees, where the X/Y is meter. Now because Google's coordinate system is very similar to WGS84, you could go from Google to WGS84 by simply updating the SRID (as long as you do not need accuracy smaller than 50m, at least).
You can see this in action by trying this:
select SDO_CS.TRANSFORM( SDO_GEOMETRY(2001, 28992, SDO_POINT_TYPE(132743, 517880, NULL), NULL, NULL), 4326) from dual;
Note that there will be a difference in the results, because Google's coordinate system is not exactly WGS84, and SRID 4326 is WGS84. 28992 being the SRID for my local projection.
Start with the easy one first
Q> Can I update geometry SRID directly ? Yes SQL> update TABLENAME a set a.GEOMETRY_NAME.sdo_srid = NULL;
for example, but not to do a transformation to another system specifically. generally to do maintenance. Its not something I would expect you to do very often...
Q> Can I use sdo_cs.transform with user defined coordinate system defined with changed units?
Yes you can
For example a user defined SRID 5000099
SELECT 'Difference',SDO_GEOM.SDO_DIFFERENCE(op.shape, nfsb.shape, 0.05) shape,
ROUND(Sdo_Geom.Sdo_Area (Sdo_Cs.Transform (SDO_GEOM.SDO_DIFFERENCE(op.shape, nfsb.shape, 0.05),5000099), 1, 'unit=Acre'), 3) acres
FROM OP01 op, OP02 nfsb
WHERE etc etc ....
Q> Can I change the linear units supported (kilo meter) in sdo_dist_units while transforming ?
Not sure why you want to do this.... This is a legacy view from the Manual
The MDSYS.SDO_DIST_UNITS reference view contains one row for each valid distance UNIT specification in the well-known text (WKT) description in the coordinate system definition. The WKT is described in Section 184.108.40.206.
My view is keep it simple ... But there is nothing to stop you having linear units of Km in a user defined CS.
Hope that helps
I would take care with Google to WGS84 by simply updating the SRID - you really have to understand your accuracy requirement at Stefan states....
And why we have the
The name of the use case to be associated with the transformation. If you specify the string
USE_SPHERICAL, the transformation uses spherical math instead of ellipsoidal math, thereby accommodating Google Maps and some other third-party tools that use projections based on spherical math.
geom IN SDO_GEOMETRY,
use_case IN VARCHAR2,
to_srid IN NUMBER
) RETURN SDO_GEOMETRY;
SDO_CS.TRANSFORM_LAYER does not support the naming of units as you have tried it.
If i dont use unit parameter, it works fine.
Because that's how it is supposed to work. A coordinate system has it's units predefined, because that is usually part of it's definition. For what you want to do, I would transform to 32630, then run a little procedure that sets the units of the ordinates to kilometers. Be aware though of your accuracy in this case. It is possible to set up your own UTM coordinate system with units as kilometers, but I tend to stay away from custom coordinate systems because when you exchange data, or upgrade your database, or move from the dev-database to the prod-database nothing works anymore because the coordinate system is not there. So my solution would be very simple: stick with what's there, then use a little post-processing to get what you need.
Why do you need the ordinates in kilometers? Just out of curiosity ;-)