GET_POINT 函式

通常在建立網路模型的時候常會用到的函數(其實在一般的向量圖資中也常會用到). 我想Oracle可能覺這個功能太簡單了, 所以並沒有放入一般的函式.
原程式碼出處: Pro Oracle Spatial, ISBN 1-59059-383-9
又從Albert Godfrind和Daniel Geringer的補充與修正. 程式碼如下

create or replace function get_point (
geom mdsys.sdo_geometry, point_number number default 1
) return mdsys.sdo_geometry DETERMINISTIC
is
d number; -- Number of dimensions in geometry
i number; -- Index into ordinates array
px number; -- X of extracted point
py number; -- Y of extracted point
pz number; -- Z of extracted point
begin
-- Return if input geometry is null
if geom is null then
return null;
end if;

-- Get the number of dimensions from the gtype
if length (geom.sdo_gtype) = 4 then
d := substr (geom.sdo_gtype, 1, 1);
else
raise_application_error (-20000, 'Unable to determine dimensionality from gtype');
end if;

-- Check that this is a simple line string geometry
if substr(geom.sdo_gtype, -1, 1) <> '2' then
raise_application_error (-20000, 'Not a simple line string');
end if;

-- Get index into ordinates array. If negative, count backwards from the end of the array
if point_number > 0 then
i := (point_number-1) * d + 1;
else
i := point_number*d + geom.sdo_ordinates.count() + 1;
end if;

-- Verify that the point exists
if i > geom.sdo_ordinates.last()
or i < geom.sdo_ordinates.first() then
raise_application_error (-20000, 'Invalid point number');
end if;

-- Extract the X and Y coordinates of the desired point
px := geom.sdo_ordinates(i);
py := geom.sdo_ordinates(i+1);
if d >= 3 then
pz := geom.sdo_ordinates(i+2);
else
pz := null;
end if;

-- Construct and return the point
return
mdsys.sdo_geometry (
d*1000+1,
geom.sdo_srid,
mdsys.sdo_point_type (px, py, pz),
null, null);
end;
/
------------------------------------------------------------------------------
create or replace function get_first_point (
geom mdsys.sdo_geometry
) return mdsys.sdo_geometry
is
begin
return get_point(geom, 1);
end;
/
------------------------------------------------------------------------------
create or replace function get_last_point (
geom mdsys.sdo_geometry
) return mdsys.sdo_geometry
is
begin
return get_point(geom, -1);
end;
/

Best Practices for Building Oracle 10gr2 Spatial Database

OK. it is reviewing my old mail from internal and find this is worthy reading.

• Spatial index creation -
• Specify WORK_TABLESPACE - During spatial index creation, the process creates intermediate tables that get dropped when the index is complete. The intermediate tables can take up to 2 times the size of the final index. If WORK_TABLESPACE is not specified, the intermediate tables are created in the same tablespace as the final index, causing fragmentation, and possible performance degradation. You can use SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE, and multiply the result by 2 to provide guidance on sizing the work tablespace. The work tablespace can be re-used to create other spatial indexes.
  
• Specify LAYER_GTYPE, especially when working with point only layers. If a point only layer stores it's points in the SDO_ORDINATE_ARRAY, you can still specify LAYER_GTYPE=POINT on spatial index creation. This can help query performance when performing spatial analysis.
  
• Specify SDO_NON_LEAF_TBL for very large spatial indexes (not necessary for smaller spatial indexes) - This generates two spatial index tables instead of one. The smaller spatial index table is the non leaf table, which is traversed most often during spatial analysis. It can be beneficial to pin the non leaf table into the buffer pool, since it is accessed most often. Below is an example.
  

• -- Create the index

  CREATE INDEX geod_counties_sidx

  ON geod_counties(geom)

  INDEXTYPE IS MDSYS.SPATIAL_INDEX

  PARAMETERS ('sdo_non_leaf_tbl=TRUE');

• -- Find the non leaf index table name

  SELECT sdo_nl_index_table

  FROM user_sdo_index_metadata

  WHERE sdo_index_name='GEOD_COUNTIES_SIDX';

  ----------

  MDNT_A930$

• -- Pin the table in memory

  ALTER TABLE MDNT_A930$ STORAGE(BUFFER_POOL KEEP);

• Transportable tablespace support for spatial indexes in Oracle 10g -
• If moving large amounts of spatial data and spatial indexes from one database instance to another, you may want to consider transportable tablespaces. Beginning with Oracle 10g, transportable tablespaces support transporting spatial indexes, as long as the source and target platforms are the same Endian format.
• If you plan to transport tablespaces, you may want to reconsider how data and indexes are mapped to tablespaces to help optimize your transport strategy.
• Geometry validation -
  
• It is good practice to ensure geometries are valid prior to index creation or running spatial analysis. This can be accomplished in bulk fashion with SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT, or on a geometry by geometry basis with SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT.
• Point data in the SDO_POINT attribute of the SDO_GEOMETRY object -
• When possible, store point data in the SDO_POINT attribute of the SDO_GEOMETRY object instead of the SDO_ORDINATES attribute.
  
• Partitioned local spatial indexes - If you are considering this, here are some best practices:
• For parallel creation of spatial indexes on partitioned tables, we now recommend a different strategy instead of using the PARALLEL keyword.
  

When creating a partitioned LOCAL spatial index, if any partitions index fails (for example, tablespace full or for some other reason), you must start at the beginning again (no way to continue from where you left off). Instead, here is a better approach.

First create the LOCAL spatial index with the UNUSABLE keyword. This runs very quickly and only creates metadata associated with the index:

• CREATE INDEX sp_idx ON my_table (location) INDEXTYPE IS mdsys.spatial_index PARAMETERS (‘tablesapce=tb_name work_tablespace=work_tb_name’) LOCAL UNSUABLE;

Hypothetically, lets say you have 100 partitions and 10 processors. Build 10 scripts, each with 10 ALTER INDEX REBUILD statements… but do not use the PARALLEL parameter. For example:

• ALTER INDEX sp_idx REBUILD PARITION ip1;
• ALTER INDEX sp_idx REBUILD PARITION ip2;
• etc...

By running all 10 scripts at the same time, each processor will be working on a single partitions index, but all the processors will still be busy working on their own set of ALTER INDEX statements.

If any of the ALTER INDEX commands failed, you do not have to rebuild any that have already successfully completed.

Network scripts for Taiwan Road Map

Script早已經在科長那兒測試過了. 怕以後找不到, 先post上來以免日後忘了.


-- Create the SDO_NET directed network. Also creates the SDO_NET_NODE$,
-- SDO_NET_LINK$, SDO_NET_PATH$, SDO_NET_PLINK$ tables, and updates
-- USER_SDO_NETWORK_METADATA. All geometry columns are named GEOMETRY.
-- Both the node and link tables contain a cost column named COST.
EXECUTE SDO_NET.CREATE_SDO_NETWORK('A012009', 1, TRUE, TRUE);
-- Populate the A012009_NODE$ A012009_LINK$ A012009_PATH$ tables.
create or replace procedure network_building as
count_num number :=0;
BEGIN
FOR A012009_rec IN (
SELECT MI_PRINX LINK_ID,
to_NUMBER(FNODE) FNODE,
get_point(geoloc)FGEOLOC,
to_NUMBER(TNODE) TNODE,
GET_POINT(GEOLOC,GET_NUM_POINTS(GEOLOC)) TGEOLOC ,
roadname,
roadtype,
GEOLOC
FROM A012009 A where (Fnode is not null)AND(Tnode is not null) AND (A.Geoloc.SDO_GTYPE = '2002') )
LOOP

select count(*) into count_num from A012009_node$ where node_id = A012009_rec.FNODE;
if count_num = 0 then
INSERT INTO A012009_node$(node_id,geometry,node_name,node_type,ACTIVE) VALUES(
A012009_rec.FNODE,
A012009_rec.FGEOLOC,
A012009_rec.roadname,
A012009_rec.roadtype,
'Y');
end if;

select count(*) into count_num from A012009_node$ where node_id = A012009_rec.TNODE;
if count_num = 0 then
INSERT INTO A012009_node$(node_id,geometry,node_name,node_type,ACTIVE) VALUES(
A012009_rec.TNODE,
A012009_rec.TGEOLOC,
A012009_rec.roadname,
A012009_rec.roadtype,
'Y');
end if;

-- select count(*) into count_num from A012009_LINK$ where LINK_id = A012009_rec.LINK_ID;
-- if count_num = 0 then
INSERT INTO A012009_link$(link_id,Link_name,start_node_id,end_node_id,LINK_TYPE,ACTIVE,GEOMETRY,BIDIRECTED) VALUES(
A012009_rec.LINK_ID ,
A012009_rec.ROADNAME,
A012009_rec.FNODE,
A012009_rec.TNODE,
A012009_rec.roadtype,
'Y',
A012009_rec.GEOLOC,
'N');
-- end if;

-- commit;
END LOOP;
commit;
end;
/
-- Do not populate the SDO_NET1_PATH$ and SDO_NET1_PLINK$ tables now.
-- Do this only when you need to create any paths.
---------------------------------------------------------------------------
-- REMAINING STEPS NEEDED TO USE SPATIAL INDEXES --
---------------------------------------------------------------------------
-- Update the USER_SDO_GEOM_METADATA view. This is required before the
-- spatial index can be created. Do this only once for each layer
-- (that is, table-column combination).
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
select 'A012009_NODE$','GEOMETRY',diminfo,SRID from user_sdo_geom_metadata where table_name = 'A012009';
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
select 'A012009_LINK$','GEOMETRY',diminfo,SRID from user_sdo_geom_metadata where table_name = 'A012009';
INSERT INTO user_sdo_geom_metadata
(TABLE_NAME,
COLUMN_NAME,
DIMINFO,
SRID)
select 'A012009_PATH$','GEOMETRY',diminfo,SRID from user_sdo_geom_metadata where table_name = 'A012009';
-- Create the spatial indexes
CREATE INDEX A012009_nodes_idx ON A012009_node$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX A012009_links_idx ON A012009_link$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX A012009_path_idx ON A012009_path$(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Published Article in MOI GIS Communictions No. 61 (In Chinese)

As the subject. The published article URL: http://ngis.moi.gov.tw/TheFiles/journal/61/page/80.htm

開始了

原因其實很簡單.
在完成了災防會的圖資倉儲之後, 覺得倉儲裏面有很多資料可以使用. 為什麼不善加利用呢? 要用什麼呢? 建立路網, 並測試 Spatial Network Model, Why not?
在拜訪過科長, 談了一下我想法. 感謝科長同意借我資料測試.
測試的資料是運研所的資料. 資料量比我想像中要大了一點.
運研所的資料有點問題. 雖然有問題的資料只佔了0.06%(都是多點了一點), 所以也寫了一支小程式修正了這些資料.
程式:

create or replace PROCEDURE FIXING_GEOM AS
b_rowid val_results.sdo_rowid%TYPE;
t_geo_loc A012009.geoloc%type;
cursor c1 is
select SDO_ROWID from VAL_RESULTS;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO b_rowid;
EXIT WHEN c1%NOTFOUND;
UPDATE A012009
SET GEOLOC = SDO_UTIL.RECTIFY_GEOMETRY(GEOLOC, 0.000005)
WHERE ROWID = b_rowid;
COMMIT;
commit;
END LOOP;
CLOSE c1;
END FIXING_GEOM;