[Tile-serving] [osm2pgsql-dev/osm2pgsql] [flex output] Get the first node and last node of a linestring using lua, and insert into as a point in the database (Discussion #2091)

[Julien Fastré]

> 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](https://www.openstreetmap.org/way/1210517109) 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:

```sql
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.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2091#discussioncomment-7265091
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2091/comments/7265091 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231012/8257c621/attachment.htm>