title: Oracle Spatial. Get the Projected coordinates [local SRID] and Geographic coordinates [WGS84] from an area centroid
body: <p>Newbie here - excuse me for any misdemeanor</p>
  
  <p>I’ve done a sql script to get the Projected coordinates (<strong>M</strong> and <strong>P</strong>) as well as the Geographic Coordinates (<strong>LONG</strong> and <strong>LAT</strong>) from a (area) centroid from table <strong>AETA</strong>, as well as another attribute (<strong>CONCELHO</strong>), from the view <strong>VAETA</strong>.</p>
  
  <p>The base geometry is in table (AETA). This table is comprised of 2 attributes [IPID, GEOMETRY] and contains area type geometries.
  The view (VAETA) is related to table AETA – they share the IPID attribute.</p>
  
  <p>Here’s the script:</p>
  
  <pre><code>SELECT A.ipid, (SDO_GEOM.SDO_CENTROID(SDO_GEOM.SDO_ARC_DENSIFY(A.GEOMETRY, m.diminfo, 'arc_tolerance=0.05'),1).sdo_point.x) as M, SDO_GEOM.SDO_CENTROID(SDO_GEOM.SDO_ARC_DENSIFY(A.GEOMETRY, m.diminfo, 'arc_tolerance=0.05'),1).sdo_point.y) as P, A.CONCELHO, TRANSFORM_LONG_LAT_PT((SDO_GEOM.SDO_CENTROID(SDO_GEOM.SDO_ARC_DENSIFY(A.GEOMETRY, m.diminfo, 'arc_tolerance=0.05'),1).sdo_point.x), SDO_GEOM.SDO_CENTROID(SDO_GEOM.SDO_ARC_DENSIFY(A.GEOMETRY, m.diminfo, 'arc_tolerance=0.05'),1).sdo_point.y)) AS T FROM VAETA A, user_sdo_geom_metadata m WHERE m.table_name = 'AETA' AND m.column_name = 'GEOMETRY';
  </code></pre>
  
  <p> </p>
  
  <p>Here’s the results I get from my sql script:</p>
  
  <p><strong>IPID || M || P || CONCELHO || T</strong></p>
  
  <p>50040615 || 58243.8292298382 || -95032.5884906886 || Borba || MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(-7.46254163019826,38.8103393988376,NULL),NULL,NULL)</p>
  
  <p>50041533 || 46178.4608566094 || -125300.367916971 || Redondo || MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(-7.6034580874097,38.5384058748327,NULL),NULL,NULL)</p>
  
  <p>…   …   …   …   …</p>
  
  <p>My problem is to get Longitude and Latitude from the geometry object (column T), So that I would get a table like this:</p>
  
  <p><strong>IPID || M || P || CONCELHO || LONG || LAT</strong></p>
  
  <p>50040615 || 58243.8292298382 || -95032.5884906886 || Borba || -7.46254163019826 || 38.8103393988376</p>
  
  <p>50041533 || 46178.4608566094 || -125300.367916971 || Redondo || -7.6034580874097 || 38.5384058748327</p>
  
  <p>…   …   …   …       …</p>
  
  <p>Anyone knows how to do this <strong>in one go, in the same sql script</strong>? </p>
  
  <p>I’m a bit lost with the syntax specifics of it all...
  I've tried several times and I keep on getting errors that I can't really fix.</p>
  
  <p>Thanks in advance,</p>
  
  <p>Best regards,
  Pedro</p>
  
  <p>Note: I’m using <strong>a function (transform_long_lat_pt)</strong> that converts coordinates from SRID 3763 [ETRS89 / Portugal TM06] to 4326 [WGS 84]. I’ve tried the function and the results are ok.</p>
  
  <p><strong>The M and P Projected coordinates are in SRID 3763 [ETRS89 / Portugal TM06]</strong></p>
  
  <p><strong>The LONG and LAT Geographic coordinates should be in SRID 4326 [WGS 84]</strong></p>
  
  <pre><code>Funtion transform_long_lat_pt
  
  create or replace function transform_long_lat_pt(longitude in number, 
                                             latitude in number)
  return SDO_GEOMETRY deterministic is
  begin
       return sdo_cs.transform(sdo_geometry(2001, 3763, 
                  sdo_point_type(longitude, latitude, NULL),NULL, NULL),4326);
  end;
  </code></pre>