{"type":"doc","content":[{"type":"heading","attrs":{"level":2},"content":[{"text":"Database Objects","type":"text"}]},{"type":"paragraph","content":[{"text":"This document is based on the following database tables within a PostgreSQL/PostGIS database.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"DROP TABLE IF EXISTS public.graffiti_p;\n\nCREATE TABLE public.graffiti_p (\n ID serial not null primary key,\n DESCRIPTION text,\n STATUS varchar(1) check (STATUS IN ('N','V','S','R')) default ('N'),\n FIELDVERIFIED varchar(1) check (FIELDVERIFIED in ('Y','N')),\n /* Who reported the graffiti */\n REPORTEDBY varchar(100),\n REPORTEDON timestamp,\n /* record / edit level metadata ie who created/updated record in database */\n CREATEDBY varchar(100),\n CREATEDON timestamp,\n MODIFIEDBY varchar(100),\n MODIFIEDON timestamp,\n /* Geometry restricted to single points in SRID 4326 */\n GEOM geometry(POINT,4326)\n);\n\nINSERT INTO public.graffiti_p (DESCRIPTION,STATUS,FIELDVERIFIED,REPORTEDBY,REPORTEDON,CREATEDBY,CREATEDON,GEOM)\nVALUES ('Banksy like mural',\n 'V',\n 'Y',\n 'Cohga',\n CURRENT_TIMESTAMP,\n 'Cohga',\n CURRENT_TIMESTAMP,\n ST_GeomFromEWKT('SRID=4326;POINT(144.953454336348 -37.8060850834926)'));\n\n-- ****************************************************************************\n\nDROP TABLE IF EXISTS public.graffiti_a;\n\nCREATE TABLE public.graffiti_a (\n ID serial not null primary key, \n DESCRIPTION text,\n STATUS varchar(1) check (STATUS IN ('N','V','S','R')) default ('N'),\n FIELDVERIFIED varchar(1) check (FIELDVERIFIED in ('Y','N')),\n /* Who reported the graffiti */\n REPORTEDBY varchar(100),\n REPORTEDON timestamp,\n /* record / edit level metadata ie who created/updated record in database */\n CREATEDBY varchar(100),\n CREATEDON timestamp,\n MODIFIEDBY varchar(100),\n MODIFIEDON timestamp,\n /* Geometry restricted to single polygons in SRID 4326 */\n GEOM geometry(POLYGON,4326)\n);\n\nINSERT INTO public.graffiti_a (DESCRIPTION,STATUS,FIELDVERIFIED,REPORTEDBY,REPORTEDON,CREATEDBY,CREATEDON,GEOM)\nVALUES ('Multiple snakes drawn on the road',\n 'V',\n 'Y',\n 'Cohga',\n CURRENT_TIMESTAMP,\n 'Cohga',\n CURRENT_TIMESTAMP,\n ST_GeomFromEWKT('SRID=4326;POLYGON((\n144.953454 -37.806085,\n144.953455 -37.806085,\n144.953455 -37.806086,\n144.953454 -37.806086,\n144.953454 -37.806085))'));\n\nGRANT ALL ON PUBLIC.graffiti_p to public;\nGRANT ALL ON PUBLIC.graffiti_a to public;\n\nSELECT * \n FROM public.geometry_columns \n WHERE table_name in ('graffiti_a','graffiti_p');","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":702,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":702,"alt":"geometry_columns_a_and_p.png","id":"9b8be815-2313-4cf3-9a08-a8c3c3fa9174","collection":"contentId-2698087082","type":"file","height":234}},{"type":"caption","content":[{"text":"Database Geometry Metadata Entries.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Requirements","type":"text"}]},{"type":"paragraph","content":[{"text":"If an entity is composed of multiple spatial tables, then it may be the case that the entity id may not be unique across all of the tables. If for example the entity is composed of more than one geometry type and the underlying spatial engine (e.g. ArcSDE, shapefiles) does not support different geometries in a single table, then the geometry would need to be spread over more than one table. In this situation, the entity id would have to be the same in all of the spatial tables, but it must at least be unique within each table. This is the only way that Weave can determine which row in the table that it needs to update as Weave determines which spatial table to update based on the geometry type stored in the spatial table.","type":"text"}]},{"type":"paragraph","content":[{"text":"The editing of multiple spatial tables is only fully supported when a different geometry type is stored in each table. That is, if an entity is linked to more than one spatial table then each spatial table should contain a different geometry type (point, line or polygon). This is so that Weave knows which table to write the appropriate geometries to. For example, if you were to have an entity linked to two spatial tables, both containing polygon geometry, then when an edit is submitted that contains a polygon, Weave would not know which table the geometry was supposed to be added to or updated in.","type":"text"}]},{"type":"paragraph","content":[{"text":"In the case of an entity represented by multiple spatial tables, the schemas for the tables should all be the same. This is a requirement because the same attribute values will be written to each table when an edit is submitted that contains different geometry types.","type":"text"}]},{"type":"paragraph","content":[{"text":"Spatial engines that do not differentiate between geometry types, (i.e. the spatial table stores \"geometry\" and not specifically points, lines or polygons) are not currently supported in multi-table configurations. This is because in these cases, Weave cannot determine which table to write the geometry to (they are supported in single table configurations). This may change in the future.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Multi-Table Configuration","type":"text"}]},{"type":"paragraph","content":[{"text":"Our example is based on the ","type":"text"},{"text":"graffiti_p ","type":"text","marks":[{"type":"strong"}]},{"text":"and ","type":"text"},{"text":"graffiti_a ","type":"text","marks":[{"type":"strong"}]},{"text":"tables (see above and ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://cohgawiki.atlassian.net/wiki/spaces/WUG/pages/2698084393"}},{"text":"). The ","type":"text"},{"text":"graffiti_p ","type":"text","marks":[{"type":"strong"}]},{"text":"table has a geometry column defined to hold single points and the ","type":"text"},{"text":"graffiti_a ","type":"text","marks":[{"type":"strong"}]},{"text":"table has a geometry column that is defined to hold single polygons. (A piece of graffiti could be at a specific point or could be spread over an area.)","type":"text"}]},{"type":"paragraph","content":[{"text":"The entity and mapper definitions for multi-table editing is as follows:","type":"text"}]},{"type":"codeBlock","content":[{"text":"\n\n\n\tse.postgresql\n\t\n\t\tm.graffiti\n\t\ttrue\n\t\tfalse\n\t\tgraffiti_p

\n\t\tgraffiti_a

\n\t\tid\n\t\n","type":"text"}]},{"type":"paragraph","content":[{"text":"A dedicated map engine is defined and added to the client XML. (The layer SLDs are defined ","type":"text"},{"text":"here","type":"text","marks":[{"type":"link","attrs":{"href":"https://cohgawiki.atlassian.net/wiki/spaces/WUG/pages/2698084418/Weave+Configuration#SLD"}}]},{"text":".)","type":"text"}]},{"type":"codeBlock","attrs":{"language":"xml"},"content":[{"text":"\n se.postgresql\n image/png32\n \n\t\t\n\t\t\n\t\n\tEPSG:4326\n ","type":"text"}]},{"type":"paragraph","content":[{"text":"This ","type":"text"},{"text":"me.multi","type":"text","marks":[{"type":"code"}]},{"text":" mapengine is added to the client as follows.","type":"text"}]},{"type":"codeBlock","content":[{"text":"\n ...\n \n \n \n \n \n ...\n","type":"text"}]},{"type":"paragraph","content":[{"text":"The new table of contents looks like this:","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1288,"id":"c5731827-116a-493c-8cbf-e5baf11dde89","collection":"contentId-2698087082","type":"file","height":791}},{"type":"caption","content":[{"text":"Multi Tables Displayed in Table of Contents.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"The following edit configuration allows for editing of the multiple table scenario. Note the ","type":"text"},{"text":"","type":"text","marks":[{"type":"code"}]},{"text":" definition and the definition for the ","type":"text"},{"text":"id","type":"text","marks":[{"type":"code"}]},{"text":" column (i.e., ","type":"text"},{"text":"auto()","type":"text","marks":[{"type":"code"}]},{"text":" - the value for the field is auto-generated by the database).","type":"text"}]},{"type":"codeBlock","content":[{"text":"\n\n\n\n\t\n\t\tm.graffiti\n\t\tGraffiti (multi)\n\t\ttrue\n\t\tReport graffiti (points or areas) for removal\n\t true\n\t true\n\t\ttrue\n\t\t\n\t\t\t \n\t\t\t \n\t\t\n\t\t\n\t\t\ttrue\n\t\t\tid\n\t\t\tauto()\n\t\t\n\t\t\n\t\t\t Description\n\t\t\t textarea\n\t\t\t description\n\t\t\n\t\t\n\t\t\t Status (List)\n\t\t\t listbox\n\t\t\t status\n\t\t\t N\n\t\t\t \n\t\t\t \n\t\t\t \n\t\t\t \n\t\t\n\t\t\n\t\t Field Verified\n\t\t checkbox\n\t\t fieldverified\n\t\t Y\n\t\t N\n\t\t\n\t\t\n\t\t\t Reporter\n\t\t\t list-box\n\t\t\t lu.staff\n\t\t\t true\n\t\t\t reportedby\n\t\t\t name\t\n\t\t\t name\n\t\t\n\t\n\n","type":"text"}]},{"type":"paragraph","content":[{"text":"Setting either of the graffiti tables as Active will cause the ","type":"text"},{"text":"Graffiti (multi)","type":"text","marks":[{"type":"code"}]},{"text":" Edit Configuration to become active. ","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":476,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":476,"id":"f6e824b6-8523-4d24-a13f-affb3a363f5d","collection":"contentId-2698087082","type":"file","height":404}},{"type":"caption","content":[{"text":"Set Graffiti Entity as Active via Either Point or Area Layer.","type":"text"}]}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"Examples of the creation of point and polygon graffiti objects are as follows:","type":"text"}]},{"type":"paragraph","content":[{"text":"Point:","type":"text","marks":[{"type":"strong"}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1289,"id":"f512cbab-cf51-4f3c-a59a-08484f28fcfb","collection":"contentId-2698087082","type":"file","height":783}},{"type":"caption","content":[{"text":"Point Graffiti Successfully Created.","type":"text"}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1774,"id":"77e27379-918a-44e1-abb5-e1c6366cfa70","collection":"contentId-2698087082","type":"file","height":377}},{"type":"caption","content":[{"text":"Database Showing Point Graffiti Record.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Polygon:","type":"text","marks":[{"type":"strong"}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1286,"id":"1b599080-4a7d-4137-a4c3-3937aca5d9d1","collection":"contentId-2698087082","type":"file","height":779}},{"type":"caption","content":[{"text":"Polygon Graffiti Successfully Created.","type":"text"}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1793,"id":"9a883ca1-9274-4b91-9031-b409d824b1df","collection":"contentId-2698087082","type":"file","height":416}},{"type":"caption","content":[{"text":"Database Showing Polygon Graffiti Record.","type":"text"}]}]},{"type":"paragraph"},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"efe692c2-d2f8-4d9c-a55a-cd0a26a23ec0"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"<< Previous","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/WUG/pages/2698086999"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","marks":[{"type":"alignment","attrs":{"align":"end"}}],"content":[{"text":"Next >>","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/WUG/pages/2698087141"}}]}]}]}]}]}],"version":1}

Browser not supported