*2. get the points from the geometry column (sdo_geometry)*
CREATE TABLE load_table AS SELECT ID, GEOM FROM base_table where SDO_INSIDE(....) = 'TRUE';
(See last comment about data.)
SELECT ID, v.x, v.y FROM load_table l, TABLE(sdo_util.getVertices(l.geom)) t;
Now show us what it is you want to do!!
WITH myGeom As ( SELECT 1 as id, MDSYS.SDO_GEOMETRY(2006, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1), MDSYS.SDO_ORDINATE_ARRAY(355600.52, 5407396.19, 361365.32, 5408106.36, 356488.27, 5409242.37, 357437.46, 5406457.67)) as geom FROM DUAL ) SELECT g.id || ',' || t.x || ',' || t.y as csv FROM myGeom g, TABLE (sdo_util.getVertices( GEOM.Rotate(p_geometry => g.geom, p_tolerance => 0.005, p_rotatePt => MDSYS.SDO_Point_Type(357437.46,5406457.67,NULL), p_rotation => 45 ))) t; -- Results -- CSV ---------------------- 1,355474.91,5405822.39 1,359049.08,5410400.89 1,354797.2,5407755.57 1,357437.46,5406457.67
Yes all the steps can be done within the database side (Oracle). However I could do the same (extracting the coordinates from the sdo_geometry column, transforming the coordinates and encoding the coordinates) in .net code (web server side) by making use of the new verison of the odp.net. So I have to make a decision now to choose which approach to go, relying on Oracle server to do all the data processing or .net code. I am pretty sure Oracle can process a single request fairly quick, but if tens or even more concurrent requests for the process, I have concerns over the performance or reliability of the database server, especially the server is (a kind of ) beyond of our control (in control of the midware, IS and network team). Somebody said database is just a database and for data storing and retrieving, but may not for processing the data.The decision as to how to deploy functionality is not an either/or binary process that is easily resolved by saying a "database is just a database and for data storing and retrieving, by [but?] may not [be used?] for processing the data".
For example, they MIGHT be able to improve the performance of SDO_CS.TRANFORM as other database solutions etc run far faster indicating some sort of algorithmic/implementation issue.James mentioned that he is on 10g and may upgrade to 11g in a year.
Fast Coordinate System Transformations
• SDO_CS.Transform is upto 10 times faster in 11gR2
– This speedup is seen if 1000s of transformations are done in a session
– This is typical for mapping applications where themes are transformed at run time to match the base map SRID
• In the prior releases, the transformation context was created for each transform() call
– now we use that context between transform() calls with the same source and traget SRIDs