SpatialDB Advisor

Simon Greener, Independent GeoSpatial Solutions Architect
(ABN 90 970 658 939)

Sir,

I want to load the shape file in oracle . please suggets me . how can I do it . I have used the tool to shp2sdo for converting and omportind data in oarcle but when i Ran it it return “Error in converting control file ”

Thanking you.

— Anurag SIngh    Jul 3, 12:51 am    #

Anurag,

There are a few different methods depending on what client technology you have.

1. Oracle’s sqlldr pre-processors shp2sdo but you have seen and tried that. Post the control file error to the Oracle Spatial forum for a fix to your problem.

2. If you want a pure Oracle based approach download the free MapBuilder Java application from Oracle Technology Network

MapBuilder is for creating and populating the metadata tables that Oracle’s MapViewer uses for styling and rendering geometry data.

If you have some $$ to spend:

3. Purchase a copy of Manifold GIS Enterprise Edition and use it to load your data. It is excellent value and can help you load data from other formats other than shapefiles. It will even load GeoRaster data from imagery.

4. Try the excellent Spatial Console from Geometry

5. FME from Safe Software is the Swiss Army knife of spatial data manipulation but it is very expensive.

I haven’t tried many of the open source products of late but one that can help is the ogr2ogr command line tool which is a part of Frank Warmerdam’s FWTools free download.

Hope this helps get you started….

regards
Simon

— Simon    Jul 3, 09:55 am    #

Simon, the SDO_MIGRATE.TO_CURRENT procedure does what you are talking about here with regards to rotation etc. Hope all is well.
-Justin

— Justin    Jul 7, 08:42 am    #

Justin,

You are right to mention sdo_migrate.to_current as a solution to the problem. However, it will not fix all the problems that wrongly oriented polygon data in shapefiles present to the Oracle Spatialist.

Here is my test:

drop table test;
create table test( geom sdo_geometry );
insert into test values(sdo_geometry(2003,null,null,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(1,1, 1,2, 2,2, 2, 1, 1,1 )));
insert into user_sdo_geom_metadata (table_name, column_name, srid, diminfo)
values(‘TEST’,’GEOM’,NULL,sdo_dim_array(sdo_dim_element(‘X’,0,100,0.05),sdo_dim_element(‘Y’,0,100,0.05)));
select sdo_geom.validate_geometry(a.geom,0.05) from test a ;
execute sdo_migrate.to_current(‘TEST’,’GEOM’,10);
select sdo_geom.validate_geometry(a.geom,0.05) from test a ;
delete from user_sdo_geom_metadata where table_name = ‘TEST’ and column_name = ‘GEOM’;
Drop table test;

The first sdo_geom.validate_geometry treturned 13369 (wrong rotation); the second TRUE.

However, if the shapefile contained a shape with a multipolygon object with two shells with the wrong rotation sdo_migrate.to_current does not fix the problem. Only a self-union using sdo_geom. As can be shown by this extra bit of testing:

drop table test;
create table test( geom sdo_geometry );
insert into test values(sdo_geometry(2003,null,null,sdo_elem_info_array(1,1003,1, 11, 1003, 1),sdo_ordinate_array(1,1, 1,2, 2,2, 2, 1, 1,1, 1.2,1.2, 1.2,1.4, 1.4,1.4, 1.4,1.2, 1.2,1.2 )));
insert into user_sdo_geom_metadata (table_name, column_name, srid, diminfo)
values(‘TEST’,’GEOM’,NULL,sdo_dim_array(sdo_dim_element(‘X’,0,100,0.005),sdo_dim_element(‘Y’,0,100,0.005)));
select sdo_geom.validate_geometry(a.geom,0.005) from test a ;
execute sdo_migrate.to_current(‘TEST’,’GEOM’,10);
select sdo_geom.validate_geometry(a.geom,0.005) from test a ;
delete from user_sdo_geom_metadata where table_name = ‘TEST’ and column_name = ‘GEOM’;
select * from test;
select sdo_geom.sdo_union(a.geom,a.geom,0.005) from test a;
select * from test;
Drop table test;

Yes, to_current fixed the rotation problem, but it did not fix the fact that the two shells of the one polygon were expressed as "external" (ie 1003) yet actually only one is external: the other is its internal "hole" and should have been coded as 2003 and not 1003.

I saw this in the reverse case recently which is why I blogged on it. In the case in hand the outer parts of a multi-part polygon had clockwise rotation and so, when converted, were coded as 2003. Yet, patently, they were outer shells and should have been coded 1003. Only a self-union via sdo_geom.sdo_union can fix this.

If I get time I will show all cases via some diagrams.

regards
S

— Simon    Jul 7, 08:23 pm    #

i want to load the shape file to oralce spatial ,but i donot know how to do and which tool i can use

— xiaochun wu    Nov 28, 05:56 pm    #

Xiaochun wu,

See my comment above in response to a similar question from Anurag.

regards
Simon

— Simon    Nov 28, 06:20 pm    #