1 2 Previous Next 16 Replies Latest reply on Feb 5, 2013 7:08 PM by user12035460

# Oracle Spatial function to find nearest line string based on lat/long

Hi,

Here is my scenario. I have a table that contains geometries of type line strings (the roadway network). The line geomteries are of type Ohio state plane south (SRID 41104).
I have a requirement - given a lat/long, find the line string that snaps to that lat/long or the nearest set of line strings within a distance of 0.02 miles.
This is a typical example of trying to identify a crash location on our roadway network. The crashes being reported to us in lat/long thru the GPS system.
How can i acheive this through any spatial functions?

Thanks for the help in advance.

thanx,
L.
• ###### 1. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi and welcome to the Spatial forum!

Try SDO_WITHIN_DISTANCE | http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i77653]

Regards,
Noel
• ###### 2. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi L,
``````SELECT
r.<columname>
FROM
WHERE SDO_NN(r.geometry,
sdo_geometry(2001, 41104, sdo_point_type(lat,lon,NULL), NULL, NULL),
'sdo_num_res=2 distance=0.02 unit=mile') = 'TRUE';``````
This will give you the two closest road segments within 0.02 miles. See [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_operat.htm#i78067]the documentation about the SDO_NN operator, especially the bit about using the index hint.

HTH,
Stefan
• ###### 3. Re: Oracle Spatial function to find nearest line string based on lat/long
<off topic>
Hi Noel,

You must've posted while I was typing :-)

Cheers,
Stefan
</off topic>
• ###### 4. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi Stephan,

First, thank you for responding and helping me out!

I tried to use this SDO_NN functionality as you mentioned. But I didn't not get any results back nor an error.
Then I realized that since I am passing a lat/long (WGS84), but my line strings are of SRID 41104, and hence I did not get any results back.
So I tried to use SRID 4326 for lat/long representation.

SELECT
r.<column_name>
FROM
WHERE SDO_NN(r.GEOMETRY,
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(39.971867,-83.119285,NULL), NULL, NULL),
'sdo_num_res=2 distance=1.0 unit=mile') = 'TRUE';

Still it doesn't give any results. I even tried to increase the distance to 1 mile, yet no results.
Do I have the SRID for lat-long correct? Or should I transform the lat/long to 41104 and then try to find the line string? Am I missing something here?
Since I am new to Spatial concepts, I appreciate any help.

Thanx,
L.
• ###### 5. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi Noel,

Thank you. As you and Stephan mentioned I am looking into the SDO_NN function.

Thanks,
L.

Edited by: user12035460 on Jan 31, 2013 5:28 AM
• ###### 6. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi L,

Some functions implicitly transform geometries of two different SRID's to the same, but I'm not sure SDO_NN does that. So I would transform your point first from WGS84 to OHIO State plane south.
``````SELECT
r.<columname>
FROM
WHERE SDO_NN(r.geometry,
SDO_CS.TRANSFORM(sdo_geometry(2001, 41104, sdo_point_type(39.971867,-83.119285,NULL), NULL, NULL), 41104),
'sdo_num_res=2 distance=0.02 unit=mile') = 'TRUE';``````
See if that returns anything. BTW, the sdo_num_res operator says that you will get max 2 results back, you may want to change that to 1 or discard it at all.

HTH,
Stefan
• ###### 7. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi Stephan and Noel,

After trying it several times, finally found a silly mistake i made in specifying the lat/long. I gave them in the wrong order!

The query works very good when the lat/long order was changed. I also included the SDO_DISTANCE along with the query to get the closest neighbor to the point. Works good.

This is just a start of my project in my department. And my department is pretty new to oracle spatial. And hence I will be having more questions coming up regarding spatial.

Thanks,
L.
• ###### 8. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi Stephan,

I didn't need to use the transform function...The SDO_NN operation all by itself converts the geom2 to type geom1; It is inbuilt within the functionality.

Thanks again,
L.
• ###### 9. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi,

I successfully found the line string that is closest to the lat long provided, but how do snap that lat/long to the line string and get the measure value?
Say e.g. the lat long i provided said that the point lies close to a roadway segment IR70 with measure from milemarker 1.50 - 1.75;
but how can i snap that given lat/long and get an answer telling it happened at IR70 @ 1.53?

Thanks,
L.
• ###### 10. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi L,

That is a different question, but basically you should be able to do that using the LRS functionality: [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_lrs_concepts.htm#BABIIFFH]see the documentation.

Specifically the function that should do what you want to achieve is [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_lrs_ref.htm#i85426]SDO_LRS.FIND_MEASURE. You give that function a road segment and a point, and then it calculates the measure (which in your case would be the mile markers) closest to the projection of the point onto the road segment.

Just out of curiosity: Are you trying to build a road incident management tool? Because there is software out there that does this kind of thing already for you.

Cheers,
Stefan
• ###### 11. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi Stephan,

I am not aware of the Road incident management tool. Is that supported by oracle?

The project I am working on is to log the accidents coming in from the police officers on to our roadway network, which can later be analyzed by our safety engineers.
The accidents come in to us in different ways.. e.g.
1. The police officers might have a gps system that they can use to say the accident happened at this location (provided the officers are on the accident site when they hit the GPS system!).
2. They can give us a reference location like intersection or milepost and let us know that the accident happened some distance offset from this reference point.
3. They can give us an address as a reference point, in which case we use the address geocoding.

This is exactly what we are trying to do..match them against our roadway network with the information given by the officers.
Do u think Oracle has something that does this? if so I am unaware of this.

Thanks,
L.
• ###### 12. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi L,

Ah ok, so this is for incident analysis (for the OH-DOT maybe?). In that case you may not need the stuff I was thinking about, allthough there is some software that might help with what you want to do. I would also talk to the Police Dispatch organization, because they may be able to give you other data as well or in a different way which may require less work. It depends a bit. I used to work for a company that develops Computer Aided Dispatch software (the software 911-control rooms run on, and frequently use Oracle as DB of choice), and with that came a lot of extra's, for example a module that allowed for incident analysis based on the records of the 911-control room, that's why I thought of this. If you want to know more we should probably take this discussion offline (since that company is not Oracle - uses Oracle as DB, but... plus I also do not work for them anymore).
1. The police officers might have a gps system that they can use to say the accident happened at this location (provided the officers are on the accident site when they hit the GPS system!).
Well, this one we figured out
2. They can give us a reference location like intersection or milepost and let us know that the accident happened some distance offset from this reference point.
Definitely look into the LRS stuff. It can do things like this for you.
3. They can give us an address as a reference point, in which case we use the address geocoding
Oracle can also do Geocoding: [url http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_geocode_concepts.htm#CIHECECC]check the documentation

Cheers,
Stefan
• ###### 13. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi Stephan,

I looked at the SDO_LRS.FIND_MEASURE and also couple of other functions like SDO_LRS.PROJECT_PT. What is the different between these two functions?
Note: the lat/long I get from the police officer does not always fall on our line segment. It might be offset to the line segment.

I also realized my line string geometry does not have lrs measures. So i might need to use CONVERT_TO_LRS_GEOM to get the measures and then project the point to the estimated closest line string and get the measure.
Seems to be using lots of functions to get to what i want.

a. First trying to locate the closest segments to the given lat/long - using SDO_NN
b. convert the line string geom to have measures - Using CONVERT_TO_LRS_GEOM
c. project the point (lat/long in different SRID ) on to the closest line string using SDO_LRS.PROJECT_PT; this might also need to transform the lat/long to 41104 (SPS) since SDO_LRS.PROJECT_PT does not support 2 different geoms.
d. Finally use SDO_LRS.GET_MEASURE of the projected point.

Am i correct? I am not sure how to go ahead and write this query.

Thanks,
L.
• ###### 14. Re: Oracle Spatial function to find nearest line string based on lat/long
Hi L,

That is not the way I would do it. I would convert my road segments to LRS data, then you can do all queries on the same data.
Or, if you do not want to modify your original data, create a copy of your road segments with the same ID's and convert the copy into LRS data. If you keep the ID's identical, you can easily use geometry from one and LRS data from the other - as long as you are sure the ID is the same.

Which will make the workflow a bit easier:

1. Use SDO_NN to get the closest segments
2. Use SDO_LRS.PROJECT_PT to get the projected point
3. Use SDO_LRS.GET_MEASURE to get the measure

And most of these you can incorporate into one single query. Now I am writing this of the top of my head (It's been a while since I played with LRS). so this has not been tested, but something like this should work (but could probably be greatly improved - it's getting late for me :-) ):
``````SELECT
SDO_LRS.FIND_MEASURE  --//find_measure needs an LRS segment and a point
(
(
SELECT            --//here we select the LRS segment
r.geometry
FROM
WHERE SDO_NN(r.geometry,    --//based on the given GPS point
sdo_geometry(2001, 41104, sdo_point_type(lat,lon,NULL), NULL, NULL),
'sdo_num_res=2 distance=0.02 unit=mile') = 'TRUE'
),
SDO_LRS.PROJECT_PT  --//We project the point on the LRS segment
(
(
SELECT         --//here we select the LRS segment (again, which could probably be improved!!)
r.geometry
FROM
WHERE SDO_NN(r.geometry,
sdo_geometry(2001, 41104, sdo_point_type(lat,lon,NULL), NULL, NULL),
'sdo_num_res=2 distance=0.02 unit=mile') = 'TRUE'
),
sdo_geometry(2001, 41104, sdo_point_type(lat,lon,NULL), NULL, NULL) --//The GPS point again
)
)
AS milemarker from dual;``````
So it is not as complicated as you think, it can easily be done with just one query (SQL can do a lot more than you think ;-) ).

Good luck,
Stefan