[flex output] Get the first node and last node of a linestring using lua, and insert into as a point in the database

[julienfastre]

First, thanks for developing flex, this is a pleasure to use it.

This is our use case: we are developing a spatial analysis which will compute the distance between different points on the same level (a platform in a station). We would like to know, for instance, the distance between the steps and a railway=platform_marker node.

For this, we summary every object on the train's platform on a point geometry.

This is very easy with elevator, which, for instance, are already a point.

For steps, we are currently using the centroid of the linestring. But, for accuracy, we would like to take the step where it does reach the platform level. Steps are a linestring. So, for this, we have to analyze the first and the last node of the linestring, and the content of the level=* key to know if it is going up or down. This will create two points in the table: one with the step's start and the corresponding level, and another with the step's end and the corresponding level. Later in the database, our queries will do a processing to compare the platform's level with the step's one.

So, it would be nice to be able to extract the first and last from a linestring, and be able to create a "geometry" from that.

Thanks.

[joto]

I am not sure I understand your use case sufficiently. Getting at the first and last points in a linestring from PostgreSQL/PostGIS is reasonably easy and should be fast enough. Does this not work in your case? Because you are doing that distance calculation in PostGIS anyway, aren't you?

I can imagine that osm2pgsql will support such a function in the future, but want to understand all the issues there first. I see two different ways to implement this:

1. A function to get the nth point in a linestring as you proposed (object:as_linestring():pointn(1))
2. A function to directly get the nth point from the OSM object (object:node(1):as_point() or object:as_point(1) or so)

The second option would potentially be much faster if you don't need the whole linestring, but just one or two points like in your case, because we don't have to get the coordinates of the nodes we are not using. But it might be a bit harder for the user to understand and would be a bit more effort to implement. (We have a similar issue with members of relations.)

[julienfastre]

Getting at the first and last points in a linestring from PostgreSQL/PostGIS is reasonably easy and should be fast enough. Does this not work in your case? Because you are doing that distance calculation in PostGIS anyway, aren't you?

You are right. But I will need guarantee that the order of points in OSM matches the order of points in the postgis line. Is this the case ?

Our use case is the following: we want to calculate with enough accuracy the distance between all object on a train platform, since a starting point which is the place where the train driver stops his vehicle. Knowing the train composition, we will be able to locate all the amenities inside the train: where to enter the train when you are a disabled person, where will be the bike are, the first classes. We want to be able to say to the user: "wait for the train between the steps and the clock", "go next to the marker A1", ...

On train platforms, there are steps. For now, we consider the centroid of the steps. It works, but some steps are very long and we would like to do better: knowing exactly where the steps arrive on the platform.

For instance, this step comes from level 0 and arrives on level 1. We are thinking (maybe a bad solution ?) to store in the database two points: the first one, associated with level 0, and the last one, associate with level 1.

Table objects:

osm_id	kind	tags	level	geom (point)
1500	step	{}	1	last node of the osm object
1500	step	{}	0	first node of the osm object

As the platform is also on level 1, the last point of the way is the one which arrives at the platform. And the query would be something like:

SELECT
    all_the_thing_we_need
FROM 
    objects
    JOIN platforms WHERE ST_contains(platforms.geom, objects.point) AND object.level = platforms.level

But, you are right, if we have the guarantees that order matters when creating postgis's linestring from osm's ways, it's ok to compute that in postgis.

But if this need can be share with each other, this one:

A function to get the nth point in a linestring as you proposed (object:as_linestring():pointn(1))

would be ok. We would also need the ability to get the number of nodes in the osm's way to get the last one.

[joto]

You are right. But I will need guarantee that the order of points in OSM matches the order of points in the postgis line. Is this the case ?

Yes, of course.

[julienfastre]

Thanks !