[postgis-users] Create constrain between Province and town tables

[Francois Hugues]

 Hello,

I'm not sure to understand what you mean by creating a constraint
between two table but I may suggest you to use update statement like
this (I assume you want to update the code_prov column thought i don't
understand why it is character varying type when your province gid is of
serial type) :

UPDATE sigfre_cub_Adm_munic
SET cod_prov = prov_gid::varchar
	FROM (SELECT b.gid AS prov_gid, a.gid town_gid
		FROM sigfre_cub_Adm_munic AS a, sigfre_cub_Adm_Prov AS b
		WHERE ST_Contains(a.the_geom, b.the_geom)
		) AS foo
WHERE gid = town_gid

I don't think the use of st_intersects will give you good results since
towns and provinces may share boundaries.

Hope this helps

Hugues.



-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of
islanis at infomed.sld.cu
Sent: Friday, February 15, 2013 7:36 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Create constrain between Province and town
tables

Hi people, i need  to create an algoritm, to alter a column of the town
table, cause they are in the form shown below, but i need a establish a
relation constrain between Province and town tables because they are
polygons and all towns are in some province, and in the actual form they
are not in a relation, , i want to make that using the content or
intersect functions of postgis, but i dont have idea how to do that,
help me please, thanks to all


//////this is the province table///////////////

CREATE TABLE "sigfre_cub_Adm_Prov"
(
   gid serial NOT NULL,
   tipoelemen character varying(10),
   municipio character varying(40),
   dempromkw double precision,
   potinsmw double precision,
   radsolprkw double precision,
   nohab double precision,
   noviv double precision,
   temedimaxc double precision,
   temedminc double precision,
   velvieprms numeric,
   humrel double precision,
   nubosidad double precision,
   extenterri double precision,
   precip_med integer,
   denspob numeric,
   the_geom geometry,
   CONSTRAINT "sigfre_cub_Adm_Prov_pkey" PRIMARY KEY (gid),
   CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
   CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1))
)
WITH (
   OIDS=FALSE
);
ALTER TABLE "sigfre_cub_Adm_Prov" OWNER TO postgres;

///////////this is the town table///////////////////////////////////
CREATE TABLE "sigfre_cub_Adm_munic"
(
   gid serial NOT NULL,
   nombre character varying(30),
   cabeceramu character varying(30),
   provincia character varying(30),
   km2 numeric,
   canthabt character varying(50),
   cant_casas integer,
   cod_munic character varying(9),
   cod_prov character varying(9),
   the_geom geometry,
   CONSTRAINT "sigfre_cub_Adm_munic_pkey" PRIMARY KEY (gid),
   CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
   CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS NULL),
   CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = (-1))
)
WITH (
   OIDS=FALSE
);
ALTER TABLE "sigfre_cub_Adm_munic" OWNER TO postgres;
////////////////////////////////////////////////////////////////////////
/////



----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.


--

Este mensaje le ha llegado mediante el servicio de correo electronico
que ofrece Infomed para respaldar el cumplimiento de las misiones del
Sistema Nacional de Salud. La persona que envia este correo asume el
compromiso de usar el servicio a tales fines y cumplir con las
regulaciones establecidas

Infomed: http://www.sld.cu/
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users