You were right, that has worked. Thanks On Tuesday, 13 October 2020 at 09:27:06 UTC+1 andrew...@historicengland.org.uk wrote:
> Gabriel, > > It looks like *function_certainty *and *place_function *are on the same > tile/card (Heritage Place Function Assignment). This means that you only > need to join to that tile once. > > > SELECT row_number() OVER () AS gid, > tv1.tileid, > tv1.resourceinstanceid, > tv1.nodeid, > tv1.geom, > name_tile.tiledata ->> '34cfe9dd-c2c0-11ea-9026-02e7594ce0a0'::text AS > name, > btrim(btrim(btrim(country_tile.tiledata ->> > '34cfea43-c2c0-11ea-9026-02e7594ce0a0'::text, '['::text), ']'::text), > '"'::text) AS country, > eamenaid_tile.tiledata ->> > '34cfe992-c2c0-11ea-9026-02e7594ce0a0'::text AS eamena_id, > btrim(btrim(btrim(*func_assignment_tile*.tiledata ->> > '34cfea4a-c2c0-11ea-9026-02e7594ce0a0'::text, '['::text), ']'::text), > '"'::text) AS place_function, > jsonb_array_elements(resourceid_tile.tiledata -> > '34cfea8a-c2c0-11ea-9026-02e7594ce0a0'::text) ->> 'resourceId'::text AS > resourceid, > *func_assignment_tile*.tiledata ->> > '34cfea7d-c2c0-11ea-9026-02e7594ce0a0'::text AS function_certainty > FROM test_view_1 tv1 > LEFT JOIN tiles *func_assignment_tile *ON tv1.resourceinstanceid = > func_assignment_tile.resourceinstanceid -- < JOIN ONCE - > replaces funxcertainty_tile and purpose_tile > LEFT JOIN tiles resourceid_tile ON tv1.resourceinstanceid = > resourceid_tile.resourceinstanceid > LEFT JOIN tiles eamenaid_tile ON tv1.resourceinstanceid = > eamenaid_tile.resourceinstanceid > LEFT JOIN tiles country_tile ON tv1.resourceinstanceid = > country_tile.resourceinstanceid > LEFT JOIN tiles name_tile ON tv1.resourceinstanceid = > name_tile.resourceinstanceid > WHERE (country_tile.tiledata ->> > '34cfea43-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL > AND (name_tile.tiledata ->> > '34cfe9dd-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL > AND (eamenaid_tile.tiledata ->> > '34cfe992-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL > AND (*func_assignment_tile*.tiledata ->> > '34cfea4a-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL -- < > place_function > AND (*func_assignment_tile*.tiledata -> > '34cfea7d-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL; -- < > function_certainty > > I've not been able to try this as I don't have your model. I've only been > able to go on the picture. > > Thanks > Andy > > On Tuesday, October 13, 2020 at 8:46:48 AM UTC+1 gabriel.c...@gmail.com > wrote: > >> Thanks for your answer, >> >> this is the SQL to generate first view which answer the UUIDS: >> >> SELECT row_number() OVER () AS gid, >> tv1.tileid, >> tv1.resourceinstanceid, >> tv1.nodeid, >> tv1.geom, >> name_tile.tiledata ->> '34cfe9dd-c2c0-11ea-9026-02e7594ce0a0'::text >> AS name, >> btrim(btrim(btrim(country_tile.tiledata ->> >> '34cfea43-c2c0-11ea-9026-02e7594ce0a0'::text, '['::text), ']'::text), >> '"'::text) AS country, >> eamenaid_tile.tiledata ->> >> '34cfe992-c2c0-11ea-9026-02e7594ce0a0'::text AS eamena_id, >> btrim(btrim(btrim(purpose_tile.tiledata ->> >> '34cfea4a-c2c0-11ea-9026-02e7594ce0a0'::text, '['::text), ']'::text), >> '"'::text) AS place_function, >> jsonb_array_elements(resourceid_tile.tiledata -> >> '34cfea8a-c2c0-11ea-9026-02e7594ce0a0'::text) ->> 'resourceId'::text AS >> resourceid, >> funxcertainty_tile.tiledata ->> >> '34cfea7d-c2c0-11ea-9026-02e7594ce0a0'::text AS function_certainty >> FROM test_view_1 tv1 >> LEFT JOIN tiles funxcertainty_tile ON tv1.resourceinstanceid = >> funxcertainty_tile.resourceinstanceid >> LEFT JOIN tiles resourceid_tile ON tv1.resourceinstanceid = >> resourceid_tile.resourceinstanceid >> LEFT JOIN tiles eamenaid_tile ON tv1.resourceinstanceid = >> eamenaid_tile.resourceinstanceid >> LEFT JOIN tiles country_tile ON tv1.resourceinstanceid = >> country_tile.resourceinstanceid >> LEFT JOIN tiles name_tile ON tv1.resourceinstanceid = >> name_tile.resourceinstanceid >> LEFT JOIN tiles purpose_tile ON tv1.resourceinstanceid = >> purpose_tile.resourceinstanceid >> WHERE (country_tile.tiledata ->> >> '34cfea43-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL >> AND (name_tile.tiledata ->> >> '34cfe9dd-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL >> AND (eamenaid_tile.tiledata ->> >> '34cfe992-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL >> AND (purpose_tile.tiledata ->> >> '34cfea4a-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL >> AND (funxcertainty_tile.tiledata -> >> '34cfea7d-c2c0-11ea-9026-02e7594ce0a0'::text) IS NOT NULL; >> >> >> >> And thsi one to display the values: >> SELECT row_number() OVER () AS gid, >> tv2.geom, >> tv2.name, >> country.value AS country, >> tv2.eamena_id, >> purpose.value AS place_function, >> funxcertainty.value AS function_certainty >> FROM test_view_2 tv2 >> LEFT JOIN "values" funxcertainty ON tv2.function_certainty = >> funxcertainty.valueid::text >> LEFT JOIN "values" country ON tv2.country = country.valueid::text >> LEFT JOIN "values" purpose ON tv2.place_function = >> purpose.valueid::text; >> >> >> Thanks in advance for any tip and help provided. >> >> Best regards, >> >> >> >> >> On Monday, 12 October 2020 at 11:05:57 UTC+1 >> andrew...@historicengland.org.uk wrote: >> >>> Post your SQL here and we can take a look. I suspect that the way you >>> are joining things means you are creating view containing all variations >>> across tiles rather than per tile. >>> >>> On Friday, October 9, 2020 at 10:54:16 AM UTC+1 gabriel.c...@gmail.com >>> wrote: >>> >>>> Hello, >>>> >>>> I have found this issue when exporting form arches to postgresql and >>>> creating views. >>>> >>>> I have created a feature and one of the fields has multiple >>>> characteristic: >>>> >>>> So at e the same time can be different utilities and the certainty can >>>> vary (defensive - High, domestic - low and fishing - negligible) as >>>> example. >>>> >>>> [image: Screenshot from 2020-10-09 10-08-05.png] >>>> >>>> >>>> but what I get when creating the view and running the queries is that >>>> create a domestic low high and negligible, a fortification low high and >>>> negligible and a hunting high, low and negligible for the same feature: >>>> >>>> [image: Screenshot from 2020-10-09 10-10-03.png] >>>> >>>> Do someone know why is this happening and how can be resolved this >>>> issue? >>>> >>>> Thank you very much in advance >>>> >>>> >>>> >>>> >>>> -- -- To post, send email to archesproject@googlegroups.com. To unsubscribe, send email to archesproject+unsubscr...@googlegroups.com. For more information, visit https://groups.google.com/d/forum/archesproject?hl=en --- You received this message because you are subscribed to the Google Groups "Arches Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to archesproject+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/6cc37b20-5b8e-4495-9a81-c21f57b73d97n%40googlegroups.com.
