0

I'm hosting a table of four points in PostGIS and querying it to create a vector tile layer of a line connecting the points.

The second and fourth points in the table are the same.

The resulting linestring as GeoJSON is this:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            -74.002571106,
            40.741008759
          ],
          [
            -73.986976624,
            40.741008759
          ],
          [
            -73.974761963,
            40.729633331
          ],
          [
            -73.986976624,
            40.741008759
          ]
        ],
        "type": "LineString"
      }
    }
  ]
}

The problem is when I load the vector tile layer in MapLibre or Mapbox GL JS, the third point does not show up. If points 2 and 4 are not identical, then it shows the line properly so I think the problem is with the line double backing on itself but I can't figure out why or how to fix it.

There are a few moving parts here so I've created a full reproduction here: https://github.com/mhkeller/postgis-line-repro

The relevant SQL that creates the tile is this:

WITH tilebounds (geom) AS (SELECT ST_TileEnvelope($1, $2, $3)),
query AS (
  SELECT ST_Makeline(geom ORDER BY field_order) as geom FROM path_test 
)
SELECT ST_AsMVT(q, 'default', 4096, 'mvtgeom')
FROM (
  SELECT
    *,
    ST_AsMVTGeom(
      ST_Transform(query.geom, 3857),
      tileBounds.geom,
      4096,
      256,
      true
    ) mvtgeom
  FROM query, tilebounds
  WHERE ST_Transform(query.geom, 3857) && tilebounds.geom
) q

Other queries show up just fine. I stumbled upon this playing around with some dummy data. I tried loading the geojson data and the line renders correctly. I also baked out the vector tiles using geojson2mvt and that rendered the line correctly, too. That makes me think it's not a Maplibre or Mapbox issue.

I tried it using someone else's PostGIS server in case I had messed something up but I get the same result with dirt-simple-postgis-http-api.

6
  • 1
    Just for your info: vector tiles created from the above GeoJSON in MapTiler show just fine in the OpenLayers web map created by MapTiler.
    – TomazicM
    Dec 13, 2023 at 9:22
  • try displaying it with geojson 1st
    – ziggy
    Dec 13, 2023 at 14:39
  • Do you mean to display GeoJSON itself? It works.
    – TomazicM
    Dec 13, 2023 at 14:41
  • I don't want to use GeoJSON. My actual data is served via vector tiles. I provided the GeoJSON as an example of the linestring and also to show that I tried it with GeoJSON and it works without issues – which points to the vector tile being the issue.
    – mhkeller
    Dec 13, 2023 at 17:13
  • My comment was just to point out that vector tiles for this GeoJSON, created with MapTiler, work without problem.
    – TomazicM
    Dec 13, 2023 at 17:18

2 Answers 2

2

I believe that the behavior is caused by simplification that PostGIS is doing as a part of the process when it builds MVT geometries.

A linestring with vertex order 1-2-3-2 is somewhat odd. I made some tests with a geometry that has the same shape than yours but with short coordinates:

LINESTRING (2 2,3 2,4 1,3 2)

For PostGIS the linesting is not simple because it has a self-intersection, but linestrings are allowed to have intersections and therefore ST_IsValid is "true" and ST_MakeValid does not do anything for the geometry.

select st_issimple(
    'LINESTRING (2 2,3 2,4 1,3 2)'
    );

false

select st_isvalid(
    'LINESTRING (2 2,3 2,4 1,3 2)'
    );

true

A usual trick to fix topology errors is to simplify with a tolerance of zero. However, the Douglas-Peucker algorithm never removes the first and last vertex so the result of simplifying this geometry is somewhat surprising. A user awaits 1-2-3 but gets 1-2-2 instead.

select st_astext(
    st_simplify(
    'LINESTRING (2 2,3 2,4 1,3 2)'
    ,0)
    );

"LINESTRING(2 2,3 2,3 2)"

Unfortunately I don't know what to do for getting 1-2-3 linestrings into your MVT tile.

1
  • FWIW the bug also appears no matter where in the linestring the intersection occurs. In this example, it's toward the end, but if you add other points to the table so that the intersection is more toward the middle of the line string, it still cuts out that segment
    – mhkeller
    Dec 16, 2023 at 17:09
0

If the resulting line needs to appear correctly, where all points are connected primarily in the order specified, but where it does not matter that the resulting path ends at the third point instead of the fourth (= second) point, then this alteration of CTE "query" would accomplish that:

query AS (
  SELECT ST_LineMerge(ST_Node(ST_MakeLine(geom ORDER BY field_order))) AS geom
  FROM path_text
)

Explanation:

  • with ST_Node the aggregated LineString is renoded and, in this example, the duplicated line part from point 4 to point 3 is removed
  • using ST_LineMerge the resulting line parts are assembled, when possible, into continuous linestrings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.