extra spaces in values returned by geometrytype function

[David Anderson]

I tried to create table B  that has the same geometry type as table A, so that I can use table B to store results from spatially processing data in table A.

The problem I ran into is that the return value from

select geometrytype(shape) from NRVSemiFinal040516 limit 1

returns 'MULTIPOLYGON ZM'.

However the documentation has 'MULTIPOLYGONZM' as a geometry.  No spaces in the string.

With the spaces, SQL like this fails

select addgeometrycolumn('B','shape',(select srid(shape) from A limit 1),(select geometrytype(shape) from A limit 1))")

I ended up manually creating the table after editing out the space in the geometry type string.

Is the return value with a space the expected behavior of the geometrytype function?

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

On Wed, 25 May 2016 10:02:13 -0700 (PDT), David Anderson wrote:
> I tried to create table B  that has the same geometry type as table
> A, so that I can use table B to store results from spatially
> processing data in table A.
> The problem I ran into is that the return value from
>
> select geometrytype(shape) from NRVSemiFinal040516 limit 1
>
> returns 'MULTIPOLYGON ZM'.
>
> However the documentation has 'MULTIPOLYGONZM' as a geometry.  No
> spaces in the string.
> With the spaces, SQL like this fails
>

Hi David,

for many long years the OGC standard specifications initially just
covered 2D geometries (XY).
so when several DBMSes started supporting a more sophisticated
dimension model (XY, XYZ, XYM, XYZM) a big confusion arose, and we
had a proliferation of mutually incompatible 3D-WKT dialects.

happily enough this story finally come to an happy ending: now a
clear WKT standard exists (ISO_IEC 13249 aka ISO SQL/MM), and
accordingly to the ISO standard the correct spelling should be e.g.:

2D XY: LINESTRING
3D XYZ: LINESTRING Z
2D XYM: LINESTRING M
3D XYZM: LINESTRING ZM

SpatiaLite is rather tolerant when accepting input WKT data,
but will always automatically insert the intermediate space
in any output. examples:

SELECT ST_AsText(ST_GeomFromText('POINTZM(1 2 3 4)'));
--------------------
POINT ZM(1 2 3 4)

SELECT ST_AsText(ST_GeomFromText('POINT ZM(1 2 3 4)'));
--------------------
POINT ZM(1 2 3 4)

SELECT ST_AsText(ST_GeomFromText('POINT ZM ( 1 2 3 4 )'));
--------------------
POINT ZM(1 2 3 4)


the current implementation of AddGeometryColumn() strictly requires
the flavor without any intermediate space: accepting as well the
other alternative flavor (with intemediate spaces) could probably
be an useful bonus for future versions.

> select addgeometrycolumn('B','shape',(select srid(shape) from A limit
> 1),(select geometrytype(shape) from A limit 1))")
>

a more generic and easily reusable SQL query for doing the same task:

SELECT AddGeometryColumn('my_new_table', f_geometry_column, srid,
CASE
WHEN geometry_type = 1 THEN 'POINT'
WHEN geometry_type = 2 THEN 'LINESTRING'
WHEN geometry_type = 3 THEN 'POLYGON'
WHEN geometry_type = 4 THEN 'MULTIPOINT'
WHEN geometry_type = 5 THEN 'MULTILINESTRING'
WHEN geometry_type = 6 THEN 'MULTIPOLYGON'
WHEN geometry_type = 7 THEN 'GEOMETRYCOLLECTION'
WHEN geometry_type = 1001 THEN 'POINTZ'
WHEN geometry_type = 1002 THEN 'LINESTRINGZ'
WHEN geometry_type = 1003 THEN 'POLYGONZ'
WHEN geometry_type = 1004 THEN 'MULTIPOINTZ'
WHEN geometry_type = 1005 THEN 'MULTILINESTRINGZ'
WHEN geometry_type = 1006 THEN 'MULTIPOLYGONZ'
WHEN geometry_type = 1007 THEN 'GEOMETRYCOLLECTIONZ'
WHEN geometry_type = 2001 THEN 'POINTM'
WHEN geometry_type = 2002 THEN 'LINESTRINGM'
WHEN geometry_type = 2003 THEN 'POLYGONM'
WHEN geometry_type = 2004 THEN 'MULTIPOINTM'
WHEN geometry_type = 2005 THEN 'MULTILINESTRINGM'
WHEN geometry_type = 2006 THEN 'MULTIPOLYGONM'
WHEN geometry_type = 2007 THEN 'GEOMETRYCOLLECTIONM'
WHEN geometry_type = 3001 THEN 'POINTZM'
WHEN geometry_type = 3002 THEN 'LINESTRINGZM'
WHEN geometry_type = 3003 THEN 'POLYGONZM'
WHEN geometry_type = 3004 THEN 'MULTIPOINTZM'
WHEN geometry_type = 3005 THEN 'MULTILINESTRINGZM'
WHEN geometry_type = 3006 THEN 'MULTIPOLYGONZM'
WHEN geometry_type = 3007 THEN 'GEOMETRYCOLLECTIONZM'
ELSE 'GEOMETRY'
END
)
FROM geometry_columns
WHERE f_table_name = 'my_old_table' AND f_geometry_column = 'geom';

bye Sandro

[David Anderson]

Sandro, thanks for the response.  I am not surprised about the differing implementations for the names. 

I actually don't know why the data is in the ZM format.  It doesn't have any data for the Z or M coordinates but that it what was handed me.  My original naïve assumption was to use MULTIPOLYGON.  Which failed.

Also thanks for the table translating the codes into the strings.  I am saving that as I have not been able to find it in the documentation.  Which is why I had not used the SQL implementation that you presented.  I did not know how to do the translation.

For the SQL perhaps this might be an alternate option:

select addgeometrycolumn('B','shape',(select srid(shape) from A limit  1),replace((select geometrytype(shape) from A limit 1),' ','')).

It is dependent on the string replacement functionality that the database engine implements.

On Thursday, May 26, 2016 at 1:46:21 AM UTC-6, sandro furieri wrote:

On Wed, 25 May 2016 10:02:13 -0700 (PDT), David Anderson wrote:
> I tried to create table B  that has the same geometry type as table
> A, so that I can use table B to store results from spatially
> processing data in table A.
> The problem I ran into is that the return value from
>
> select geometrytype(shape) from NRVSemiFinal040516 limit 1
>
> returns 'MULTIPOLYGON ZM'.
>
> However the documentation has 'MULTIPOLYGONZM' as a geometry.  No
> spaces in the string.
> With the spaces, SQL like this fails
>

Hi David,

for many long years the OGC standard specifications initially just
covered 2D geometries (XY).
so when several DBMSes started supporting a more sophisticated
dimension model (XY, XYZ, XYM, XYZM) a big confusion arose, and we
had a proliferation of mutually incompatible 3D-WKT dialects.

happily enough this story finally come to an happy ending: now a
clear WKT standard exists (ISO_IEC 13249 aka ISO SQL/MM), and
accordingly to the ISO standard the correct spelling should be e.g.:

2D XY:   LINESTRING
3D XYZ:  LINESTRING Z
2D XYM:  LINESTRING M
3D XYZM: LINESTRING ZM

I