Mixing
PostgreSQL - only allow points to be added to the table (no polygons or lines)
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I want to know if there is a way to only allow certain Geometry types to be added to a table in PostgreSQL.
I have a tool that currently allows points, lines and polygons but would like to restrict it so that only points are allowed but am not sure how to do this in pgAdmin.
postgis postgresql spatial-database database
edited Aug 16 at 13:05
Vince
13.9k32444
asked Aug 16 at 10:18
Hutch
536
add a comment |Â
up vote
1
down vote
favorite
I want to know if there is a way to only allow certain Geometry types to be added to a table in PostgreSQL.
I have a tool that currently allows points, lines and polygons but would like to restrict it so that only points are allowed but am not sure how to do this in pgAdmin.
postgis postgresql spatial-database database
edited Aug 16 at 13:05
Vince
13.9k32444
asked Aug 16 at 10:18
Hutch
536
1
Suggest commenters review: meta.stackexchange.com/conduct
– Jay Cummins
Aug 16 at 10:51
You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc)
– JGH
Aug 16 at 12:03
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I want to know if there is a way to only allow certain Geometry types to be added to a table in PostgreSQL.
I have a tool that currently allows points, lines and polygons but would like to restrict it so that only points are allowed but am not sure how to do this in pgAdmin.
postgis postgresql spatial-database database
edited Aug 16 at 13:05
Vince
13.9k32444
asked Aug 16 at 10:18
Hutch
536
I want to know if there is a way to only allow certain Geometry types to be added to a table in PostgreSQL.
I have a tool that currently allows points, lines and polygons but would like to restrict it so that only points are allowed but am not sure how to do this in pgAdmin.
postgis postgresql spatial-database database
edited Aug 16 at 13:05
Vince
13.9k32444
asked Aug 16 at 10:18
Hutch
536
edited Aug 16 at 13:05
Vince
13.9k32444
edited Aug 16 at 13:05
Vince
13.9k32444
edited Aug 16 at 13:05
Vince
13.9k32444
13.9k32444
asked Aug 16 at 10:18
Hutch
536
asked Aug 16 at 10:18
Hutch
536
asked Aug 16 at 10:18
Hutch
536
536
1
Suggest commenters review: meta.stackexchange.com/conduct
– Jay Cummins
Aug 16 at 10:51
You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc)
– JGH
Aug 16 at 12:03
add a comment |Â
1
Suggest commenters review: meta.stackexchange.com/conduct
– Jay Cummins
Aug 16 at 10:51
You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc)
– JGH
Aug 16 at 12:03
1
1
Suggest commenters review: meta.stackexchange.com/conduct
– Jay Cummins
Aug 16 at 10:51
Suggest commenters review: meta.stackexchange.com/conduct
– Jay Cummins
Aug 16 at 10:51
You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc)
– JGH
Aug 16 at 12:03
You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc)
– JGH
Aug 16 at 12:03
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
4
down vote
accepted
You can change the underlying column type. It is likely a geometry column, that you can change to a geometry(point) column.
Let's note that the type change will work only if there is only point in the column, so you would have to clean your current data first:
Create table public.a(
id integer,
geom geometry
);
insert into public.a values (1, st_geomFromText('point(1 1)',4326));
insert into public.a values (2, st_geomFromText('LINESTRING(2 3, 3 4)',4326));
Alter table public.a alter column geom type geometry(point,4326);
--ERROR: Geometry type (LineString) does not match column type (Point)
DELETE from public.a where id =2;
Alter table public.a alter column geom type geometry(point,4326);
--OK
Alternatively, you could create a trigger on before insert/update that ensure the new geometry is indeed a point and throws an error otherwise.
answered Aug 16 at 12:00
JGH
10.2k2733
add a comment |Â
up vote
0
down vote
An additional solution to my question is to create a geom_point geometry column and then just use this column to add point data:
CREATE TABLE public.test1
(
gid integer NOT NULL DEFAULT nextval('test1_gid_seq'::regclass),
description text COLLATE pg_catalog."default",
item_id integer,
item_class text COLLATE pg_catalog."default",
origin_x double precision,
origin_y double precision,
origin_z double precision,
geom_point geometry(Point,27700),
CONSTRAINT test1_pkey PRIMARY KEY (gid)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.test1
OWNER to tommy;
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
And if you are updating an existing table you will need to create the geom_point column and copy the existing geometry into the new column.
ALTER TABLE public.test1
ADD COLUMN geom_point geometry(POINT, 27700);
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
UPDATE test1 SET geom_point = geometry;
answered Aug 17 at 12:49
Hutch
536
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
accepted
You can change the underlying column type. It is likely a geometry column, that you can change to a geometry(point) column.
Let's note that the type change will work only if there is only point in the column, so you would have to clean your current data first:
Create table public.a(
id integer,
geom geometry
);
insert into public.a values (1, st_geomFromText('point(1 1)',4326));
insert into public.a values (2, st_geomFromText('LINESTRING(2 3, 3 4)',4326));
Alter table public.a alter column geom type geometry(point,4326);
--ERROR: Geometry type (LineString) does not match column type (Point)
DELETE from public.a where id =2;
Alter table public.a alter column geom type geometry(point,4326);
--OK
Alternatively, you could create a trigger on before insert/update that ensure the new geometry is indeed a point and throws an error otherwise.
answered Aug 16 at 12:00
JGH
10.2k2733
add a comment |Â
up vote
4
down vote
accepted
You can change the underlying column type. It is likely a geometry column, that you can change to a geometry(point) column.
Let's note that the type change will work only if there is only point in the column, so you would have to clean your current data first:
Create table public.a(
id integer,
geom geometry
);
insert into public.a values (1, st_geomFromText('point(1 1)',4326));
insert into public.a values (2, st_geomFromText('LINESTRING(2 3, 3 4)',4326));
Alter table public.a alter column geom type geometry(point,4326);
--ERROR: Geometry type (LineString) does not match column type (Point)
DELETE from public.a where id =2;
Alter table public.a alter column geom type geometry(point,4326);
--OK
Alternatively, you could create a trigger on before insert/update that ensure the new geometry is indeed a point and throws an error otherwise.
answered Aug 16 at 12:00
JGH
10.2k2733
add a comment |Â
up vote
4
down vote
accepted
up vote
4
down vote
accepted
You can change the underlying column type. It is likely a geometry column, that you can change to a geometry(point) column.
Let's note that the type change will work only if there is only point in the column, so you would have to clean your current data first:
Create table public.a(
id integer,
geom geometry
);
insert into public.a values (1, st_geomFromText('point(1 1)',4326));
insert into public.a values (2, st_geomFromText('LINESTRING(2 3, 3 4)',4326));
Alter table public.a alter column geom type geometry(point,4326);
--ERROR: Geometry type (LineString) does not match column type (Point)
DELETE from public.a where id =2;
Alter table public.a alter column geom type geometry(point,4326);
--OK
Alternatively, you could create a trigger on before insert/update that ensure the new geometry is indeed a point and throws an error otherwise.
answered Aug 16 at 12:00
JGH
10.2k2733
You can change the underlying column type. It is likely a geometry column, that you can change to a geometry(point) column.
Let's note that the type change will work only if there is only point in the column, so you would have to clean your current data first:
Create table public.a(
id integer,
geom geometry
);
insert into public.a values (1, st_geomFromText('point(1 1)',4326));
insert into public.a values (2, st_geomFromText('LINESTRING(2 3, 3 4)',4326));
Alter table public.a alter column geom type geometry(point,4326);
--ERROR: Geometry type (LineString) does not match column type (Point)
DELETE from public.a where id =2;
Alter table public.a alter column geom type geometry(point,4326);
--OK
Alternatively, you could create a trigger on before insert/update that ensure the new geometry is indeed a point and throws an error otherwise.
answered Aug 16 at 12:00
JGH
10.2k2733
answered Aug 16 at 12:00
JGH
10.2k2733
answered Aug 16 at 12:00
JGH
10.2k2733
answered Aug 16 at 12:00
JGH
10.2k2733
10.2k2733
add a comment |Â
add a comment |Â
up vote
0
down vote
An additional solution to my question is to create a geom_point geometry column and then just use this column to add point data:
CREATE TABLE public.test1
(
gid integer NOT NULL DEFAULT nextval('test1_gid_seq'::regclass),
description text COLLATE pg_catalog."default",
item_id integer,
item_class text COLLATE pg_catalog."default",
origin_x double precision,
origin_y double precision,
origin_z double precision,
geom_point geometry(Point,27700),
CONSTRAINT test1_pkey PRIMARY KEY (gid)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.test1
OWNER to tommy;
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
And if you are updating an existing table you will need to create the geom_point column and copy the existing geometry into the new column.
ALTER TABLE public.test1
ADD COLUMN geom_point geometry(POINT, 27700);
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
UPDATE test1 SET geom_point = geometry;
answered Aug 17 at 12:49
Hutch
536
add a comment |Â
up vote
0
down vote
An additional solution to my question is to create a geom_point geometry column and then just use this column to add point data:
CREATE TABLE public.test1
(
gid integer NOT NULL DEFAULT nextval('test1_gid_seq'::regclass),
description text COLLATE pg_catalog."default",
item_id integer,
item_class text COLLATE pg_catalog."default",
origin_x double precision,
origin_y double precision,
origin_z double precision,
geom_point geometry(Point,27700),
CONSTRAINT test1_pkey PRIMARY KEY (gid)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.test1
OWNER to tommy;
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
And if you are updating an existing table you will need to create the geom_point column and copy the existing geometry into the new column.
ALTER TABLE public.test1
ADD COLUMN geom_point geometry(POINT, 27700);
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
UPDATE test1 SET geom_point = geometry;
answered Aug 17 at 12:49
Hutch
536
add a comment |Â
up vote
0
down vote
up vote
0
down vote
An additional solution to my question is to create a geom_point geometry column and then just use this column to add point data:
CREATE TABLE public.test1
(
gid integer NOT NULL DEFAULT nextval('test1_gid_seq'::regclass),
description text COLLATE pg_catalog."default",
item_id integer,
item_class text COLLATE pg_catalog."default",
origin_x double precision,
origin_y double precision,
origin_z double precision,
geom_point geometry(Point,27700),
CONSTRAINT test1_pkey PRIMARY KEY (gid)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.test1
OWNER to tommy;
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
And if you are updating an existing table you will need to create the geom_point column and copy the existing geometry into the new column.
ALTER TABLE public.test1
ADD COLUMN geom_point geometry(POINT, 27700);
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
UPDATE test1 SET geom_point = geometry;
answered Aug 17 at 12:49
Hutch
536
An additional solution to my question is to create a geom_point geometry column and then just use this column to add point data:
CREATE TABLE public.test1
(
gid integer NOT NULL DEFAULT nextval('test1_gid_seq'::regclass),
description text COLLATE pg_catalog."default",
item_id integer,
item_class text COLLATE pg_catalog."default",
origin_x double precision,
origin_y double precision,
origin_z double precision,
geom_point geometry(Point,27700),
CONSTRAINT test1_pkey PRIMARY KEY (gid)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.test1
OWNER to tommy;
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
And if you are updating an existing table you will need to create the geom_point column and copy the existing geometry into the new column.
ALTER TABLE public.test1
ADD COLUMN geom_point geometry(POINT, 27700);
CREATE INDEX test1_geom_point_sidx
ON public.test1 USING gist
(geom_point)
TABLESPACE pg_default;
UPDATE test1 SET geom_point = geometry;
answered Aug 17 at 12:49
Hutch
536
answered Aug 17 at 12:49
Hutch
536
answered Aug 17 at 12:49
Hutch
536
answered Aug 17 at 12:49
Hutch
536
536
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f292973%2fpostgresql-only-allow-points-to-be-added-to-the-table-no-polygons-or-lines%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
Suggest commenters review: meta.stackexchange.com/conduct
– Jay Cummins
Aug 16 at 10:51
You should fix your tool first. Otherwise if the DB doesn't comply with the tool expectations you will have errors that might not be handled properly by the tool (crash, erratic behavior etc)
– JGH
Aug 16 at 12:03