This content has been marked as final.
Show 4 replies

1. Re: REALLY New to spatial...
Simon Greener Jun 22, 2012 11:24 PM (in response to FlyingGuy)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:
2. Assume we have a point whose heading is 56 degrees and speed is 80 km per hour.SDO_GEOMETRY(2003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(144.533217950225,43.3288392547696,144.656799114 473,43.3172501109063))
Firstly:(2001,8307,SDO_POINT_TYPE(144.591427805722,43.341131942977,NULL),NULL,NULL)
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:
as follows:SDO_GEOM.SDO_DISTANCE( geom1 IN SDO_GEOMETRY, geom2 IN SDO_GEOMETRY, tol IN NUMBER [, unit IN VARCHAR2] ) RETURN NUMBER;
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 SDO_GEOM.SDO_DISTANCE(point,rect,0.005,'unit=M') as dist from geoms;  Result DIST  1365.043
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: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
as follows:SDO_UTIL.POINT_AT_BEARING( start_point IN SDO_GEOMETRY, bearing IN NUMBER, distance IN NUMBER ) RETURN SDO_GEOMETRY;
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.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))
NOTE: the point_at_distance function as returned the point within the sdo_ordinate_array and not in the sdo_point_type structure.
Answer: Yes they overlap.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', sdo_geometry(2002,f.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),f.line_at_heading.sdo_ordinates), 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'))), 0.005) as line_at_heading from geoms g, table(sdo_util.getVertices(g.rect)) v ) f;  Results  ANYINTERACTION  OVERLAPBDYDISJOINT
To answer:
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.
Yes, store as 2003 polygon geometry of type optimized rectangle. See above for example.
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.
If this posting has helped answer your question, please award me the relevant points.
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...
FlyingGuy Jun 23, 2012 2:00 PM (in response to Simon Greener)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 onramp 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?
Thanks in advance! 
3. Re: REALLY New to spatial...
Simon Greener Jun 23, 2012 6:11 PM (in response to FlyingGuy)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...
Simon Greener Jun 24, 2012 8:31 PM (in response to Simon Greener)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:
Please follow through the example with your own data. Again if the solution is correct or helpful please award points.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, 45 as heading, 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, 45 as heading, 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, 45 as heading, 20 as speed_kmh from dual ) select sdo_geometry(2002,f.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),f.line_at_heading.sdo_ordinates) as direction_as_line from (select g.highway, sdo_geom.sdo_union(g.point, SDO_UTIL.POINT_AT_BEARING(g.point, g.heading, 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'))), 0.005) as line_at_heading 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, 45 as heading, 20 as speed_kmh from dual ) select sdo_geom.sdo_intersection(f.highway, sdo_geometry(2002,f.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),f.line_at_heading.sdo_ordinates), 0.005) as highway_intersection, sdo_geom.sdo_intersection(f.rect, sdo_geometry(2002,f.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),f.line_at_heading.sdo_ordinates), 0.005) as rect_intersection from (select g.highway, g.rect, sdo_geom.sdo_union(g.point, SDO_UTIL.POINT_AT_BEARING(g.point, g.heading, 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'))), 0.005) as line_at_heading 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, 0 as heading, 20 as speed_kmh from dual ) select sdo_geometry(2002,f.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),f.line_at_heading.sdo_ordinates) as direction_as_line from (select g.highway, sdo_geom.sdo_union(g.point, SDO_UTIL.POINT_AT_BEARING(g.point, g.heading, 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'))), 0.005) as line_at_heading 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, 0 as heading, 20 as speed_kmh from dual ) select sdo_geom.sdo_intersection(f.highway, sdo_geometry(2002,f.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),f.line_at_heading.sdo_ordinates), 0.005) as intersection from (select g.highway, sdo_geom.sdo_union(g.point, SDO_UTIL.POINT_AT_BEARING(g.point, g.heading, 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'))), 0.005) as line_at_heading 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, 0 as heading, 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, sdo_geometry(2002,h.line_at_heading.sdo_srid,null,sdo_elem_info_array(1,2,1),h.line_at_heading.sdo_ordinates), 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, g.heading, 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'))), 0.005) as line_at_heading 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
regards
Simon
Edited by: Simon Greener on Jun 25, 2012 11:30 AM to show why rectangle is not a good surrogate for the highway.