Spatial :: Splitting Line At Multiple Points

Apr 22, 2013

I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).

However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.

View 13 Replies


ADVERTISEMENT

Spatial :: Decimal Points - Geometry - Sql Developer Geographer?

Aug 13, 2013

I have loaded shape files with SQL developer Georaper and limited number of decimals to 8. There is option to limit number of decimals in georapter.But there are some X, Y with many decimals. This may create problem in spatial functions in which geometries are compared and filtered

how to control the decimal points of X,Y to be loaded in geometry column.  -0.4695623500000000172249770002963487058878                    51.47160360000000167701728059910237789154-0.4702653600000000211345252409955719485879                    51.47174944999999723904693382792174816132-0.4713475400000000092859409051015973091125                    51.47330664999999783049133839085698127747-0.4717886499999999760923685698799090459943  
                 
[code]...

View 2 Replies View Related

Spatial :: Sdo_nn To Fetch Points From Point Cloud And Georaster

Mar 9, 2013

I have a requirement to fetch the nearest neighbor from a given point coordinates inside a point cloud/georaster data. how can i retrieve a collection of all neighboring points from a given point.

I guess i will have to use sdo_nn operator. how to use the operator to achieve the result.

View 21 Replies View Related

Spatial :: Convert Multi-line To Simple Line

Feb 13, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:

SDO_GTYPE     2006
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )

Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).

A better way to encode this geometry would be:

SDO_GTYPE     2002 <---
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )

Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)

View 4 Replies View Related

SQL & PL/SQL :: Splitting One Row To Multiple?

Oct 9, 2012

I have a table Product as;
desc product
Name Null Type
--------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER
INGREDIENT VARCHAR2(20)

The data in Ingredient is separated by ','.
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A,B,C
2 A,D
3 E,F

I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;

PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A
1 B
1 B
2 A
2 D
3 E
3 F

write this sql ?

View 1 Replies View Related

SQL & PL/SQL :: Splitting Given String Into Multiple Sub-strings?

Feb 18, 2013

I need to split the given string into muliple sub strings based on one special character

Ex : Speace is Special character

with data as (
select 'ab cd ef gh ' from dual )
select * from data

Required Output :
ab
cd
ef
gh

View 3 Replies View Related

SQL & PL/SQL :: Multiple Row View From Splitting Table?

May 8, 2010

Table Name : Trans

chitta_enn number(10,0)
varavu_patti varchar2(100)
pattru_patti varchar2(100)
Thogai number(10,2)

where in the data's are as follows
chitta_enn varavu_patti pattru_patti Thogai
101 panam null 101.00
101 null sambalam 51.00
101 null kamishan 50.00

I need to create the view as follows

View Name : Pattiyal

vivaram varchar2(2000)
varavu number(10,2)
pattru number(10,2)

The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null

Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn

Is it possible to create a view as above

View 1 Replies View Related

SQL & PL/SQL :: Splitting Data In 1 Column Into Multiple Rows?

Oct 20, 2010

I have a table which has a column that stored concatenated data.

Sample test case is as below:

SQL> create table tst (
2 col1 varchar2(20));

SQL> insert into tst values ('one,two,three');

1 row created.

SQL> commit;

Commit complete.

Is there any way i could write a sql to split the text of this column into rows? Sample output im expecting is as below

col1
-------
one
two
three

View 13 Replies View Related

SQL & PL/SQL :: Splitting One Row In Multiple Rows Based On QTY And Join

Sep 14, 2012

I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.

Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.

-- ITL Table

Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)

[code]....

-- Issued Removed table

create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select

[code]....

-- The way I need to join the Issued_Removed Table

select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)

[code]....

View 1 Replies View Related

Client Tools :: Splitting A Column Into Multiple Columns

Jul 31, 2012

I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).

Quote:FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE

RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE

Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION

RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION

View 3 Replies View Related

Spatial :: Convert Shape File To Oracle Spatial

Jul 31, 2012

I have a road network which is shape file format and i want to export it to oracle spatial format using any free tool, I am using arcgis 9.3.1 and Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bi,

View 2 Replies View Related

Spatial :: Error In Spatial Index

Jun 16, 2013

I created a spatial index on a table, but i get the error message:

ORA-13033: the data in sdo_elem_info_array of sdo_geometry is unavailable

How can i write a SQL command to delete those unavailable records?

View 2 Replies View Related

SQL & PL/SQL :: Carriage Return Line Feed Multiple Times In Same CLOB

Jan 31, 2012

I'm looking for a way to make CRLFs show in a CLOB.I'm feeding the insert statement with a concatinated string like this:

insert into table(Data) values (MyConc)

(MyConc is a string put togheter by another application)

Because of this I can't use the "|| chr(13) || chr(10) ||" because I only have that one concatinated string.Is there a character I can set in my string that automatically translates to a CRLF?

My CLOB-data should look something like this:

1;blue;Woodstock;;
34;giant;squid;attack;
5;blue;squid;;

And in this case the "MyConc" would look like this "1;blue;Woodstock;;[X]34;giant;squid;attack;[X]5;blue;squid;;[X]

where [X]=the character I need for CRLF =)

View 11 Replies View Related

Reports & Discoverer :: Multiple Record Printing On Same Line Number In Oracle 10g?

Jun 28, 2013

I want to print two record of emp table in same line number in tabular format report.

For example if i have emp table and I want to print empno,ename,sal columm only,In tabular format report, Two record should print per line. so If emp table have 14 record report should be printed on 7 rows.

so the output in print preview should be like this

EMPNO ENAME SAL EMPNO ENAME SAL
------ ---------- ----------------------------------------------
7369 SMITH 800 7499 ALLEN 1600
7521 WARD 1250 7566 JONES 2975
7654 MARTIN 1250 7698 BLAKE 2850
.
.
.
.

View 1 Replies View Related

Server Utilities :: SQL Loader - How To Dynamically Create Multiple Records Out Of One CSV Input Line

Jul 28, 2010

I need to load data from a CSV file where one of the CSV values determines how many records should be inserted.

Example of the input data:

KEYWORD;2;REC1_COL1_X,REC1_COL2_X;REC2_COL1_X;REC2_COL2_X
KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y,REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y
KEYWORD;4;REC1_COL1_Z;REC1_COL2_Z,REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z,REC4_COL1_Z;REC4_COL2_Z
If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.

As a result I hope to achieve this:

SELECT Column1, Column2 FROM testTable

REC1_COL1_X,REC1_COL2_X
REC2_COL1_X;REC2_COL2_X
REC1_COL1_Y;REC1_COL2_Y
REC2_COL1_Y;REC2_COL2_Y
REC3_COL1_Y;REC3_COL2_Y
REC1_COL1_Z;REC1_COL2_Z
REC2_COL1_Z;REC2_COL2_Z
REC3_COL1_Z;REC3_COL2_Z
REC4_COL1_Z;REC4_COL2_Z

I learned how to import data using Oracle SQL loader for cases where one input line more or less matches a (new) row in a DB table.

View 9 Replies View Related

Spatial :: ORACLE 11g Spatial And GRS 80

Apr 4, 2013

I am working on a project that will require both projected coordinates and geographic coordinates for different product files coming in. I have no issues defining, storing, extracting or matching the geographic coordinates. My columns for those coordinates with SRID 8307 are defined in the user_sdo_geom_metadata table as follows:

INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('INVENTORYGEOMETRY','INVGEO',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05)),8307);

the index: create index "MYDB"."INVGEO_IDX" on "MYDB"."INVENTORYGEOMETRY"("INVGEO") indextype is MDSYS.SPATIAL_INDEX;

how I am going to have a column that has both Geographic and Projected coordinates. Am I allowed to define more SDO_DIM_ELEMENT(s) fro the SDO_DIM_ARRAY with different min/max values. As an example, would I be able to still use an SRID of 8307 for both WGS 84 and GRS80 data (ellipsoid), something like the following:

INSERT INTO
USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
('INVENTORYGEOMETRY','INVGEO',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05),
(MDSYS.SDO_DIM_ELEMENT('FIXEDGRID_.5Resolution', 0, 26916, .5)),8307);

Is that possible or do i have to create a new column in the database? Also, I don't know the relationship between the SDO_ELLIPSOIDS table and the SDO_GEOMETRY table.

how I'm going to make this work for both the geographic and projected coordinates or if I'm going to be able to make it work.

View 4 Replies View Related

PL/SQL :: Two Decimal Points

Aug 10, 2012

What would be the syntax if I want to use use '999G999G990D00' to show only two decimal points for outstanding_receipt_value in the following

((declared_quantity - delivered_qty) * po_unit_price) outstanding_receipt_value

View 3 Replies View Related

SQL & PL/SQL :: Query To Get Total Points From Various Factors

Oct 20, 2011

I am trying to calculate the total points of each customer

create table Customers(Customer_no number,revenue varchar2(30),Regularity varchar 2(30)
);
insert into Customers values(1,'25','2');
insert into Customers values(2,'50','3');

Table customers:

Customer_no, revenue, regularity
1,25,2
2,50,3

create table Points(ponts ID number,Factor varchar2(30),Weight number);

insert into Points values(1,revenue,2);
insert into Points values(1,Regularity,4);

Table points
Points ID,Factor,Weight
1,revenue,2
1,regularity,4

So for customer_no: 1

revenue is 25 * 2(weight for revenue ) =50
regularity is 2*4(weight for regularity) =8
Total =58

So for customer_no: 2

revenue is 50 * 2(weight for revenue ) =100
regularity is 3*4(weight for regularity) =12
Total =104

View 8 Replies View Related

PL/SQL :: Generate All Dates Between To Date Points

Sep 8, 2012

Is there a way I can generate a list of date using a simple SELECT statement to generate all dates between to date points? For example between SYSDATE AND SYSDATE-7?

One way I know is have a table and run PL/SQL script to put all dates in it and query that but was wondering if it can be achieve via SQL SELECT query at all without creating a table (querying DUAL).

View 5 Replies View Related

Server Administration :: Why 3 Mount Points For Datafiles

Feb 3, 2013

i encoutered lately on such statement:

Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....

why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?

View 4 Replies View Related

Application Express :: How To Manage Positions Of Different Display Points Of Theme

Apr 6, 2013

I'm developing, or trying, on Apex 4.2...The issue is about themes and how to manage the positions of diferents Display Points of a theme.

Everybody know that any theme have a map where its position all differents parts of this map (Body1, Body2, Body3, position01, position02 and so). So when you choice any theme, it come with all this Display points defined. You can see this map rendering any region page and in the item Display Point you can see this map clicking on the light.

There is anyway to can manage this map and change position or behavior of those points?. For example , I'm interested that under the tabs, to have at the same height, 3 diferentes position, one at left, one at center and other at right.

View 2 Replies View Related

Oracle Block Splitting

Aug 24, 2012

How block splitting will happen in oracle block.

Suppose, I am having Oracle BLOCK_SIZE of 16k and my Linux OS level BLOCK SIZE is 4k. then How 16k oracle block will store in OS level? and
What will be the internal block splitting process?

View 10 Replies View Related

Splitting A String Based On Delimiter?

Feb 19, 2008

I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:

HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com

I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.

View 1 Replies View Related

SQL & PL/SQL :: Splitting Large Table Output

Sep 20, 2012

I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:

1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?

2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.

I'm trying to get the first block of the IF working first, so the rest is just placeholders.

DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....

View 12 Replies View Related

Forms :: Splitting Oracle Database Into Two

Mar 26, 2010

Aim: Architecture change in existing application
Domain: Health Care
Background: There are 2 application ( Front end: one in oracle forms - deals with accounts module and another in some legacy application - deals with patient, clinical and diagnose module) using and sharing the same Oracle 9i database.

Patient related modules are moved into another database ( java as a front end, oracle 10g as backend ) which is normalized - eliminating duplicate tables and column, also its tables and columns names are not matching with existing (patient)system.

Now the requirement is making the existing application related only to Accounts module ( having complicated business logic written in packages ) to work as it is without changing the code, design drastically.

Questions:

1. Now how best this task can be completed without affecting existing Accounts system drastically ( with minimal changes )?
2. what are the possible best approach to achieve this ?
3. what are the best way for communicating the 2 DB in this scenario ( may be creating synonym, views etc ) ?
4. What are challenges that needs to be addressed ?

View 1 Replies View Related

SQL & PL/SQL :: Splitting Data And Store Into Columns?

Feb 28, 2013

I have string like 'PRASAD,ALLEN,STEWART,SMITH'.

LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH

I want to store the data into columns using SELECT statement only

View 7 Replies View Related

Error Splitting Partition On IOT Table

May 14, 2013

11.2.0.3...just trying to learn the syntax. I have not worked with IOTs and I am exploring a feature I have not really used to try to learn something new. I know about intervals.This exact split syntax below, works on a heap table without errors. When I run the following split against a regular heap table it works.

CREATE TABLE MYTABLE (
INSERT_DATE date,
myfield1 varchar2(50),
myfield2 varchar2(50),
myfield3 varchar2(50),
[code]....

alter table MYTABLE split partition "FUTURE" at ( to_date('09_MAY_2013_13','DD_MON_YYYY_HH24' ) ) into ( partition "B4_09_MAY_2013_13", partition "FUTURE" ) update global indexes
               *
ERROR at line 1: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER

View 4 Replies View Related

Server Administration :: Splitting Table Partition Without Making Primary Key Index Unusable?

Apr 8, 2013

splitting a table partition without making its primary key index ar any other indexes unusable.

I think it is possible to do so 10g onwards.

DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP

View 2 Replies View Related

Spatial :: Geometry  (0,0) Coordinates

Jun 3, 2013

When performing job search in i Recruitment found that all jobs are not retried when performing geocode search using Distance from Location functionality.

Narrowed down and found few addresses have Geometry coordinates (0,0) which are posing this problem.
Example :

Geometry :
MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)

Address line 1 :1560 Regent Ave
Town/City :Winnipeg
Region : MB
Postal code:R2C 3B4
COuntry : CA

This customer has 200 such Canada Addresses with (0,0) geometry details.

how to get the Geometry colun populated with proper coordinates so that the searches can be successfull.

View 2 Replies View Related

Spatial :: Strange Lengths From SDO?

Oct 7, 2013

The SDO_LENGTH command appears to be giving us the length of only the first line segment in a mult-segmented polylines. ... For example..  for this

polyline.... MDSYS.SDO_GEOMETRY(3002,82212,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,7,2,2),MDSYS.SDO_ORDINATE_ARRAY(489308.53608219,5465823.43147879,0,489254.621813806,5465739.29869485,0,489223.453489433,5465690.81111309,0,489217.608149169,5465676.83952032,0,489213.696031073,5465662.20843221,0,489213.337168734,5465657.12388026,0,489213.259485918,5465652.02727197,0)) RunningUPDATE TEMP_1 SET LEN = SDO_GEOM.SDO_LENGTH (GEOMETRY,0.005,'unit=meter'); 

We get 99.925 meters,and not the 198.075 that we were expecting. 

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved