Get geometric cordinates from Oracle table

Question

Hi I need to get geometric coordinates from my Oracle table. Currently I use

 (select column_value 
    from (
           select rownum r, 
                  b.* 
            from table(
                       select h.geometry.sdo_ordinates 
                        from hz_locations h 
                       where location_id =
                             (select location_id 
                                from csf_ct_tasks 
                               where task_id = p_task_id)
                       ) b
          )    -- location_id = 973,  task_id = 36420   

   where r =1) as latitude,


  (select column_value 
     from (
           select rownum r, 
                  b.* 
             from table(
                        select h.geometry.sdo_ordinates 
                          from hz_locations h 
                         where location_id =  
                               (select location_id 
                                  from csf_ct_tasks 
                                 where task_id = p_task_id)
                         ) b
            ) 

   where r =2) as longitude

Here p_task_id is used to get a particular latitude, longitude of a task. But I need to get list of latitude, longitude of a single user by specifying user id in outer query. Is it possible to rewrite my query.

My Actual select statement is

SELECT all h.location_id,
       h.address1,
       h.address2,
       h.address3,
       h.address4,
       h.house_number,
       h.street_suffix,
       h.apartment_number,
       h.street,
       h.po_box_number,
       h.city,
       h.state,
       h.province,
       h.county,
       h.country,
       h.postal_code as customer_address
  from hz_locations h,
       csf_ct_tasks ct 
 where h.location_id = ct.location_id 
   and ct.owner_id = 10180

Thanks in Advance.

![Sample response of the latitude and longitude store in the table][2]

Actual table data

Where do you have the user_id stored? Could you please post the complete select? — Srini V, Sep 23 '13 at 08:45
The statements are completely contradicting!!. Please tell us where the user_id is stored — Srini V, Sep 23 '13 at 08:56
realspirituals: Here "ct.owner_id = 10180" this is my user id. Actually my need is to get latitude and longitude with my select query. Here my address and latitude and longitude values are stored in hz_locations table. — Hope, Sep 23 '13 at 08:58
As a side note. There is no need to use `all` keyword - it's a default behavior. — Nick Krasnov, Sep 23 '13 at 09:22

Srini V · Accepted Answer · 2013-09-23T13:06:24.603

1

Try this

WITH DATASET
    AS (SELECT
             CT.OWNER_ID OWNER_ID,
             ROWNUM R,
             H.GEOMETRY.SDO_ORDINATES VALUE
        FROM
             HZ_LOCATIONS H,
             CSF_CT_TASKS CT
        WHERE
             H.LOCATION_ID = CT.LOCATION_ID
             AND CT.OWNER_ID = 10180)
SELECT
      OWNER_ID,
      REGEXP_REPLACE ( VALUE,
                    '^([^,]*).*$',
                    '\1' )
          AS LAT,
      REGEXP_REPLACE ( VALUE,
                    '^[^,]*,|([^,]*).*$',
                    '\1' )
          AS LON
FROM
      DATASET
WHERE
      ROWNUM = 1;

edited Sep 23 '13 at 13:06

answered Sep 23 '13 at 09:04

Srini V

11,045
14
66
89

But when i update the query, i got an error like CT"."OWNER_ID": invalid identifier – Hope Sep 23 '13 at 09:13
Modified.. Please retry – Srini V Sep 23 '13 at 09:16
Here also we need to specify P_TASK_ID in the inner query. But i have only P_User_Id. – Hope Sep 23 '13 at 09:20
I dont understand this. CSF_CT_TASKS has both task_id and owner_id columns. So what is the problem in passing the inputs p_task_id and p_user_id? – Srini V Sep 23 '13 at 09:22
Here my actual problem is to get the list of task address for a particular user, this address is basically related to a task. for that i have only p_user_id. By the help of this p_user_id i need to read all address assigned for that user(all the task address). – Hope Sep 23 '13 at 09:25
@Hope Please see the updated query. If you are not able to achieve the intended results, please post CREATE TABLE, sample INSERT and sample output. – Srini V Sep 23 '13 at 09:43
I don't want to create a new table. I already have two table. One is user to store task details,user_id and another one is used to store task location details. Location details include location address and geo-cordinate with the type (SDO_GEOMETRY). Here my requirement is to read list of task assigned for a user, espcly address and cordinate from the second table. In my select statement i need to read all address and its cordinate from SDO_GEOMETRY field. My question include my select statement. How can i insert cordinate read query(mentioned in my question) into my select statement – Hope Sep 23 '13 at 09:53
@Hope I am not creating any new table. It is just a sub query factoring. Please try that and let me know the results. – Srini V Sep 23 '13 at 09:55
Is this what you wanted to achieve? If so please mark it as correct answer, which would be more polite. If not please post more details. – Srini V Sep 23 '13 at 10:09
But here AND CT.OWNER_ID = 10180 AND CT.TASK_ID = 36420 AND CT.LOCATION_ID = 973 are predefined ry8. how can i get all details by just specifying owner_id. If it is possible then please update your query. – Hope Sep 23 '13 at 10:19
On the Assumption: At least 2 rows stored in HZ_LOCATIONS for every owners. At least 1 row stored in CSF_CT_TASKS for every owner. I have modified the query – Srini V Sep 23 '13 at 10:23
Thats why we asked for table structure and dataset. How do you define the correct lat and lon for the user? – Srini V Sep 23 '13 at 10:34
i use 3 table. 1- user, 2-task(CSF_CT_TASKS), 3-cordinate. task table include list of user_id and location_id. In the cordinate table include list of cordinates . I need to read list of task id from CSF_CT_TASKS table. and for each task id i need to read list of cordinate vales. – Hope Sep 23 '13 at 10:36
@Hope This is leading to nowhere. Please post the create table statements for them and sample rows for atleast one useful result and a sample result to be seen – Srini V Sep 23 '13 at 10:39
i use 3 table. 1- user, 2-task(CSF_CT_TASKS), 3-cordinate. task table include list of user_id and location_id. In the cordinate table include list of cordinates . I need to read list of task id from CSF_CT_TASKS table. and for each task id i need to read list of cordinate vales by using corresponding location_id – Hope Sep 23 '13 at 10:40
CANT YOU POST THE CREATE TABLE AND INSERTS IN YOUR QUESTION? This is the worst way to ask for answers. – Srini V Sep 23 '13 at 10:42
Actually its an Oracle EBS back-end system. So its a big table. I cannot include it with this quest. I think you understand my issue. If you need anything other thas this – Hope Sep 23 '13 at 12:18
We dont need actual table. You can cook the example with a sample table and data and results. – Srini V Sep 23 '13 at 12:24
1

I made a small change in your code. 'WHERE ROWNUM = 1;'. Now it will return all the data that i wanted. I include that response with my question. Thanks for your response. Any way i need to read first value from each lat and log value. Thnk it should be perfect – Hope Sep 23 '13 at 12:41
For reading first row use ROWNUM else remove it. If issue is resolved mark it as correct answer, which would be more polite – Srini V Sep 23 '13 at 12:44
Should be. I also include the way data stored in the table as a screen shot. Is it possible to read that first an second data like -122 and 37.55 by update above query as my lat and long – Hope Sep 23 '13 at 12:50
I got an error like ORA-00932: inconsistent datatypes: expected NUMBER got MDSYS.SDO_ORDINATE_ARRAY – Hope Sep 23 '13 at 13:18
1

This is why we ask for table definitions. :( You need to convert the data types for consistent datatypes – Srini V Sep 23 '13 at 13:31
Is that stored as an array definition in database? – Srini V Sep 23 '13 at 13:44
@ realspirituals: yes – Hope Sep 23 '13 at 13:47
You have wasted atleast 5 hours. You should have mentioned spatial utilities and DWH environment in your question. Never post such blunt questions in future. Read here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_util.htm#autoId0 Extracting LAT and LON is purely based on the way it is stored. – Srini V Sep 23 '13 at 14:21

Get geometric cordinates from Oracle table

1 Answers1