Unable to recover geometry table on table created with route results query

[Stephen Woodbridge]

Hi,

I compute a route and dump the results into a table, then try to recovergeometrycolumn on that table but it fails. I'm guessing the problem is because the first row in the table is the summary row for the route and the geometry is null.

create table route_results as select * from edges_net where NodeFrom=33 and NodeTo=123;

select recovergeometrycolumn('route_table', 'Geometry');
RecoverGeometryColumn(): validation failed
0

select asewkt(Geometry) from route_results where rowid=1;
SRID=-1;LINESTRING()

select asewkt(Geometry) from route_results where rowid=3;
SRID=4326;LINESTRING(-10.76069 6.336939999999999,-10.76451 6.33742)

Using Spatialite 4.3.0a on Windows.

I saw a discussion with Jukka about this failing in spatialite_gui, but not sure what version of spatialite the fix was going into.

-Steve

[mj10777]

On Thursday, 22 September 2016 17:48:35 UTC+2, Stephen Woodbridge wrote:

Hi,

I compute a route and dump the results into a table, then try to recovergeometrycolumn on that table but it fails. I'm guessing the problem is because the first row in the table is the summary row for the route and the geometry is null.

create table route_results as select * from edges_net where NodeFrom=33 and NodeTo=123;

select recovergeometrycolumn('route_table', 'Geometry');
RecoverGeometryColumn(): validation failed
0

This report is correct

- it is a invalid geometry-column

A valid geometry-column MUST have

- the same geometry-type (i.e. not mixed, such as LINESTRING with LINESTRINGZ)

- the same srid (i.e. not mixed, such as -1 with 4326)

select asewkt(Geometry) from route_results where rowid=1;
SRID=-1;LINESTRING()

Assuming that all the geometries should be 4326 (Wsg84)

- then this is the cause of the error 

select asewkt(Geometry) from route_results where rowid=3;
SRID=4326;LINESTRING(-10.76069 6.336939999999999,-10.76451 6.33742)

Using Spatialite 4.3.0a on Windows.

I saw a discussion with Jukka about this failing in spatialite_gui, but not sure what version of spatialite the fix was going into.

If that sample (I don't which discussion you mean) is the same as this one

- then it will still fail spatialite_gui because

-- it is a invalid geometry-column

Mark

-Steve

[Stephen Woodbridge]

I understand, but it makes sense to be able to allow NULL geometries in a table. So I guess what I'm asking for is that recovergeometrycolumn() allow or optionally allow recovery if a table is consistent with a single SRID but has some NULL geometries. Postgis allows this and the output of the spatialite route computation generates this, so the request seems reasonable.

[mj10777]

On Thursday, 22 September 2016 18:18:07 UTC+2, Stephen Woodbridge wrote:

I understand, but it makes sense to be able to allow NULL geometries in a table. So I guess what I'm asking for is that recovergeometrycolumn() allow or optionally allow recovery if a table is consistent with a single SRID but has some NULL geometries. Postgis allows this and the output of the spatialite route computation generates this, so the request seems reasonable.

I have not (deliberately) tried RecoverGeometryColumn with any NULL geometries, but will tomorrow

- but in your case it not NULL, but a valid geometry with an incorrect srid  

[Stephen Woodbridge]

Ok, doing a little more checking on this it seems that my query creating the table  has a left outer join and for some reason is inserting empty geometries with srid=-1 rather than NULLs. I updated the table and set all the geometries with srid=-1 to NULL and was able to recover the geometry table.

I need to investigate why the left outer join is not inserting NULL. I probably messed something up.

After I thought about your post, it made sense and I started looking for other issues which turned up the above.

Thanks!

On Thursday, September 22, 2016 at 11:48:35 AM UTC-4, Stephen Woodbridge wrote: