st_astwkb not returning valid geometry

[furstenheim]

Hi, st_astwkb is not returning a valid geometry. As an example:

`select st_isvalidreason(st_geomfromtwkb(st_astwkb(st_geomfromgeojson('{

"type": "Polygon", "coordinates": [2.1776390993201,41.3722988965004],[2.17763909886103,41.37229889631543],[2.17763909900003,41.3722988970001],[2.1820804568871637,41.37027921005133],[2.18208045654673,41.3702792096251],[2.1776390993201,41.3722988965004]

}'))))

`

[nicklas]

Hi

What you posted is not a valid geojson. Both because of the question mark in it and because of the lack of nestling.

It is just a lot of point couple arrays not a point array in a ring array as polygon demands.

So, what I guess that you want to write is:

select 
  st_isvalidreason(
    st_geomfromtwkb(
      st_astwkb(
        st_geomfromgeojson(
         '{
    "type": "Polygon", "coordinates": [2.1776390993201,41.3722988965004],
[2.17763909886103,41.37229889631543],[2.17763909900003,41.3722988970001],
[2.1820804568871637,41.37027921005133],[2.18208045654673,41.3702792096251],
[2.1776390993201,41.3722988965004?]
    }'
          )
        )
      )
    )

Then I get the error Too few points for geometry type.

That is because the points are so close so many of them gets removed as duplicates when creating twkb. twkb by default only saves the integer part for geometry.

But even if you tell it to store 7 decimals it is only 2 different points, not 3 as needed for a polygon to be valid.

If you manually remove the precesion beyond 7 decimals in your data you see that you get the same result from geojson:

select st_isvalidreason(
st_geomfromgeojson('{
    "type": "Polygon", "coordinates": [[
[2.1776390,41.3722988],
[2.1776390,41.3722988],
[2.1776390,41.3722988],
[2.1820804,41.3702792],
[2.1820804,41.3702792],
[2.1776390,41.3722988]]]
    }')
    )

[furstenheim]

Indeed, I think the problem was that the code was not in a code block. I meant:

select st_isvalidreason(st_geomfromtwkb(st_astwkb(st_geomfromgeojson('{"type": "Polygon","coordinates": [[[2.1776390993201,41.3722988965004],[2.17763909886103,41.37229889631543],[2.17763909900003,41.3722988970001],[2.1820804568871637,41.37027921005133],[2.18208045654673,41.3702792096251],[2.1776390993201,41.3722988965004]]]}'))))

So basically there is no guarantee that st_astwkb returns a valid geometry out of a valid geometry, which it is since the following is true:

select st_isvalid(st_geomfromgeojson('{"type": "Polygon","coordinates": [[[2.1776390993201,41.3722988965004],[2.17763909886103,41.37229889631543],[2.17763909900003,41.3722988970001],[2.1820804568871637,41.37027921005133],[2.18208045654673,41.3702792096251],[2.1776390993201,41.3722988965004]]]}'))

Is it possible to at least make twkb return a point instead of invalid geometry? With that one would be able to filter out them using st_extractcollect for example.

[nicklas]

Did you read and understand what I just used 20 minutes to investigate and write or are you just trolling?

[furstenheim]

Sorry nicklas, I wanted to edit the question to fix the geojson. I clicked in "Modify Ticket" but that created a new comment

[nicklas]

No problem

[Algunenano]

As nicklas said, this is working as documented.