[fdo-internals] Re: Multiple geometries per table

[Mike Toews]

On 14 June 2010 06:09, Rui Daniro <ruidaniro at hotmail.co.uk> wrote:

>
> Hi,
> Thanks in advance for your time.
> I am a begginer with Postgis and I am trying to create a table with
> multiple
> geometries using Postgis,but I am struggling to find the workaround, can
> you
> give me some hints on how to do this or guide me somewhere I can find any
> help?
>

Hi Rui,

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:

CREATE TABLE public.structure
(
  gid serial NOT NULL,
  unref geometry,
  georef geometry,
  name1 character varying(100),
  name2 character varying(100),
  CONSTRAINT structure_pkey PRIMARY KEY (gid),
  CONSTRAINT enforce_dims_georef CHECK (ndims(georef) = 2),
  CONSTRAINT enforce_dims_unref CHECK (ndims(unref) = 2),
  CONSTRAINT enforce_geotype_georef CHECK (geometrytype(georef) =
'MULTIPOLYGON'::text OR georef IS NULL),
  CONSTRAINT enforce_geotype_unref CHECK (geometrytype(unref) =
'MULTIPOLYGON'::text OR unref IS NULL),
  CONSTRAINT enforce_srid_georef CHECK (srid(georef) = 4326),
  CONSTRAINT enforce_srid_unref CHECK (srid(unref) = (-1)),
  CONSTRAINT enforce_valid_georef CHECK (st_isvalid(georef)),
  CONSTRAINT enforce_valid_unref CHECK (st_isvalid(unref))
);

CREATE INDEX structure_georef_idx
  ON public.structure USING gist(georef);

CREATE OR REPLACE VIEW public.structure_georef AS
 SELECT structure.gid, structure.georef, structure.name1, structure.name2
   FROM public.structure;

-- Update metadata:
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid, "type")
VALUES ('','public','structure','unref','2','-1','MULTIPOLYGON'),
('','public','structure','georef','2','4326','MULTIPOLYGON'),
('','public','structure_georef','georef','2','4326','MULTIPOLYGON');


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?

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 http://www.postgresql.org/docs/8.4/interactive/rules-update.html

Sorry it isn't for "the beginner," but hope it helps.

-Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/fdo-internals/attachments/20100615/47786b36/attachment.html