4 Replies Latest reply on Jun 25, 2012 1:31 AM by Simon Greener

# REALLY New to spatial...

So I am trying to get a handle on this.

I point in space that I know the long/lat, compass direction and speed from a single observation call this P1.
I have a rectangle that I have derived from two pairs of lon/lat points so it is UpperLeft(long,lat) LowerRight(long,lat).

Assume the rectangle is 10km by 1 km

- I want to determine if P1's projected direction of travel will cross the rectangle.
- I want to determine the distance that P1 is from the center of the rectangle so I can determine the ETA of P1.

If I have a table that has a column of sdo_geometry column that will describe the rectangle and I believe it should be an SDO_GTYPE of 2003.

Should I simply store the upper right and lower left pairs as long/lat? If so what sort of spatial query function(s) would I use to figure this out.

• ###### 1. Re: REALLY New to spatial...
FlyingGuy,

What is helpful is some real data that we can construct a solution from.

I assume that the data is GPS data since you talk about lat/long/heading/speed.

Is the rectangle formed from data separate to the observed point?

1. Assume we have a 10km x 1km rectangle coded as an optimized rectangle:
``````SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225,-43.3288392547696,144.656799114
473,-43.3172501109063))``````
2. Assume we have a point whose heading is 56 degrees and speed is 80 km per hour.
``(2001,8307,SDO_POINT_TYPE(144.591427805722,-43.341131942977,NULL),NULL,NULL)``
Firstly:
A. Will P1's projected direction of travel cross the rectangle?

Let's try and create a line that reflects the heading. We have a start point, what we need is an end point. Now we can determine distance from our point to the rectangle via:
``````SDO_GEOM.SDO_DISTANCE(
geom1 IN SDO_GEOMETRY,
geom2 IN SDO_GEOMETRY,
tol IN NUMBER
[, unit IN VARCHAR2]
) RETURN NUMBER;``````
as follows:
``````With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225, -43.3288392547696, 144.656799114473, -43.3172501109063)) as rect,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(144.591427805722,-43.341131942977,NULL),NULL,NULL) as point
from dual
)
select SDO_GEOM.SDO_DISTANCE(point,rect,0.005,'unit=M') as dist
from geoms;
-- Result
DIST
----
1365.043``````
But this is the minimum distance. What is the maximum? Process each point via use of SDO_UTIL.GETVERTICES ..
``````With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225, -43.3288392547696, 144.656799114473, -43.3172501109063)) as rect,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(144.591427805722,-43.341131942977,NULL),NULL,NULL) as point
from dual
)
select min(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.rect.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M')) as dist
from geoms g,
table(SDO_UTIL.GETVERTICES(g.rect)) v;
-- Result
--
DIST
----
4914.206``````
OK, so if we don't hit the rectangle at our heading and using this maximum distance we ain't going to! We can generate a line via:
``````SDO_UTIL.POINT_AT_BEARING(
start_point IN SDO_GEOMETRY,
bearing IN NUMBER,
distance IN NUMBER
) RETURN SDO_GEOMETRY;``````
as follows:
``````With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225, -43.3288392547696, 144.656799114473, -43.3172501109063)) as rect,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(144.591427805722,-43.341131942977,NULL),NULL,NULL) as point
from dual
)
select SDO_UTIL.POINT_AT_BEARING(g.point,
56,
min(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.rect.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))) as point2
from geoms g,
table(sdo_util.getVertices(g.rect)) v;
-- Results
--
GEOM
------
SDO_GEOMETRY(2001, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(144.559839781571, -43.3033873736708))``````
Now we can test to see if a line from our first point, at our heading and distance to a second point, crosses the rectangle.

NOTE: the point_at_distance function as returned the point within the sdo_ordinate_array and not in the sdo_point_type structure.
``````With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225, -43.3288392547696, 144.656799114473, -43.3172501109063)) as rect,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(144.591427805722,-43.341131942977,NULL),NULL,NULL) as point
from dual
)
select sdo_geom.relate(f.rect,
'DETERMINE',
0.005) as anyinteraction
from (select g.rect,
sdo_geom.sdo_union(g.point,
SDO_UTIL.POINT_AT_BEARING(g.point,
56,
min(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.rect.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))),
from geoms g,
table(sdo_util.getVertices(g.rect)) v
) f;
-- Results
--
ANYINTERACTION
--------------
OVERLAPBDYDISJOINT ``````

I want to determine the distance that P1 is from the center of the rectangle so I can determine the ETA of P1.
``````With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225, -43.3288392547696, 144.656799114473, -43.3172501109063)) as rect,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(144.591427805722,-43.341131942977,NULL),NULL,NULL) as point
from dual
)
select sdo_geom.sdo_distance(g.point,sdo_geom.sdo_centroid(g.rect,0.005),0.005,'units=M') as dist_to_centre
from geoms g;
-- Results
--
DIST_TO_CENTRE
--------------
2028.53989685933``````
If I have a table that has a column of sdo_geometry column that will describe the rectangle and I believe it should be an SDO_GTYPE of 2003.
Should I simply store the upper right and lower left pairs as long/lat? If so what sort of spatial query function(s) would I use to figure this out.
Yes, store as 2003 polygon geometry of type optimized rectangle. See above for example.

But you can see from the above, only if you provide REAL data can we REALLY help you.

regards
Simon
• ###### 2. Re: REALLY New to spatial...
Hey Simon,

Wow that is a lot of information. So here is some actual data ( I got these points from google maps ). The crux here is traffic management. What my question boils down to is this: If I have a Doppler radar hit that tells me a rain squall is located at:

-122.69 , 37.85 and is moving 090 degrees true at is moving along at say 20 kph

My bounding rectangle surrounds a section of highway:

upper left = -122.3072, 37.87870
lower right = -122.2994 , 37.85017

So the idea here is to know IF and WHEN that rain squall will cross my bounding box. Now the data I have provided, it WILL pass directly over the bounding box ( visual determination ) if nothing changes. Again using features of google maps I measured the distance as ~ 37 km. So if it is moving at 20 kph then it should arrive at the bounding rectangle in ~1.85 hours. The notion is that If I can determine this and create this as a future event and project that out, I can tell the traffic control center to then start metering the on-ramp flow to the highway to prevent the jam condition that will form as people start using larger following distances and generally slowing down. This rain condition effects the free flow speed of the road from dry pavement and clear conditions. Obviously other things that would be nice to know is the intensity of the rain squall ie: the worse it is the longer the lag at the metering areas will be since intensity amplifies the aforementioned effects.

Sp the problem comes down to:

1. Extending the line of travel ( projecting the course over distance )
2. Determine if it crosses this bounding box ( or any other bounding box in the system as defined to encompass large sections or roadway )
3. The distance from the point to the bounding box.

Once I know which box it will cross then I can come up with the ETA since I will then know the two locations and can compute distance.

Is that enough data and detail?

• ###### 3. Re: REALLY New to spatial...
FlyingGuy,

Well, looking at your response I can see that my posting was very HELPFUL (but no points), as it had all the elements of the result.

Secondly, the SQL above can be easily modified to give you:

1. The point where the track of the storm passes over the bounding box, and
2. The time to get there from the starting point (uber trivial once you have the distance).

(I presume this is what you meant by the distance to the centre of the bounding box which is not the same as the point the track covers the bounding box.)

However, I do not have access to my Oracle db at the moment and will respond in about 6 hours time.

But surely you want the point on the highway that the track will cross because, depending on the length and direction of the highway, a bounding box will be quite inaccurate. As such my solution will give you a way of calculating both. To help, I would like the sdo_geometry of the highway.

Regards
Simon
• ###### 4. Re: REALLY New to spatial...
FlyingGuy,

Here is my solution to your problem.

I have not used your rectangle/mbr. Rather I have used a pretend highway as it the highway we are worried about crossing. This version of the solution shows how it is better to use the highway than the rectangle. (Perhaps buffer of highway better?)

An image of what I did is available here: http://www.spatialdbadvisor.com/files/RainSqualHitsHighway.png

The SQL is:
``````With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
select max(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.rect.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M')) as dist
from geoms g,
table(sdo_util.getVertices(g.highway)) v;
-- Results
--
DIST
----
15531.09

-- Maximum distance to highway from point is 15531.09 meters.

-- Compute point at bearing 45 degrees and 15531.09 meters away from observed rain squal start point
--
With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
select SDO_UTIL.POINT_AT_BEARING(g.point,
56,
max(SDO_GEOM.SDO_DISTANCE(g.point,sdo_geometry(2001,g.highway.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))) as point2
from geoms g,
table(sdo_util.getVertices(g.highway)) v;
-- Results
-- Point furthest from our point to which it could hit the highway is
--
POINT2
------
SDO_GEOMETRY(2001,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-122.6697493, 37.3222959))

-- The generates line looks like

With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
from (select g.highway,
sdo_geom.sdo_union(g.point,
SDO_UTIL.POINT_AT_BEARING(g.point,
max(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.highway.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))),
from geoms g,
table(sdo_util.getVertices(g.highway)) v
) f;
-- Results
-- Heading of rain squall depicted as a Line
--
DIRECTION_AS_LINE
-----------------
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.57836,37.20293,-122.429350131876,37.2763510664655))

-- Now compute intersection point of rain squal line with rectangle and highway
--
With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
select sdo_geom.sdo_intersection(f.highway,
0.005) as highway_intersection,
sdo_geom.sdo_intersection(f.rect,
0.005) as rect_intersection
from (select g.highway,
g.rect,
sdo_geom.sdo_union(g.point,
SDO_UTIL.POINT_AT_BEARING(g.point,
max(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.highway.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))),
from geoms g,
table(sdo_util.getVertices(g.highway)) v
) f;
-- Results
-- Note while intersection with rectangle is not null there is no intersection with the highway
-- which is why the rectangle is not a good surrogate for the highway (perhaps buffered highway would be better?)
--
HIGHWAY_INTERSECTION RECT_INTERSECTION
-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------
NULL                 SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.51074549161,37.2362863056168,-122.500689999999,37.2412411858053))

-- Now try with line that will hit.... change heading to 0 degrees
--
With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
from (select g.highway,
sdo_geom.sdo_union(g.point,
SDO_UTIL.POINT_AT_BEARING(g.point,
max(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.highway.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))),
from geoms g,
table(sdo_util.getVertices(g.highway)) v
) f;
-- Results
-- Here is the new heading at 0 degrees
--
DIRECTION_AS_LINE
-----------------
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.57836,37.20293,-122.57836,37.3428714662851))

-- Now let's compute the intersection with the highway
--
With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
select sdo_geom.sdo_intersection(f.highway,
0.005) as intersection
from (select g.highway,
sdo_geom.sdo_union(g.point,
SDO_UTIL.POINT_AT_BEARING(g.point,
max(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.highway.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))),
from geoms g,
table(sdo_util.getVertices(g.highway)) v
) f;
-- Results
--
INTERSECTION
-------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-122.57836,37.2632679001176))

-- Ergo the time to arrive is....

With geoms as (
select SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-122.659906,37.23628,-122.50069,37.32835)) as rect,
SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(-122.50069,37.32835,-122.53250,37.320711,-122.50927,37.276741,-122.51856,37.246085,-122.55944,37.246705,-122.59907,37.281386,-122.62694,37.258162,-122.659906,37.23628)) as highway,
SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(-122.57836,37.20293,NULL),NULL,NULL) as point,
20 as speed_kmh
from dual
)
select to_char(to_date(trunc(sdo_geom.sdo_distance(f.intersection_point,f.start_point,0.005,'unit=KM') / f.speed_kmh * 3600.0),'sssss'),'hh24:mi:ss') as TIME_TO_HIT_HIGHWAY
from (select h.speed_kmh,
h.start_point,
sdo_geom.sdo_intersection(h.highway,
0.005) as intersection_point
from (select g.speed_kmh,
g.highway,
g.point as start_point,
sdo_geom.sdo_union(g.point,
SDO_UTIL.POINT_AT_BEARING(g.point,
max(SDO_GEOM.SDO_DISTANCE(point,sdo_geometry(2001,g.highway.sdo_srid,sdo_point_type(v.x,v.y,null),null,null),0.005,'unit=M'))),
from geoms g,
table(sdo_util.getVertices(g.highway)) v
) h
) f
where f.intersection_point is not null;
-- Results
--
TIME_TO_HIT_HIGHWAY
-------------------
00:20:05``````