[postgis-users] Sorting/ordering linestrings

[Charles Galpin]

Does anyone have any bright ideas on how to sort linestrings (with postgis or anything else for that matter).

The idea is I have some set of linestrings which mostly if not all will be part of an interconnected "route" or roadway, but not sorted from start to finish in say a particular direction.

My goal is to sort/order them so that they are in order as you'd traverse the network, keeping in mind there may be gaps or other randomness to the linestrings but ill be generally related. I don't have a start/end or I'd route between them to get the ordering.

Thanks,
charles

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

[fork]

Charles Galpin <cgalpin <at> lhsw.com> writes:

> Does anyone have any bright ideas on how to sort linestrings (with postgis
>or anything else for that matter).

You could sort them based on their beginning and ending lat's and long's, and
then connect them if they are within a certain tolerance, probably using
a loop.

You might also want to make a distance matrix and iteratively merge "close
enough" linestrings.

However, if you can give us some specifics about your problem (schemas,
typical geometries and attributes) we might come up with something more
useful and less hand-wavy.

[Stephen Woodbridge]

Charles Galpin<cgalpin<at> lhsw.com> writes:

> Does anyone have any bright ideas on how to sort linestrings (with postgis
> or anything else for that matter).

ok, need some more criteria here. Sorting linestrings is a proposed
solution to an undisclosed problem. What is your problem?

If you have not additional criteria, then:

select *
from my_table
order by st_x(st_startpoint(the_geom)), st_y(st_startpoint(the_geom));

or maybe just:

select *
from my_table
order by the_geom::text;

-Steve

[Charles Galpin]

Thanks for responding Steve (and fork)

On Mar 30, 2011, at 7:47 PM, Stephen Woodbridge wrote:

> Charles Galpin<cgalpin<at> lhsw.com> writes:
>
>> Does anyone have any bright ideas on how to sort linestrings (with postgis
>> or anything else for that matter).
>
> ok, need some more criteria here. Sorting linestrings is a proposed solution to an undisclosed problem. What is your problem?

I guess my biggest problem is I don't carry enough clout around here, because I wouldn't be doing this if it was my choice :)

It's a stupid requirement. I can't remember some of the reasons (other than they want it), but I know of one specific use case where it's just plain idiotic and they should use a map and let the user select links of interest from it (reporting tool). Instead they want to present the user with a list of links and let them wade through them (i assume filter) and select the ones they care about, but they want them ordered as if you were traversing the road. A map would be such a cleaner way to do it. It's due to the fact that they currently have a set of data that is much smaller and instead of lots of little short linestrings they have larger road segments essentially from intersection to intersection. And perhaps a perception it's quicker to develop without the map.

So I guess in an ideal world I'd build up linestrings from intersection to intersection and mimic the kind of thing they have today, although they have the same ordering requirement between these larger linestrings too. I also need to enhance our data with the nearest cross roads for the linestrings, and better road name and direction, but again most of this is oriented to how they did things before with a different data set.

Unless you have better ideas I'll revisit using the lat/lon but recall quickly running into problems with curved roads. Also taking a fixed point and comparing the distance to it has it flaws with horseshoe shaped roads and curves etc.

Thanks,
charles