Problem with geometries with 'GeometryCollection' type from/to geojson

[duvifn]

Hi again,
Probably I'm doing something wrong again but I can't figure this out.
I'm using the following statement to add geometries of type GeometryCollection to spl.js:

CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, src_id TEXT);
SELECT AddGeometryColumn('test', 'geometry', 4326, 'GEOMETRY', 'XY');

Then:

INSERT OR REPLACE INTO test (src_id, geometry)
 VALUES ('1', 
          SetSRID(
            GeomFromGeoJSON(
              '{"type":"GeometryCollection","geometries":[{"coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]],"type":"LineString"},{"coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]],"type":"Polygon"},{"coordinates":[37.17246065497398,33.80763700738737],"type":"Point"}]}'), 
          4326));

However when I query this table I get null for field geometry.

It worth mentioning that the following query returns a good geometry:

select SetSRID(
   GeomFromGeoJSON('{"type":"GeometryCollection","geometries":[{"type":"LineString", "coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]]},{"type":"Polygon", "coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]]},{"type":"Point", "coordinates":[37.17246065497398,33.80763700738737]}]}'),
  4326);

Also any other geometry type (other than 'GeometryCollection') works fine.

What am I doing wrong?

Thank you very much!

[jvail]

Hello,

might be a bug - maybe I need to treat Collections differently when converting from a geometry blob to GeoJSON. I'll look into it.
Could you try converting in the query like SELECT AsGeoJSON(geometry) ... - do you get a proper result then or still null?

[duvifn]

Could you try converting in the query like SELECT AsGeoJSON(geometry) ... - do you get a proper result then or still null?

Still null.

[jvail]

I guess that your insert in not working properly. Is anything in the table after your insert? ... just guessing.

Since theSELECT AsGeoJSON(geometry) FROM test returns null I am sure it is not the convenience GeoJSON conversion in JavaScript.

I would recommend to try to test the query in SpatiaLite command line first. Otherwise please post the complete JavaScript. Then I can test it.

[duvifn]

I think I found the reason (unless I did something really silly).
The following code produce geometry null. However if you use the insertStatement2 the geometry is valid.
The only difference between the two is the order in which geometry attributes in the json are appearing (in the valid one, the type is first in the sub-geometries json)

    const db =<...>;
    const tableName = "test";
    const srid = 4326;
    const createTable = `CREATE TABLE ${tableName} (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, src_id TEXT);`;
    const addGeometry = `SELECT AddGeometryColumn('${tableName}', 'geometry', ${srid}, 'GEOMETRY', 'XY');`;
    const insertStatement = `INSERT OR REPLACE INTO ${tableName} (src_id, geometry) VALUES ('1',SetSRID(GeomFromGeoJSON('{"type":"GeometryCollection","geometries":[{"coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]],"type":"LineString"},{"coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]],"type":"Polygon"},{"coordinates":[37.17246065497398,33.80763700738737],"type":"Point"}]}'),${srid}));`;
    const insertStatement2 = `INSERT OR REPLACE INTO ${tableName} (src_id, geometry) VALUES ('1',SetSRID(GeomFromGeoJSON('{"type":"GeometryCollection","geometries":[{"type":"LineString", "coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]]},{"type":"Polygon", "coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]]},{"type":"Point", "coordinates":[37.17246065497398,33.80763700738737]}]}'),${srid}));`;
    await db.exec(createTable).get;
    await db.exec(addGeometry).get;
    await db.exec(insertStatement).get;
    const response = await db.exec(`SELECT * from ${tableName};`).get;
    console.log(await response.first);

[duvifn]

Also, a weird behavior executing the code above (this might be another issue or it might be related somehow), if I execute CREATE TABLE statement and SELECT addGeometryColumn in the same call, the second line is not executed and I get an error:

table test has no column named geometry thrown

For example the following code throws an error to me:

    const db = <...>;
    const tableName = "test";
    const srid = 4326;
    const createTable = `CREATE TABLE ${tableName} (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, src_id TEXT);
                         SELECT AddGeometryColumn('${tableName}', 'geometry', ${srid}, 'GEOMETRY', 'XY');`;
    const insertStatement = `INSERT OR REPLACE INTO ${tableName} (src_id, geometry) VALUES ('1',SetSRID(GeomFromGeoJSON('{"type":"GeometryCollection","geometries":[{"coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]],"type":"LineString"},{"coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]],"type":"Polygon"},{"coordinates":[37.17246065497398,33.80763700738737],"type":"Point"}]}'),${srid}));`;
    const insertStatement2 = `INSERT OR REPLACE INTO ${tableName} (src_id, geometry) VALUES ('1',SetSRID(GeomFromGeoJSON('{"type":"GeometryCollection","geometries":[{"type":"LineString", "coordinates":[[35.17246065497398,31.807637007387367],[35.1730225777626,31.807637007387367],[35.1730225777626,31.807292779878278],[35.17246065497398,31.807292779878278],[35.17246065497398,31.807637007387367]]},{"type":"Polygon", "coordinates":[[[36.17246065497398,32.80763700738737],[36.1730225777626,32.80763700738737],[36.1730225777626,32.80729277987828],[36.17246065497398,32.80729277987828],[36.17246065497398,32.80763700738737]]]},{"type":"Point", "coordinates":[37.17246065497398,33.80763700738737]}]}'),${srid}));`;
    await db.exec(createTable).get;
    await db.exec(insertStatement2).get;
    const response = await db.exec(`SELECT * from ${tableName};`).get;
    console.log(await response.first);

I'm posting this here since it might be related. But perhaps should I open another issue for it?

[jvail]

Hi @duvifn,

indeed, the ordering of GeoJSON attributes required by SpatiaLite is odd, or a bug if you like: https://groups.google.com/g/spatialite-users/c/jqIdvEIMLn4/m/F06LvCmTCAAJ

Frankly, your coding style is a bit hard to read. also maybe try using parameters to avoid issues with quotes and you can insert multiple values if you like. Here is minimal example for the browser that works well for me:

(async () => {
    try {

        const db = await SPL([], {}).then(spl => spl.db());

        const tableName = "test";
        const srid = 4326;
        const json = `{
            "type": "GeometryCollection",
            "geometries":[
                { "type": "LineString", "coordinates": [ [0,0], [1,1] ] },
                { "type": "Polygon", "coordinates": [ [[0,0], [0,1], [1,0], [0,0]] ] },
                { "type": "Point", "coordinates": [1,1] }
            ]
        }`;
        const script = `
            SELECT InitSpatialMetadata(1);
            CREATE TABLE ${tableName} (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, src_id TEXT);
            SELECT AddGeometryColumn('${tableName}', 'geometry', ${srid}, 'GEOMETRY', 'XY');
        `;
        await db.read(script);
        await db.exec(
            `INSERT OR REPLACE INTO ${tableName} (src_id, geometry) VALUES (@id, SetSRID(GeomFromGeoJSON(@json), ${srid}))`,
            [
                { '@id': 1, '@json': json },
                { '@id': 2, '@json': json }
            ]
        );
        const response = await db.exec(`SELECT * FROM ${tableName};`).get.objs;
        console.log(response);

    } catch (err) {
        console.log(err);
    }
})();

[duvifn]

Ah, OK. Thanks a lot.
So I guess I have to write a custom json stringify function.

The code sections I posted above are examples I extracted for this issue.
My code actually looks completely different and managed across different classes.

From your code example I realize that exec is only for one line statement while read is for multiline script (this is why my second code example didn't work). Am I right?

[duvifn]

From your code example I realize that exec is only for one line statement while read is for multiline script (this is why my second code example didn't work). Am I right?

Never mind. I now see this is explicitly documented.

[jvail]

Great that it sis solved.

The code sections I posted above are examples I extracted for this issue.
My code actually looks completely different and managed across different classes.

I just meant, that it is much easier for me to find the issue if the example code is clearer and contains just a necessary minimum.