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/89f55a31-8502-4f5d-937a-ee2c39221f04n%40googlegroups.com.
