This discussion is archived
3 Replies Latest reply: Nov 8, 2012 7:20 AM by angelo_z RSS

Get lat/long from polygon geometry

user11219778 Newbie
Currently Being Moderated
Hi,

I have an application where I need to get point(lat,long) values from polygon geometry. It has to be
in a seperate column. I have tried following but it is not working in my application. How I should
get lat, long column in a seperate column i.e. select a.LATITUDE, a.LONGITUDE, a.geometry from table1 a.
Please help!

select s.ID, t.x as LATITUDE, t.y as LONGITUDE
FROM table1 s, Table(sdo_util.GETVERTICES(s.geometry)) t

Thanks in advance
  • 1. Re: Get lat/long from polygon geometry
    _jum Journeyer
    Currently Being Moderated
    What does mean "but it is not working in my application", do you get an error?
    Your code is working fine:
    WITH table1 AS
     (SELECT 1 id, SDO_GEOMETRY(2003, NULL, NULL, 
                   SDO_ELEM_INFO_ARRAY(1, 1003, 1),
                   SDO_ORDINATE_ARRAY(10,50, 11,52, 11,53)) geometry FROM dual) 
    SELECT s.ID, t.x as LATITUDE, t.y as LONGITUDE
      FROM table1 s, Table(sdo_util.GETVERTICES(s.geometry)) t;
    
            ID   LATITUDE  LONGITUDE
    ---------- ---------- ----------
             1         10         50
             1         11         52
             1         11         53
    
    3 rows selected.
    Because I don't have your table, I built it "on the fly" (WITH table1 AS ...)
  • 2. Re: Get lat/long from polygon geometry
    Simon Greener Journeyer
    Currently Being Moderated
    Note that t.x is LONGITUDE not LATITUDE.
    Simon
  • 3. Re: Get lat/long from polygon geometry
    angelo_z Newbie
    Currently Being Moderated
    Ciao!

    Maybe I can help you, try this:

    select
    B.ID,
    B.X,
    B.Y,
    B.Z,
    B.geopoint.sdo_point.x as Longitude,
    B.geopoint.sdo_point.y as Latitude,
    B.geopoint.sdo_point.z as Higth
    from (
    select
    ID,
    X,
    Y,
    Z,
    sdo_cs.transform
    (
    sdo_geometry(2001,32632,
    sdo_point_type(x, y, z),NULL,NULL),
    4326
    ) as
    geopoint
    from
    (
    SELECT
    ID,
    t.x,
    t.y,
    t.z
    FROM table1 A,
    TABLE(SDO_UTIL.GETVERTICES(A.GEOMETRY)) t
    WHERE
    A.ID = 1
    )
    )B


    Saluti
    Angelo Z.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points