Multiple building usages not displayed using postgREST API

[matteomandrile]

Hello, I am encountering some difficulties in setting up the connection between my PostgreSQL database (containing a cityGML model) and the web-map client. I am using The postgREST API interface to read building attributes from a DB view (vertical table). The SQL statement to create the view splits the building usage attribute value into multiple rows as per image below.

Before:

After:

Unfortunately, when I use the link to the DB view to display the attributes in the web-map client, it only displays one single usage.

How can I display all the usages as in the db view?

Also, do you know if it is possible to retrieve the corresponding text description from the SIG3D code list that I used in the usage_codespace field?

Thank you for your support!

[Son-HNguyen]

To your first question: In a vertical table, the first two columns build a primary key, which means that for each row gmlid combined with attribute must be unique. However, in your table, you have 4 rows with the same values lj_building.7166 and usage. As a result, the web client only fetches one of those, which explains what you saw in the info box. A solution is e.g. to rename the attribute usage or combine all attribute values of usage in one string, for example by replacing --/\-- with ` or, ` etc.

To your second question: I guess you could create something like a look-up table or a dictionary or even the brute-force if-else or switch commands in your SQL query or in the PostgreSQL database itself to assign the decriptions to each corresponding usage name.

[matteomandrile]

Thank you @Son-HNguyen! Following your advice, I renamed the attribute usage adding a progressive number that denotes the "order" by which each usage appear in the table. This way I the query shows dynamically multiple rows each with one usage.

I leave here the code snippet in case someone has the same necessity.

SELECT cityobject.gmlid,
    'usage '::text || s.nr::text AS attribute,
    codelist_building_usage.description AS value,
   FROM cityobject,
    building,
    LATERAL unnest(string_to_array(building.usage::text, '--/\--'::text)) WITH ORDINALITY s(split_usages, nr)
     JOIN codelist_building_usage ON s.split_usages = codelist_building_usage.name::text
WHERE cityobject.id = building.id

For the second question, I created a look-up table with name and description for each SIG3D building usage. I then used the textual description (codelist_building_usage.description AS value) in the DB view instead of the numeric code.

I have one more question. I am following the same approach to display multiple addresses, meaning that I am adding a number after the "address" field, as per the image above. Even though the approach worked fine, now when I click the building it takes longer to display the results (30-40 seconds against the previous 2-5 seconds). I believe that the cause could be the inefficiency of my SQL statement, particularly the row_number() OVER (PARTITION BY...):

SELECT cityobject.gmlid,
    concat('address '::text, row_number() OVER (PARTITION BY address_to_building.building_id)) AS attribute,
    concat(address.street::character varying(255), ' ', address.house_number::character varying(255), ', ', address.zip_code::character varying(255), ', ', address.city::character varying(255), ', ', address.country::character varying(255)) AS value,
    0 AS order_vba
   FROM cityobject,
    building
     JOIN address_to_building ON building.id = address_to_building.building_id
     JOIN address ON address.id = address_to_building.address_id
 WHERE cityobject.id = building.id

could you suggest me a more efficient way to achieve the same result?

[Son-HNguyen]

Hi Matteo, sorry for the late reply. The loading speed can be affected by many factors. But first, could you take only the query URL, which is something similar to this: http://<server_ip>:<port>/<your_view>?gmlid=eq.<gmlid> and paste in the address bar of the browser and see how long it takes until you see the results? If it's slow here then it could be due to slow internet connection or on the database side. If it's fast here but slow in the web client, then it could be due to how the web client parses the JSON response from the database. Please let me know if this is the case.

[matteomandrile]

Doing the above the browser returns the results in 10s on average. I also ran a speed test to test my internet connection:

Still, before the SQL Statement to retrieve the addresses on multiple rows, the results were retrieved almost instantaneously from the web client! Also, before the "address" I was doing a LATERAL unnest(string_to_array(building.usage::text, '--/\--'::text)) to retrieve the useges on multiple lines, and this did not caused any delay in the response. I am not 100% sure but I believe the row_number() OVER (PARTITION BY...) is expensive in terms of computation.

What do you think?

[Son-HNguyen]

How many rows do your tables cityobject, building, address and address_to_building have? Or how long does it take to execute the SQL query that contains row_number() OVER (PARTITION BY...)? You can measure this in pgAdmin. I actually find your SQL query quite good already. Alternatively, you could concatenate all addresses of each building in one string using a fixed delimiter (such as those used in usages) and then use the same approach LATERAL unnest(string_to_array(building.usage::text, '--/\--'::text)) to split them again. I'm not sure if it improves the performance in your use case but it might be worth a look at.