TopoGeo_ToGeoTableGeneralize produces empty output

43 views
Skip to first unread message

Francesco Frassinelli

unread,
Dec 28, 2020, 12:06:13 PM12/28/20
to SpatiaLite Users
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

unread,
Dec 28, 2020, 12:35:04 PM12/28/20
to spatiali...@googlegroups.com
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

unread,
Dec 29, 2020, 12:46:36 PM12/29/20
to spatiali...@googlegroups.com
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

unread,
Dec 30, 2020, 4:35:06 AM12/30/20
to spatiali...@googlegroups.com
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

unread,
Dec 30, 2020, 5:18:05 AM12/30/20
to spatiali...@googlegroups.com
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

unread,
May 29, 2021, 6:21:57 PMMay 29
to SpatiaLite Users
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

unread,
Jun 2, 2021, 3:01:12 AMJun 2
to spatiali...@googlegroups.com
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

unread,
Jun 2, 2021, 3:19:24 AMJun 2
to spatiali...@googlegroups.com
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.
Reply all
Reply to author
Forward
0 new messages