Challenge: Query ways refs and nodes geometries. Return polygons, keeping refs order.

[emi420]

Do you have good Postgres skills? we need your help :)

Underpass can keep a local copy of the OSM database. For rendering OSM data on a UI, we need to query a boundary box and return a GeoJSON with the ways polygons as features.

Data

It looks something like the following.

Ways

way_id	refs
731200449	{6848777775,6848777776,6848777777,10942922912,6848777778,6848777779,6848777775}

Nodes

osm_id	geometry
6848777775	POINT (-64.3001687 -31.0696472)
6848777776	POINT (-64.3002557 -31.0697271)
6848777777	POINT (-64.3002418 -31.0699626)
10942922912	POINT (-64.3002217 -31.0699613)
6848777778	POINT (-64.2999753 -31.0699448)
6848777779	POINT (-64.3000054 -31.0697673)
6848777775	POINT (-64.3001687 -31.0696472)

Query

I'm experimenting with this query:

with t0 AS ( SELECT osm_id AS node_id FROM raw_node_tmp WHERE ST_Intersects(\"geometry\", ST_GeomFromText(' \
POLYGON((-64.2967751 -31.07191454, -64.2967751 -31.0690751, -64.3021156 -31.0690751, -64.3021156 -31.07191454, -64.2967751 -31.07191454)) \
', 4326)) LIMIT 500 ), \
t1 AS (SELECT osm_id AS way_id, unnest(refs) AS node_id FROM raw_way_tmp WHERE refs && array(SELECT * FROM t0)::bigint[] ), \
t2 AS (SELECT way_id, osm_id, geometry FROM t1 JOIN raw_node_tmp ON node_id = raw_node_tmp.osm_id), \
t3 AS (SELECT way_id, ST_MakeLine(geometry) AS linestring FROM t2 GROUP BY way_id ), \
t4 AS (SELECT way_id, ST_MakePolygon(ST_AddPoint(t3.linestring, ST_StartPoint(t3.linestring))) AS polygon FROM t3), \
t5 AS ( SELECT jsonb_build_object( 'type', 'Feature', 'id', t4.way_id, 'properties', to_jsonb(t4) - 'polygon' , 'geometry', ST_AsGeoJSON(polygon)::jsonb ) AS feature FROM t4 ) \
SELECT jsonb_build_object( 'type', 'FeatureCollection', 'features', jsonb_agg(t5.feature) ) FROM t5;

It works as the following:

1. Select nodes that intersects with the boundary box
2. Select ways that have those nodes in their refs
3. Join the nodes geometries for all the refs
4. Make LineStrings from nodes geometries
5. Make Polygons from LineStrings, adding the first element at the end to close the polygon
6. Build JSON objects for each row
7. Build a single GeoJSON document with all the rows

Problem

Refs order is not being kept, resulting in broken polygons:

---

---

We need to find a way to keep the order of the refs, but I'm not an Postgres expert at all :) could you help? it will be much appreciated!

For making things easier, I've created a test that includes sample data and the query, returning a GeoJSON that you can render in https://geojson.io or whatever you want use.

wayrefs-nodes-geojson.zip

You can make it work just editing the CONN_STRING variable for connecting it to a Postgres database, with the PostGIS installed on.

Thank you!

[caprieldeluca]

Hi, the order of the elements, for the refs bigint[] array defined field, can be extracted with the generate_subscripts() function (documented at: https://www.postgresql.org/docs/current/functions-srf.html).

For example:

WITH ways AS (
 SELECT 
  osm_id AS way_id,
  generate_subscripts(refs, 1) AS idx,
  refs
 FROM raw_way_tmp),
unnested_ways AS (
 SELECT 
  way_id,
  idx,
  refs[idx] AS node_id
 FROM ways),
distinct_nodes AS (
 SELECT DISTINCT
  osm_id AS node_id,
  geometry
 FROM raw_node_tmp),
ways_nodes AS (
 SELECT
  w.way_id AS way_id,
  w.idx AS idx,
  n.geometry AS geometry
 FROM unnested_ways AS w
 LEFT JOIN distinct_nodes AS n
 USING (node_id)),
linestrings AS (
 SELECT 
  way_id,
  ST_MakeLine(geometry ORDER BY idx) AS geometry
 FROM ways_nodes
 GROUP BY way_id)
SELECT
 way_id,
 ST_AsText(geometry) AS wkt,
 ST_IsClosed(geometry) AS is_closed,
 ST_IsSimple(geometry) AS is_simple
FROM linestrings
LIMIT 10;

It is more like a proof of concept:
The ways table has not the refs unnested, but the generated subscripts as idx.
The unnested_ways table extracts the refs element for each idx.
Distinct_nodes table removes duplicate records from the data source.
The ways_nodes table is the join using node_id.
And linestrings table creates the rings.

It returns:

way_id	wkt	is_closed	is_simple
731200449	"LINESTRING(-64.3001687 -31.0696472,-64.3002557 -31.0697271,-64.3002418 -31.0699626,-64.3002217 -31.0699613,-64.2999753 -31.0699448,-64.3000054 -31.0697673,-64.3001687 -31.0696472)"	true	true
1039593602	"LINESTRING(-64.3001099 -31.0694204,-64.3000484 -31.0694324,-64.299987 -31.0694579,-64.299966 -31.0695136,-64.3000204 -31.0695707,-64.300087 -31.0695662,-64.3001836 -31.0695391,-64.3001941 -31.0694745,-64.3001801 -31.0694414,-64.3001099 -31.0694204)"	true	true
1039593603	"LINESTRING(-64.3001923 -31.0693497,-64.300166 -31.0693873,-64.3001941 -31.0694159,-64.3002573 -31.0694098,-64.3002503 -31.0693527,-64.3001923 -31.0693497)"	true	true
1177341200	"LINESTRING(-64.2988856 -31.0710112,-64.2987311 -31.0709979,-64.2987248 -31.0710518,-64.2987741 -31.071056,-64.2987679 -31.0711091,-64.2988731 -31.0711181,-64.2988856 -31.0710112)"	true	true
1177341202	"LINESTRING(-64.2988649 -31.0711834,-64.2987864 -31.0711756,-64.2987836 -31.0711958,-64.2987465 -31.0711922,-64.2987434 -31.0712152,-64.2986815 -31.0712091,-64.2986747 -31.07126,-64.2988522 -31.0712777,-64.2988649 -31.0711834)"	true	true
1177341203	"LINESTRING(-64.2977309 -31.0712988,-64.2976094 -31.0712918,-64.2976125 -31.071253,-64.2975706 -31.0712506,-64.2975749 -31.071196,-64.2975943 -31.0711971,-64.297596 -31.071176,-64.2976829 -31.071181,-64.2976812 -31.0712023,-64.2977382 -31.0712056,-64.2977309 -31.0712988)"	true	true
1177356676	"LINESTRING(-64.2991841 -31.0710657,-64.299115 -31.0711022,-64.2992098 -31.0712337,-64.2993149 -31.0711782,-64.2992881 -31.0711397,-64.2992451 -31.0710813,-64.2991841 -31.0710657)"	true	true
1177356678	"LINESTRING(-64.2990636 -31.0714364,-64.2989167 -31.0714321,-64.2989132 -31.0715188,-64.2989726 -31.0715205,-64.2989719 -31.0715389,-64.2990594 -31.0715415,-64.2990636 -31.0714364)"	true	true
1177358310	"LINESTRING(-64.2984913 -31.0709882,-64.2984418 -31.0709837,-64.2984393 -31.0710039,-64.2983825 -31.0709986,-64.2983762 -31.071049,-64.2984824 -31.0710588,-64.2984913 -31.0709882)"	true	true
1177358312	"LINESTRING(-64.2985258 -31.0713495,-64.2985028 -31.0713493,-64.2985032 -31.0713187,-64.2984386 -31.0713182,-64.2984371 -31.0714545,-64.2985247 -31.0714551,-64.2985258 -31.0713495)"	true	true

[omranlm]

@emi420 it seems you wanna do this on the database level, I would suggest ST_MakePolygon to create a Polygon from an open LineString, using ST_StartPoint and ST_AddPoint to close it.
Ref: https://postgis.net/docs/ST_MakePolygon.html

[omranlm]

oh, now I got it.. we are loosing the order in t1 query

[kshitijrajsharma]

This is how osm2pgsql does it , It should solve the issue !
planet_osm_nodes===== your nodes tables
unnest refnodes with ordinatility first

    if (parser.is_polygon && way->isClosed()) {
        // Insert
        insert_sql = str(format(R"sql(
          INSERT INTO %1%.planet_osm_%2%
            (osm_id, way, tags %3%)
          VALUES ($1, ST_SetSRID( ST_MakePolygon( ST_MakeLine( ARRAY(
            SELECT ST_MakePoint(n.lon/10000000.0 , n.lat/10000000.0) FROM %1%.planet_osm_nodes n
            JOIN UNNEST(%6%::bigint[]) WITH ORDINALITY t(id, ord) USING (id) ORDER BY t.ord ))), 4326 ),
          %5% %4%)
        )sql") % schema % "polygon" %
                         parser.tag_field_names % parser.tag_field_values % parser.tags_hstore_literal % refs);

    }

[emi420]

Finally, I opted for storing and keeping updated geometries for ways also, not only for nodes, so the query is so much easy now.

Thank you all for your contributions!