3 Replies Latest reply: Nov 8, 2012 9:20 AM by angelo_z RSS

    Get lat/long from polygon geometry

    user11219778
      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
          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
            Note that t.x is LONGITUDE not LATITUDE.
            Simon
            • 3. Re: Get lat/long from polygon geometry
              angelo_z
              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.