This discussion is archived
2 Replies Latest reply: Jun 6, 2013 5:40 AM by 1013173 RSS

Oracle Spatial , Nead Help for Simple Query

1013173 Newbie
1013173 Jun 6, 2013 1:51 AM
Currently Being Moderated
Hello,

I'm a beginner in Oracle Spatial and I have to make a simple query but i have no clue how to do that.

My problem is :
I have a table which contains Highways informations.
A Highway has :
- an id (VARCHAR2(10)) which is the primary key
- a geometry (SDO_GEOMETRY) which contains the geographical informations

The geometry always is
MDSYS.SDO_GEOMETRY(
3002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),
MDSYS.SDO_ORDINATE_ARRAY( ...))

The SDO_ORDINATE_ARRAY contains a list of "triplets" : the X, Y coordinates in Lambert 93 and the corresponding km (in meters) of the highway.
The km is always growing. The kms are a subdivisions of the highways.

I want a simple query that give me the X, Y knowing the id and the km, assuming that if there is not a point which match the proper km, the query must reply a linear interpolation taking the closest point in the geometry.

Thanks in advance

PS : excuse me for my bad english.
  • 285 Views
  • 1. Re: Oracle Spatial , Nead Help for Simple Query
    John O'Toole Journeyer
    John O'Toole Jun 6, 2013 2:27 AM (in response to 1013173)
    Currently Being Moderated
    Hi,

    The sdo_gtype of your sample geometry is 3002 which suggests you are storing x, y, z values. i.e. heights in the z.
    Based on your explanation of the data, it sounds like you should be using Linear Referencing - read about it here (
    http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_lrs_concepts.htm#BABIIFFH).
    Note that use of Linear Referencing requires an Oracle Spatial license - Locator is not enough.

    SDO_LRS has a variety of functions/operators which will help you achieve your goal.
    E.g. SDO_LRS.LOCATE_PT
    http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_lrs_ref.htm#i85478

    John
  • 2. Re: Oracle Spatial , Nead Help for Simple Query
    1013173 Newbie
    1013173 Jun 6, 2013 5:40 AM (in response to John O'Toole)
    Currently Being Moderated
    Hi,

    Thanks for the tip.
    I used the LOCATE_PT function with the SDO_UTIL.GETVERTICES function and it works.

    My query is (for hisghway id = '1000' and km = 5000):

    select li.*, t.*
    from HIGHWAYS li , table( sdo_util.getvertices( SDO_LRS.LOCATE_PT( li.geom, 5000 ) )) t
    where li.id = '1000'
    ;

Actions

Legend

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