On 14 June 2010 06:09, Rui Daniro <span dir="ltr"><<a href="mailto:ruidaniro@hotmail.co.uk" target="_blank">ruidaniro@hotmail.co.uk</a>></span> wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0.8ex;border-left-width:1px;border-left-color:rgb(204, 204, 204);border-left-style:solid;padding-left:1ex">
<br>Hi,<br>Thanks in advance for your time.<br>I am a begginer with Postgis and I am trying to create a table with multiple<br>geometries using Postgis,but I am struggling to find the workaround, can you<br>give me some hints on how to do this or guide me somewhere I can find any<br>
help?<br></blockquote><div><br></div><div>Hi Rui,</div><div><br></div><div>I just had to use the workaround in my office for AutoCAD Map3D 2011, so I'll show you my example solution. However, this is a read-only solution:</div>
<div><br></div><div><div><font class="Apple-style-span" face="'courier new', monospace">CREATE TABLE public.structure</font></div><div><font class="Apple-style-span" face="'courier new', monospace">(</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> gid serial NOT NULL,</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> unref geometry,</font></div><div>
<font class="Apple-style-span" face="'courier new', monospace"> georef geometry,</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> name1 character varying(100),</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> name2 character varying(100),</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT structure_pkey PRIMARY KEY (gid),</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_dims_georef CHECK (ndims(georef) = 2),</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_dims_unref CHECK (ndims(unref) = 2),</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_geotype_georef CHECK (geometrytype(georef) = 'MULTIPOLYGON'::text OR georef IS NULL),</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_geotype_unref CHECK (geometrytype(unref) = 'MULTIPOLYGON'::text OR unref IS NULL),</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_srid_georef CHECK (srid(georef) = 4326),</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_srid_unref CHECK (srid(unref) = (-1)),</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_valid_georef CHECK (st_isvalid(georef)),</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> CONSTRAINT enforce_valid_unref CHECK (st_isvalid(unref))</font></div>
<div><span class="Apple-style-span" style="font-family: 'courier new', monospace; ">);</span></div><div><font class="Apple-style-span" face="'courier new', monospace"><br></font></div><div><font class="Apple-style-span" face="'courier new', monospace">CREATE INDEX structure_georef_idx</font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> ON public.structure </font><font class="Apple-style-span" face="'courier new', monospace">USING gist</font><span class="Apple-style-span" style="font-family: 'courier new', monospace; ">(georef);</span></div>
<div><font class="Apple-style-span" face="'courier new', monospace"><br></font></div><div><font class="Apple-style-span" face="'courier new', monospace">CREATE OR REPLACE VIEW public.structure_georef AS </font></div>
<div><font class="Apple-style-span" face="'courier new', monospace"> SELECT structure.gid, structure.georef, structure.name1, structure.name2</font></div><div><font class="Apple-style-span" face="'courier new', monospace"> FROM public.structure;</font></div>
</div><div><font class="Apple-style-span" face="'courier new', monospace"><br></font></div><div><font class="Apple-style-span" face="'courier new', monospace">-- Update metadata:</font></div><div><font class="Apple-style-span" face="'courier new', monospace"><div>
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")</div><div>VALUES ('','public','structure','unref','2','-1','MULTIPOLYGON'),</div>
<div>('','public','structure','georef','2','4326','MULTIPOLYGON'),</div><div>('','public','structure_georef','georef','2','4326','MULTIPOLYGON');</div>
<div><br></div></font></div><div><br></div><div>Although "georef" is the only column with a spatial index, it is not selected by AutoCAD, rather it picks the un-indexed "unref" column, which appears before "georef". So, that's why I made the view "structure_georef" to explicitly select that column. Load up this in AutoCAD to view the data. Brent, is this a bug? Why isn't "georef" picked?</div>
<div><br></div><div>I have to test INSERT/UPDATE/DELETE rules on the view to see if I can make the view act more like a table, because this example makes read-only view. See <a href="http://www.postgresql.org/docs/8.4/interactive/rules-update.html">http://www.postgresql.org/docs/8.4/interactive/rules-update.html</a></div>
<div><br></div><div>Sorry it isn't for "the beginner," but hope it helps.</div><div><br></div><div>-Mike</div></div>