TopoGeo_ToGeoTableGeneralize produces empty output

[Francesco Frassinelli]

Hi everybody!

I am importing some geojson multipolygons representing administrative boundaries into Spatialite, because I need to simplify their geometries while maintaining the topology.

My problem is that the last step of the process, which involves TopoGeo_ToGeoTableGeneralize, produces a table with an empty Geometry column and no error at all. What could it be?

.load mod_spatialite
SELECT InitSpatialMetaData();

CREATE TABLE comuni (name TEXT NOT NULL PRIMARY KEY);
SELECT AddGeometryColumn('comuni', 'Geometry', 4326, 'MULTIPOLYGON', 'XY');

INSERT INTO comuni
SELECT name, Geometry
  FROM (
        SELECT name, SetSRID(GeomFromGeoJson(
                json_extract(data, '$.geometry')
            ), 4326) AS Geometry
          FROM fsdir('output')
         WHERE name LIKE '%/poly/comuni/%.geojson'
);

SELECT CreateTopology('topology', 4326, 0, 0);
SELECT TopoGeo_FromGeoTable('topology', NULL, 'comuni', 'Geometry');

SELECT ST_ValidateTopoGeo('topology');
SELECT * FROM TEMP.topology_validate_topogeo;
SELECT TopoGeo_UpdateSeeds('topology');

SELECT TopoGeo_ToGeoTableGeneralize('topology', NULL, 'comuni', 'Geometry', 'comuni_simplified_1000m', 1000);

I am following this guide: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=topo-intermediate. I would like to mention that the first example contains an invocation of TopoGeo_FromGeoTable which is no more valid.

"TopoGeo_ToGeoTable" produces geometries correctly.

As the example provided by the wiki works as expected, I wonder if it could be a problem in my data, but they look clean to me.

$ rpm -q libspatialite sqlite
libspatialite-5.0.0-3.fc34.x86_64
sqlite-3.33.0-1.fc33.x86_64

Any idea?

Regards,

FraFra / Francesco Frassinelli

[a.fu...@lqt.it]

On Mon, 28 Dec 2020 09:06:12 -0800 (PST), Francesco Frassinelli wrote:
> Any idea?
>

ciao Francesco,

I'll be glad to deeply examine your databse so to set up a
thorough debugging session, but I absolutely need a copy
of your dataset.

can you kindly send it to <a.fu...@lqt.it> ?
(also in a private form, if you are not willing to
publicly disclose your data).

bye Sandro

[a.fu...@lqt.it]

On Mon, 28 Dec 2020 09:06:12 -0800 (PST), Francesco Frassinelli wrote:

> My problem is that the last step of the process, which involves
> TopoGeo_ToGeoTableGeneralize, produces a table with an empty Geometry
> column and no error at all. What could it be?
>

ciao Francesco,

SOLVED :-D

it drove me crazy for many hours searching what was wrong in your SQL
or if it was some obscure bug, but at the end of the day I've finally
caught it.

conclusion: there is nothing wrong in TopoGeo_ToGeoTableGeneralize(),
it works nicely as expected; there was just an absurd value passed
as an argument. I've to say that it was under my nose for hours,
and I wasn't able to see it. :-P
let's see what was the real cause of failure:

SELECT TopoGeo_ToGeoTableGeneralize('topology', NULL, 'comuni',
'Geometry', 'comuni_simplified_1000m', 1000);

the last argument (1000) is the tolerance factor for simplifying,
that's to say that it's a distance radius.
but distances _MUST_ be expressed in the natural unit of measure
for the adopted SRID, that in this case is 4326 WGS84, a geographic
reference system based on _ANGLES_ of latitude and longitude,
adopting _DEGREES_ as the unit of measure.
conclusion: a tolerance of thousand degrees is obviouly an
absurdity, and consequently the underlying algorithm
just returned NULL Geometries.

SELECT TopoGeo_ToGeoTableGeneralize('topology', NULL, 'comuni',

'Geometry', 'comuni_simplified_1000m', 0.01);

this second corrected statement will work ;-)
note: at the equator 1 longitude degree = 111.1 Km, so
0.01 degrees are roughly equivalent to 1 Km

bye Sandro

[Francesco Frassinelli]

Il giorno mar 29 dic 2020 alle ore 18:46 <a.fu...@lqt.it> ha scritto:
>
> On Mon, 28 Dec 2020 09:06:12 -0800 (PST), Francesco Frassinelli wrote:
> > My problem is that the last step of the process, which involves
> > TopoGeo_ToGeoTableGeneralize, produces a table with an empty Geometry
> > column and no error at all. What could it be?
> >
>
> ciao Francesco,
>
> SOLVED :-D
>
> it drove me crazy for many hours searching what was wrong in your SQL
> or if it was some obscure bug, but at the end of the day I've finally
> caught it.

Great! Sorry to hear that it took so much time!

> the last argument (1000) is the tolerance factor for simplifying,
> that's to say that it's a distance radius.
> but distances _MUST_ be expressed in the natural unit of measure
> for the adopted SRID, that in this case is 4326 WGS84, a geographic
> reference system based on _ANGLES_ of latitude and longitude,
> adopting _DEGREES_ as the unit of measure.
> conclusion: a tolerance of thousand degrees is obviouly an
> absurdity, and consequently the underlying algorithm

> just returned NULL Geometries.

That makes sense :-)

Would looking for empty Geometry values be a reasonable check after each call to TopoGeo_ToGeoTableGeneralize then?

Bye,

FraFra

[a.fu...@lqt.it]

On Wed, 30 Dec 2020 10:34:53 +0100, Francesco Frassinelli wrote:
> Would looking for empty Geometry values be a reasonable check after
> each call to TopoGeo_ToGeoTableGeneralize then?
>

it seems to be a very reasonable assumption

bye Sandro

[Andrea Peri]

Hi,

Pay attention that an Empty Geometry is different from NULL value geometry.

An empty geometry is not null-valued.

example: POINT EMPTY is an Empty Geometry of Kind POINT.

Regards,

A.

[a.fu...@lqt.it]

On Sat, 29 May 2021 15:21:57 -0700 (PDT), Andrea Peri wrote:
> Hi,
>
> Pay attention that an Empty Geometry is different from NULL value
> geometry.
> An empty geometry is not null-valued.
>
> example: POINT EMPTY is an Empty Geometry of Kind POINT.
>

Andrea,

you are absolutely right: these are the requirements of the
theoric model of OGC Geometries.

anyway it should be considered that SpatiaLite does not supports
EMPTY geometries, so in the peculiar case of SpatiaLite there
is no way to distinguish between EMPTY and NULL geometries,
because both will be represented as NULL.

it's surely a defect due to an initial underestimations
present since the very first version and that became
practically impossibile to resolve in following versions
without introduce a paramount breakage in backward
compatibility and with serious risks of many unexpected
regressions in the code.

conclusions: yes, it's certainly a disturbing, annoying
and unelegant glitch. but about fifteen years of successful
experiences on the field tell us that many users can rather
easily adapt to this.

bye Sandro

[Andrea Peri]

All ok Sandro

My puntialization was about the wrong term used.

A null value mean only "null", not empty. The empty simply can't exist in spatialite.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/f18bdad9939da82a5ade2d86ab6f5914%40lqt.it.