match line with road segment

[searchelite]

hi all..
i have a line table consists of gps points, i'm using ST_makeline(gps_points) to create the line..my question is, how to match the gps line into road segment in postgis?

thank you

[Stephen Woodbridge]

searchelite wrote:
> hi all..
> i have a line table consists of gps points, i'm using
> ST_makeline(gps_points) to create the line..my question is, how to match the
> gps line into road segment in postgis?
>
> thank you

You can buffer the line and see which segments fall inside the buffer.

select * from roads
  where ST_Contains(the_geom, ST_makeline(gps_points));

-Steve
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite]

Stephen Woodbridge wrote:

You can buffer the line and see which segments fall inside the buffer.

select * from roads
  where ST_Contains(the_geom, ST_makeline(gps_points));

-Steve

thank you for the reply..can you be more detail please..i've tried your suggestion but it returned no record.

I create a simple illustration below.. the green line is the record from road table, and the red line is the route table generated from ST_Makeline(gps_points), I want to make the red line snap into the road so it become the route



Thank you in advance

[Paragon Corporation-2]

You may be better off snapping the points to the line first before you do a
make line.  Take a look at Paul's blog entry.  


http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html

Sounds similar to what you are trying to do.

Hope that helps,
Regina

 

-----Original Message-----
From: postgis-users-bounces@...
[mailto:postgis-users-bounces@...] On Behalf Of
searchelite
Sent: Thursday, July 17, 2008 5:34 AM
To: postgis-users@...
Subject: Re: [postgis-users] match line with road segment



Stephen Woodbridge wrote:
>
> You can buffer the line and see which segments fall inside the buffer.
>
> select * from roads
>   where ST_Contains(the_geom, ST_makeline(gps_points));
>
> -Steve
>

thank you for the reply..can you be more detail please..i've tried your
suggestion but it returned no record.

I create a simple illustration below.. the green line is the record from
road table, and the red line is the route table generated from
ST_Makeline(gps_points), I want to make the red line snap into the road so
it become the route

http://www.nabble.com/file/p18504794/untitled.jpg 

Thank you in advance

--
View this message in context:
http://www.nabble.com/match-line-with-road-segment-tp18479996p18504794.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite]

Paragon Corporation-2 wrote:

You may be better off snapping the points to the line first before you do a
make line.  Take a look at Paul's blog entry.  


http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html

Sounds similar to what you are trying to do.

Hope that helps,
Regina

Thanks for the reply...Yes, I already did that (snapping the point to the line)..so, is there any idea what should i do next?
thanks

[P.Rizzi Ag.Mobilità Ambiente]

If I undestood it well enough and you are trying to match
GPS tracked points upon a road system, you should be careful
about crossings.
Where two roads cross, a GPS point may be nearer to the crossing road
instead of the one the vehicle actually was.
So you should ensure to somehow "stick" to the "correct" road.
Maybe by calculating the azimuth of each individual two-consecutive-points
segment and searching for the road nearest to the ending point
of the segment _and_ with the azimuth most similar to the segment itself.
Obviuosly sometimes a vehicle actually turns into a crossing road,
so you should also recognize that somehow and _don't_ "stick" in that case...

Bye
Paolo Rizzi

> -----Messaggio originale-----
> Da: postgis-users-bounces@...
> [mailto:postgis-users-bounces@...]Per conto di
> searchelite
> Inviato: giovedì 17 luglio 2008 16.23
> A: postgis-users@...
> Oggetto: RE: [postgis-users] match line with road segment
>
>
>
>
>
> Paragon Corporation-2 wrote:
> >
> > You may be better off snapping the points to the line first
> before you do
> > a
> > make line.  Take a look at Paul's blog entry.  
> >
> >
> >
> http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html
> >
> > Sounds similar to what you are trying to do.
> >
> > Hope that helps,
> > Regina
> >
> >
>
> Thanks for the reply...Yes, I already did that..so, is there
> any idea what
> should i do next?
> thanks
> --
> View this message in context:
> http://www.nabble.com/match-line-with-road-segment-tp18479996p

18509643.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Regina Obe]

Now you do your make line.  But you'll have to take into consideration what Paolo Rizzi said afterward.  Not sure the easiest way to do that.

 

So basically your process should look something like the below where trip_id is some fied that uniquely identifies each trip.  The below snaps each point to closest road segment and then makes a line for each trip.  It assumes the pt.gid is in the order of time travel.  If you have a datetime field for each point, use that instead of the pt.gid.

 

 SELECT trip_id, ST_MakeLine(snapped_point) As trip_route

(SELECT
pt_id,
ln_id,

ST_Line_Interpolate_Point(
 ln_geom,
 ST_Line_Locate_Point(ln_geom, pt_geom)
 ) As snapped_point,

travel_id

FROM
(
SELECT DISTINCT ON (pt.gid)
 ln.the_geom AS ln_geom,
 pt.the_geom AS pt_geom,
 ln.id AS ln_id,
 pt.gid AS pt_id,pt.trip_id
FROM
gps_points pt INNER JOIN
road_table ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY
pt.gid,ST_Distance(ln.the_geom, pt.the_geom)
) AS subquery ORDER BY trip_id, pt_id) As snapped_points

GROUP BY travel_id;

 

Hope that helps,

Regina

---

From: postgis-users-bounces@... on behalf of searchelite
Sent: Thu 7/17/2008 10:23 AM
To: postgis-users@...
Subject: RE: [postgis-users] match line with road segment

Paragon Corporation-2 wrote:

>
> You may be better off snapping the points to the line first before you do
> a
> make line.  Take a look at Paul's blog entry. 
>
>
> http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html
>
> Sounds similar to what you are trying to do.
>
> Hope that helps,
> Regina
>
>

Thanks for the reply...Yes, I already did that (snapping the point to the
line)..so, is there any idea what should i do next?
thanks
--
View this message in context: http://www.nabble.com/match-line-with-road-segment-tp18479996p18509643.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

---

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.

---

Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite ()]

P.Rizzi Ag.Mobilità Ambiente wrote:

If I undestood it well enough and you are trying to match
GPS tracked points upon a road system, you should be careful
about crossings.
Where two roads cross, a GPS point may be nearer to the crossing road
instead of the one the vehicle actually was.
So you should ensure to somehow "stick" to the "correct" road.
Maybe by calculating the azimuth of each individual two-consecutive-points
segment and searching for the road nearest to the ending point
of the segment _and_ with the azimuth most similar to the segment itself.
Obviuosly sometimes a vehicle actually turns into a crossing road,
so you should also recognize that somehow and _don't_ "stick" in that case...

Bye
Paolo Rizzi

Thanks for the idea paolo..yes i've been considered that case..
btw, can you give me an example in how to do that?
thank you again

[searchelite]

Obe, Regina DND\MIS wrote:

Now you do your make line.  But you'll have to take into consideration what Paolo Rizzi said afterward.  Not sure the easiest way to do that.
 
So basically your process should look something like the below where trip_id is some fied that uniquely identifies each trip.  The below snaps each point to closest road segment and then makes a line for each trip.  It assumes the pt.gid is in the order of time travel.  If you have a datetime field for each point, use that instead of the pt.gid.
 
 SELECT trip_id, ST_MakeLine(snapped_point) As trip_route
(SELECT
pt_id,
ln_id,

ST_Line_Interpolate_Point(
 ln_geom,
 ST_Line_Locate_Point(ln_geom, pt_geom)
 ) As snapped_point,
travel_id

FROM
(
SELECT DISTINCT ON (pt.gid)
 ln.the_geom AS ln_geom,
 pt.the_geom AS pt_geom,
 ln.id AS ln_id,
 pt.gid AS pt_id,pt.trip_id
FROM
gps_points pt INNER JOIN
road_table ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY
pt.gid,ST_Distance(ln.the_geom, pt.the_geom)
) AS subquery ORDER BY trip_id, pt_id) As snapped_points
GROUP BY travel_id;
 
Hope that helps,
Regina

thanks a lot for the reply...i'll try the code you give me first thing tomorrow...

[searchelite]

searchelite wrote:

Obe, Regina DND\MIS wrote:

Now you do your make line.  But you'll have to take into consideration what Paolo Rizzi said afterward.  Not sure the easiest way to do that.
 
So basically your process should look something like the below where trip_id is some fied that uniquely identifies each trip.  The below snaps each point to closest road segment and then makes a line for each trip.  It assumes the pt.gid is in the order of time travel.  If you have a datetime field for each point, use that instead of the pt.gid.
 
 SELECT trip_id, ST_MakeLine(snapped_point) As trip_route
(SELECT
pt_id,
ln_id,

ST_Line_Interpolate_Point(
 ln_geom,
 ST_Line_Locate_Point(ln_geom, pt_geom)
 ) As snapped_point,
travel_id

FROM
(
SELECT DISTINCT ON (pt.gid)
 ln.the_geom AS ln_geom,
 pt.the_geom AS pt_geom,
 ln.id AS ln_id,
 pt.gid AS pt_id,pt.trip_id
FROM
gps_points pt INNER JOIN
road_table ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY
pt.gid,ST_Distance(ln.the_geom, pt.the_geom)
) AS subquery ORDER BY trip_id, pt_id) As snapped_points
GROUP BY travel_id;
 
Hope that helps,
Regina

thanks a lot for the reply...i'll try the code you give me first thing tomorrow...

basically, the code you gave me just create a line from snapped points right? what about matching the route with the road? I want to make the route like the illustration below




thank you

[Luigi Castro Cardeles]

hi,

just a suggestion: you should try a check to identify the line segment that don't lies in the road.

ST_PointN(geometry,integer) you can iterate and create the line segments.
then you can run a ST_Contains to check if your road segment matches route segment.
you should save the information of the line segment after and before the ST_Contains fails.
with that information you can find the vertice and them interpolate to create him.

best regards,
 
Luigi Castro Cardeles

2008/7/18 searchelite <searchelite@...>:

searchelite wrote:
>
>
>
> Obe, Regina     DND\MIS wrote:
>>
>> Now you do your make line.  But you'll have to take into consideration
>> what Paolo Rizzi said afterward.  Not sure the easiest way to do that.
>>
>> So basically your process should look something like the below where
>> trip_id is some fied that uniquely identifies each trip.  The below snaps
>> each point to closest road segment and then makes a line for each trip.
>> It assumes the pt.gid is in the order of time travel.  If you have a
>> datetime field for each point, use that instead of the pt.gid.
>>
>>  SELECT trip_id, ST_MakeLine(snapped_point) As trip_route
>> (SELECT
>> pt_id,
>> ln_id,
>>
>> ST_Line_Interpolate_Point(
>>  ln_geom,
>>  ST_Line_Locate_Point(ln_geom, pt_geom)
>>  ) As snapped_point,
>> travel_id
>>
>> FROM
>> (
>> SELECT DISTINCT ON (pt.gid)
>>  ln.the_geom AS ln_geom,
>>  pt.the_geom AS pt_geom,
>>  ln.id AS ln_id,
>>  pt.gid AS pt_id,pt.trip_id
>> FROM
>> gps_points pt INNER JOIN
>> road_table ln
>> ON
>> ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
>> ORDER BY
>> pt.gid,ST_Distance(ln.the_geom, pt.the_geom)
>> ) AS subquery ORDER BY trip_id, pt_id) As snapped_points
>> GROUP BY travel_id;
>>
>> Hope that helps,
>> Regina
>>
>>
>
>
> thanks a lot for the reply...i'll try the code you give me first thing
> tomorrow...:-)
>
>

basically, the code you gave me just create a line from snapped points
right? what about matching the route with the road? I want to make the route
like the illustration below

http://www.nabble.com/file/p18522213/untitled.jpg


thank you



--
View this message in context: http://www.nabble.com/match-line-with-road-segment-tp18479996p18522213.html

Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Stephen Woodbridge]

searchelite wrote:
>
> Stephen Woodbridge wrote:
>> You can buffer the line and see which segments fall inside the buffer.
>>
>> select * from roads
>>   where ST_Contains(the_geom, ST_makeline(gps_points));

Sorry, this should have been:

select * from roads
   where ST_Contains(the_geom, Buffer(ST_makeline(gps_points)), "value");

where "value" is the radius of the sausage that you are going to make
the line into. This has to be in the units of you data set, which is
likely degrees. 69 mile ~= 1 degree, 69*5280 ft ~= 1 degree so 50 ft ~=
50/(69*5280) = 0.00013724 so try that for starters and adjust it as
required to get good results.

-Steve

>> -Steve
>>
>
> thank you for the reply..can you be more detail please..i've tried your
> suggestion but it returned no record.
>
> I create a simple illustration below.. the green line is the record from
> road table, and the red line is the route table generated from
> ST_Makeline(gps_points), I want to make the red line snap into the road so
> it become the route
>
> http://www.nabble.com/file/p18504794/untitled.jpg 
>
> Thank you in advance
>

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite]

Stephen Woodbridge wrote:

searchelite wrote:
>
> Stephen Woodbridge wrote:
>> You can buffer the line and see which segments fall inside the buffer.
>>
>> select * from roads
>>   where ST_Contains(the_geom, ST_makeline(gps_points));

Sorry, this should have been:

select * from roads
   where ST_Contains(the_geom, Buffer(ST_makeline(gps_points)), "value");

where "value" is the radius of the sausage that you are going to make
the line into. This has to be in the units of you data set, which is
likely degrees. 69 mile ~= 1 degree, 69*5280 ft ~= 1 degree so 50 ft ~=
50/(69*5280) = 0.00013724 so try that for starters and adjust it as
required to get good results.

-Steve

thank you steve for your reply, i guess the buffer should be ST_buffer right? but unfortunately it's not working...it said that aggregates not allowed in WHERE clause

thank you

[searchelite]

Luigi Castro Cardeles wrote:

hi,

just a suggestion: you should try a check to identify the line segment that
don't lies in the road.

ST_PointN(geometry,integer) you can iterate and create the line segments.
then you can run a ST_Contains to check if your road segment matches route
segment.
you should save the information of the line segment after and before the
ST_Contains fails.
with that information you can find the vertice and them interpolate to
create him.

best regards,

Luigi Castro Cardeles

thanks for the suggestion, but there is a problem...I'm using ST_makeline to create the route so it only return 1 linestring. When i check with ST_contains(road_segment, route) it returns f values for all the record

thanks

[Paragon Corporation-2]

Are you just looking to get back the portions of the line segments with the
line segment id of the line segments that intersect your road.

If so, then now that you have your single line, you can splice it with the
road segments by doing

SELECT r.gid As road_gid, t.tripid, ST_Intersection(r.the_geom,
triproute.the_geom) As road_segment
 FROM roads r INNER JOIN triproute t ON ST_Intersects(r.the_geom,
triproute.the_geom)

The only problem with the above is that it will splice your trip into the
various road segments making it up, but then you loose the time path that
make line gave you.  
It may not matter depending on what you are doing though.

If you want to still maintain a sense of time and splice such that you have
something like

Timestart, trip_id, trip_road_segment, road_gid

Then that's a tad bit trickier and requires you do this after you do the
snap points.


Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces@...
[mailto:postgis-users-bounces@...] On Behalf Of
searchelite
Sent: Monday, July 21, 2008 4:56 AM
To: postgis-users@...
Subject: Re: [postgis-users] match line with road segment




Luigi Castro Cardeles wrote:

>
> hi,
>
> just a suggestion: you should try a check to identify the line segment
> that don't lies in the road.
>
> ST_PointN(geometry,integer) you can iterate and create the line segments.
> then you can run a ST_Contains to check if your road segment matches
> route segment.
> you should save the information of the line segment after and before
> the ST_Contains fails.
> with that information you can find the vertice and them interpolate to
> create him.
>
> best regards,
>
> Luigi Castro Cardeles
>
>

thanks for the suggestion, but there is a problem...I'm using ST_makeline to
create the route so it only return 1 linestring. When i check with
ST_contains(road_segment, route) it returns f values for all the record

thanks
--
View this message in context:
http://www.nabble.com/match-line-with-road-segment-tp18479996p18564625.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite]

Paragon Corporation-2 wrote:

Are you just looking to get back the portions of the line segments with the
line segment id of the line segments that intersect your road.

If so, then now that you have your single line, you can splice it with the
road segments by doing

SELECT r.gid As road_gid, t.tripid, ST_Intersection(r.the_geom,
triproute.the_geom) As road_segment
 FROM roads r INNER JOIN triproute t ON ST_Intersects(r.the_geom,
triproute.the_geom)

The only problem with the above is that it will splice your trip into the
various road segments making it up, but then you loose the time path that
make line gave you.  
It may not matter depending on what you are doing though.

If you want to still maintain a sense of time and splice such that you have
something like

Timestart, trip_id, trip_road_segment, road_gid

Then that's a tad bit trickier and requires you do this after you do the
snap points.


Hope that helps,
Regina

thanks for the idea regina, it's almost what i want to..it's just that now i don't have a connected route..my purpose is to match the route line with a road segment. From the illustration that i gave, i have to connect the two point between the intersection with the intersection so i have a route match with a road. I have the end_point and start_point in every road segment. any idea how to do that?

Thank you, sorry for the rough explanation, i hope you can understand my problem

[Regina Obe]

Does this work for you?- main issue I see is that it wouldn't work if
your travel involved crossing segments you already crossed.  For that
its even trickier and would involve probably 2 correctlate sub selects.


Basic below is
Do the snap points to line segment routine to get the points matched
with a line,
order the points by trip, road segment and time
and then make line of the points along that line segment
- getting the max and min gps time to denote the start of time of
segment and end time of segment.

SELECT ln_id, MIN(gps_time) As start_time, MAX(gps_time) As end_time
ST_MakeLine(snapped_point) As road_segment
FROM
(SELECT *  
FROM (
SELECT DISTINCT ON (pt.id)
pt.trip_id
 ln.id AS ln_id,
 pt.id AS pt_id,
ST_Line_Interpolate_Point(
 ln.the_geom,
 ST_Line_Locate_Point(ln.the_geom, pt.the_geom)
 ) As snapped_point, pt.gps_time
FROM
point_table pt INNER JOIN
line_table ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY
pt.id,ST_Distance(ln.the_geom, pt.the_geom)) As newpoints
ORDER BY trip_id, ln_id, gps_time) As ordered_points
GROUP BY trip_id, ln_id;

Hope that helps,
Regina



-----Original Message-----
From: postgis-users-bounces@...
[mailto:postgis-users-bounces@...] On Behalf Of
searchelite
Sent: Wednesday, July 23, 2008 3:39 AM
To: postgis-users@...
Subject: RE: [postgis-users] match line with road segment




Paragon Corporation-2 wrote:
>
> Are you just looking to get back the portions of the line segments
with
> the
> line segment id of the line segments that intersect your road.
>
> If so, then now that you have your single line, you can splice it with
the
> road segments by doing
>
> SELECT r.gid As road_gid, t.tripid, ST_Intersection(r.the_geom,
> triproute.the_geom) As road_segment
>  FROM roads r INNER JOIN triproute t ON ST_Intersects(r.the_geom,
> triproute.the_geom)
>
> The only problem with the above is that it will splice your trip into
the
> various road segments making it up, but then you loose the time path
that

> make line gave you.  
> It may not matter depending on what you are doing though.
>
> If you want to still maintain a sense of time and splice such that you
> have
> something like
>
> Timestart, trip_id, trip_road_segment, road_gid
>
> Then that's a tad bit trickier and requires you do this after you do

the
> snap points.
>
>
> Hope that helps,
> Regina
>
>

thanks for the idea regina, it's almost what i want to..it's just that
now i
don't have a connected route..my purpose is to match the route line with
a
road segment. From the illustration that i gave, i have to connect the
two
point between the intersection with the intersection so i have a route
match
with a road. I have the end_point and start_point in every road segment.
any
idea how to do that?

Thank you, sorry for the rough explanation, i hope you can understand my
problem
--
View this message in context:
http://www.nabble.com/match-line-with-road-segment-tp18479996p18605499.h
tml
Sent from the PostGIS - User mailing list archive at Nabble.com.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite]

Obe, Regina DND\MIS wrote:

Does this work for you?- main issue I see is that it wouldn't work if
your travel involved crossing segments you already crossed.  For that
its even trickier and would involve probably 2 correctlate sub selects.


Basic below is
Do the snap points to line segment routine to get the points matched
with a line,
order the points by trip, road segment and time
and then make line of the points along that line segment
- getting the max and min gps time to denote the start of time of
segment and end time of segment.

SELECT ln_id, MIN(gps_time) As start_time, MAX(gps_time) As end_time
ST_MakeLine(snapped_point) As road_segment
FROM
(SELECT *  
FROM (
SELECT DISTINCT ON (pt.id)
pt.trip_id
 ln.id AS ln_id,
 pt.id AS pt_id,
ST_Line_Interpolate_Point(
 ln.the_geom,
 ST_Line_Locate_Point(ln.the_geom, pt.the_geom)
 ) As snapped_point, pt.gps_time
FROM
point_table pt INNER JOIN
line_table ln
ON
ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY
pt.id,ST_Distance(ln.the_geom, pt.the_geom)) As newpoints
ORDER BY trip_id, ln_id, gps_time) As ordered_points
GROUP BY trip_id, ln_id;

Hope that helps,
Regina



-----Original Message-----
From: [e-mail]
[mailto:[e-mail]] On Behalf Of
searchelite
Sent: Wednesday, July 23, 2008 3:39 AM
To: [e-mail]
Subject: RE: [postgis-users] match line with road segment




Paragon Corporation-2 wrote:
>
> Are you just looking to get back the portions of the line segments
with
> the
> line segment id of the line segments that intersect your road.
>
> If so, then now that you have your single line, you can splice it with
the
> road segments by doing
>
> SELECT r.gid As road_gid, t.tripid, ST_Intersection(r.the_geom,
> triproute.the_geom) As road_segment
>  FROM roads r INNER JOIN triproute t ON ST_Intersects(r.the_geom,
> triproute.the_geom)
>
> The only problem with the above is that it will splice your trip into
the
> various road segments making it up, but then you loose the time path
that
> make line gave you.  
> It may not matter depending on what you are doing though.
>
> If you want to still maintain a sense of time and splice such that you
> have
> something like
>
> Timestart, trip_id, trip_road_segment, road_gid
>
> Then that's a tad bit trickier and requires you do this after you do
the
> snap points.
>
>
> Hope that helps,
> Regina
>
>

I've tried your aprroach but it's not really working, i still got the route which doesnt match with the road segment. Btw, i've managed to do it with different approach. I've made a table that consists the node of the road segment, after that i create ST_makeline(snapped_point, node). I got the route match with the road segment  but i still have to perfect it..any suggestion about this?

thank you

[Regina Obe]

 


>I've tried your aprroach but it's not really working, i still got the
route
>which doesnt match with the road segment. Btw, i've managed to do it
with
>different approach. I've made a table that consists the node of the
road
>segment, after that i create ST_makeline(snapped_point, node). I got
the
>route match with the road segment :-D but i still have to perfect
it..any
>suggestion about this?

>thank you

I wonder if you have the worst case I was worried about. Can you send a
snapshot of what the
last query I gave you produces.  

Thanks,
Regina



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[searchelite ()]

Obe, Regina DND\MIS wrote:

 
I wonder if you have the worst case I was worried about. Can you send a
snapshot of what the
last query I gave you produces.  

Thanks,
Regina

I got the result just like the image below




about my approach above, i dont think i can use ST_makeline(geometry, geometry) right? because if i use that it will give me many linestring not one linestring, it will connect every point in the first geometry into the points in second geometry.. so i guess i still havent found the solution to my problem..

Thanks,