Croatia/Kratki vodici/Oracle HTRS96

O
= HTRS96 koordinatni sustav i Oracle =
== Uvod ==
Kako Oracle još uvijek ne podržava novi službeni hrvatski koordinatni sustav (http://spatialreference.org/ref/epsg/3765/), a u praksi ima potrebe za ovim koordinatnim sustavom, u nastavku slijedi definicija HTRS96 koordinatnog sustava za Oracle:
-- HTRS96
-- Definicija konverzije
INSERT INTO MDSYS.sdo_coord_ops
 (coord_op_id, coord_op_name, coord_op_type, source_srid,
 target_srid, coord_tfm_version, coord_op_variant,
 coord_op_method_id, uom_id_source_offsets,
 uom_id_target_offsets,
 information_source,
 data_source, show_operation, is_legacy, legacy_code, reverse_op,
 is_implemented_forward, is_implemented_reverse
 )
 VALUES (19851, 'HTRS96 / Croatia TM', 'CONVERSION', NULL,
 NULL, NULL, NULL,
 9807, NULL,
 NULL,
 'State Geodetic Administration of the Republic of Croatia.',
 'EPSG', 1, 'FALSE', NULL, 1,
 1, 1
 );
-- Definicija parametara za konverziju
INSERT INTO MDSYS.sdo_coord_op_param_vals
 (coord_op_id, coord_op_method_id, parameter_id, parameter_value,
 param_value_file_ref, uom_id
 )
 VALUES (19851, 9807, 8801, 0,
 NULL, 9102
 );
 
INSERT INTO MDSYS.sdo_coord_op_param_vals
 (coord_op_id, coord_op_method_id, parameter_id, parameter_value,
 param_value_file_ref, uom_id
 )
 VALUES (19851, 9807, 8802, 16.5,
 NULL, 9102
 );
 
INSERT INTO MDSYS.sdo_coord_op_param_vals
 (coord_op_id, coord_op_method_id, parameter_id, parameter_value,
 param_value_file_ref, uom_id
 )
 VALUES (19851, 9807, 8805, 0.9999,
 NULL, 9001
 );
 
INSERT INTO MDSYS.sdo_coord_op_param_vals
 (coord_op_id, coord_op_method_id, parameter_id, parameter_value,
 param_value_file_ref, uom_id
 )
 VALUES (19851, 9807, 8806, 500000,
 NULL, 9001
 );
 
INSERT INTO MDSYS.sdo_coord_op_param_vals
 (coord_op_id, coord_op_method_id, parameter_id, parameter_value,
 param_value_file_ref, uom_id
 )
 VALUES (19851, 9807, 8807, 0,
 NULL, 9001
 );
-- Dodavanje datuma
INSERT INTO sdo_datums
 VALUES (6761, 'Croatian_Terrestrial_Reference_System', 'GEODETIC',
 '7019', '8901', , 'EPSG', NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, 'FALSE', NULL);
-- Definicija projekcije
INSERT INTO sdo_coord_ref_sys
 VALUES (4761, 'HTRS96', 'GEOGRAPHIC2D', 6402, 6761, 6761, NULL, NULL,
 NULL, NULL,
 'Cadastral surveys, large scale topographic mapping, engineering survey.',
 'EPSG', 'FALSE', NULL,
 'GEOGCS["HTRS96",DATUM["Croatian_Terrestrial_Reference_System",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","6761"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4761"]]',
 NULL, 'TRUE', 'TRUE');
INSERT INTO sdo_coord_ref_system
 (srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id,
 datum_id, geog_crs_datum_id, source_geog_srid,
 projection_conv_id, cmpd_horiz_srid, cmpd_vert_srid,
 information_source,
 data_source, is_legacy, legacy_code, legacy_wktext,
 legacy_cs_bounds, is_valid, supports_sdo_geometry
 )
 VALUES (3765, 'HTRS96 / Croatia TM', 'PROJECTED', 4400,
 NULL, 6761, 4761,
 19851, NULL, NULL,
 'Cadastral surveys, large scale topographic mapping, engineering survey.',
 'EPSG', 'FALSE', NULL, NULL,
 NULL, 'TRUE', 'TRUE'
 );
== Transformacija koordinata iz GK5 i GK6 u HTRS ==
=== Napomena ===
S obzirom da se za GK5 i GK6 u Oracleu koriste SRID 31275 i 31276, a koji imaju KRIVE parametre za konverziju, prvo je potrebno dodati GK5 i GK6 koordinatne sustave sa točnim parametrima konverzije (vidi http://wiki.osgeo.org/wiki/Croatia/Kratki_vodici/transformacija_koordinata_cs2cs). Slijedi definicija tih koordinatnih sustava za Oracle:
-- Definicija Gauss-Kruegerove 5. i 6. zone s ispravnim parametrima
-- GK - 5. zona
Insert into SDO_DATUMS (datum_id, datum_name, datum_type, ellipsoid_id, prime_meridian_id, information_source, data_source, shift_x, shift_y, shift_z, rotate_x, rotate_y, rotate_z, scale_adjust, is_legacy) Values (1000000124, 'Bessel 1841', 'GEODETIC', 7004,8950, , 'hr.osgeo', 550.499, 164.116,475.142, 5.80967, 2.07902, -11.62386, 0.99999445824,'FALSE');
-- Geografski sustav baziran na gornjem datumu (Bessel elipsoid)
Insert into SDO_COORD_REF_SYStem(srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, datum_id, geog_crs_datum_id, information_source, data_source, is_legacy, is_valid, supports_sdo_geometry) Values (1000003006, 'Bessel 1841', 'GEOGRAPHIC2D', 6405,1000000124, 1000000124,, 'hr.osgeo', 'FALSE', 'TRUE', 'TRUE');
-- GK5 pojekcija bazirana na gore definiranom geografskom sustavu
Insert into SDO_COORD_REF_SYStem (srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, geog_crs_datum_id, source_geog_srid, projection_conv_id, information_source, data_source, is_legacy,is_valid,supports_sdo_geometry) Values (478005, 'GK zone 5', 'PROJECTED', 4499,1000000124, 1000003006, 18275,, 'hr.osgeo', 'FALSE', 'TRUE', 'TRUE');
-- GK - 6. zona
Insert into SDO_DATUMS (datum_id, datum_name, datum_type, ellipsoid_id, prime_meridian_id, information_source, data_source, shift_x, shift_y, shift_z, rotate_x, rotate_y, rotate_z, scale_adjust, is_legacy) Values (1000000125, 'Bessel 1841', 'GEODETIC', 7004,8950, , 'hr.osgeo', 550.499, 164.116,475.142, 5.80967, 2.07902, -11.62386, 0.99999445824,'FALSE');
-- Geografski sustav baziran na gornjem datumu (Bessel elipsoid)
Insert into SDO_COORD_REF_SYStem(srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, datum_id, geog_crs_datum_id, information_source, data_source, is_legacy, is_valid, supports_sdo_geometry) Values (1000003007, 'Bessel 1841', 'GEOGRAPHIC2D', 6405,1000000125, 1000000125,, 'hr.osgeo', 'FALSE', 'TRUE', 'TRUE');
-- GK6 pojekcija bazirana na gore definiranom geografskom sustavu
Insert into SDO_COORD_REF_SYStem (srid, coord_ref_sys_name, coord_ref_sys_kind, coord_sys_id, geog_crs_datum_id, source_geog_srid, projection_conv_id, information_source, data_source, is_legacy,is_valid,supports_sdo_geometry) Values (478006, 'GK zone 6', 'PROJECTED', 4499,1000000125, 1000003007, 18276,, 'hr.osgeo', 'FALSE', 'TRUE', 'TRUE');
Dakle, za upis podataka iz 5. zone koristite SRID 478005, a za 6. zonu SRID 478006.
Primjer transformacije tablice s podacima iz GK5 u HTRS96:
create table T3765 AS select c.oid oid,SDO_CS.TRANSFORM(c.geometry,m.diminfo, 3765) geometry from T478005 c, user_sdo_geom_metadata m where m.table_name = 'T478005' AND m.column_name = 'GEOMETRY';