table creation and mixed geometries

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

table creation and mixed geometries

Kralidis, Tom (EC)
Hi all: as per [1] we are looking into how GeoAlchemy2 [2] communicates with PostGIS when creating columns of type geometry.  Below are two variations of table creation/geometry column definition:

a./ create table testptz (pkey integer primary key, geom geometry);

b./ create table testptz (pkey integer primary key, geom geometry(geometry, 0));

The issue/context is being able to insert both POINT and POINTZ geometries into a given table.

GeoAlchemy runs the SQL in option b to create the table but runs into error when trying to insert as follows:

insert into testptz values (1, 'POINT(0 0 0)');
ERROR: Geometry has Z dimension but column does not

When running the insert command against option a, everything works.  Any idea/suggestions?

Thanks

..Tom

[1] https://groups.google.com/forum/#!topic/geoalchemy/LxC9y2JsXhg
[2] https://geoalchemy-2.readthedocs.io/en/latest/

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table creation and mixed geometries

Regina Obe-2
Tom,

a) geom geometry   means no restriction whatsoever.  No typmod is applied.
b) geometry typmod is short for any 2D geometry so it won't allow Zs.
c) If you want to allow only 3D geometries, then what you'd want is

geom geometryZ

create table testptz (pkey integer primary key, geom geometry(geometryZ));

There is no reason to include a srid constraint unless you plan to enforce it.  Though putting 0 in there is same as leaving it out.

Hope that helps,
Regina
http://postgis.us


-----Original Message-----
From: postgis-users [mailto:[hidden email]] On Behalf Of Kralidis, Tom (EC)
Sent: Friday, July 28, 2017 1:04 PM
To: [hidden email]
Subject: [postgis-users] table creation and mixed geometries

Hi all: as per [1] we are looking into how GeoAlchemy2 [2] communicates with PostGIS when creating columns of type geometry.  Below are two variations of table creation/geometry column definition:

a./ create table testptz (pkey integer primary key, geom geometry);

b./ create table testptz (pkey integer primary key, geom geometry(geometry, 0));

The issue/context is being able to insert both POINT and POINTZ geometries into a given table.

GeoAlchemy runs the SQL in option b to create the table but runs into error when trying to insert as follows:

insert into testptz values (1, 'POINT(0 0 0)');
ERROR: Geometry has Z dimension but column does not

When running the insert command against option a, everything works.  Any idea/suggestions?

Thanks

..Tom

[1] https://groups.google.com/forum/#!topic/geoalchemy/LxC9y2JsXhg
[2] https://geoalchemy-2.readthedocs.io/en/latest/

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table creation and mixed geometries

Sandro Santilli-3
On Fri, Jul 28, 2017 at 10:56:14PM -0400, Regina Obe wrote:

> a) geom geometry   means no restriction whatsoever.  No typmod is applied.
> b) geometry typmod is short for any 2D geometry so it won't allow Zs.

It keeps striking me that there's no way to have unconstrained number
of dimensions when using AddGeometryColumn (both typmod based or
constraint-based). We can have unconstrained spatial type and SRS but
no unconstrained ZM flags: isn't that unfair ?

--strk;
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: table creation and mixed geometries

Regina Obe-2

> On Fri, Jul 28, 2017 at 10:56:14PM -0400, Regina Obe wrote:

>> a) geom geometry   means no restriction whatsoever.  No typmod is applied.
>> b) geometry typmod is short for any 2D geometry so it won't allow Zs.

> It keeps striking me that there's no way to have unconstrained number of dimensions when using AddGeometryColumn (both typmod based or constraint-based). We can have unconstrained spatial type and
> SRS but no unconstrained ZM flags: isn't that unfair ?

> --strk;
Unfair?  I suppose.
You want to implement strk for 2.4.  I never bothered to check how much effort it would be to do this since it seemed like a rare request.   I think for constraint way it would not be too hard to add an overloaded that allows it.  Though I don't think that would help Tom's original gripe that he wanted to do it in Django and Django expects typmod.

I was also concerned if we could would it just cause misuse with people unintentionally mixing dimensionality.

Mixing dimensionally different geometries in same table always seemed like an unpleasant thing to do to me as then you get into questions like

What does ST_Union of 2D and 3D points mean especially since our ST_Union is 3dish.
What does ST_MakeLine do when you try to make a line between 2D and 3D points.

What kind of monster would ST_Collect make or would it throw an error since it's illegal to have a mixed dimensionality collection.

I suppose you could say the same for SRID, but mixing SRIDs was always a side effect of us not trying to stop it rather than the ability to not do it being there already.
When in doubt do nothing.  It is better to not lift a finger than to lift a finger in error is my motto :)

Thanks,
Regina




_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...