You were right, that has worked. Thanks On Tuesday, 13 October 2020 at 09:27:06 UTC+1 [email protected] 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 [email protected] > 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 >> [email protected] 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 [email protected] >>> 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 [email protected]. To unsubscribe, send email to [email protected]. 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/archesproject/6cc37b20-5b8e-4495-9a81-c21f57b73d97n%40googlegroups.com.
