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/fc7a6c96-4c6c-4542-94e7-e0a693436fc2n%40googlegroups.com.
