2017_06_12_ _3DCityDB_iTowns_adapter - VCityTeam/UD-SV GitHub Wiki

Attendees: VJA, EBO

• We need to adapt the database installation process as provided by Oslandia in order for the database "behind 3DCityDB" to be itwons ready.
• This will require adapting/extending Oslandia's building server code. In the github logic this means forking Oslandia/building-server.git.
• This fork should live at the organization level (of the VCity or RICT project). Since this is public we must create a new public organisation.
• Ask GGE whether this is the way to go and what should be the organization name ?
• GGE: since this is public stick with using MEPP-team for the time being.
• It seems that building-server can access the database through sql network queries (to be asserted). Thus the process goes:
  • Prepare context
    • (just once) fork into the organization (say myOrg) Oslandia's building server
    • (as many times as devel environments are needed) on the vm server (the host of the postgre database) by duplicating, at the postgre level, the citydb_demo database (that must remain stable) into citydb_dev or citydb_my-devel-name
  • working cycle
    • fork/clone the myOrg/building-server git repository within your favorite devel environment (desktop or team vm server)
    • patch your code
    • run the patched building-server against a myServer:citydb_dev database (over the net)
    • commit your successful patch

• The database structure (which is a sub-set of 3DCityDB structure) related to a building as store in our 3DCityDB database
• Retrieving the global extent:
  • We match all objects with Building in their classname

    select ST_extent(geometry) from surface_geometry where cityobject_id IN (
       select id from cityobject where objectclass_id IN (
          select id from objectclass where classname LIKE '%Building%'));
    

  • Note: in order to know the type of CityObject stored in the 3DCityDB database

    select classname from objectclass where id IN ( select objectclass_id from cityobject );
    

• Retrieving the srs from the base: select * from database_srs;

• GGE: CityGML provides bounding boxes. Running building-server.pys might thus be both a loss of time (deploying building-server.py) and of space (creating an additional table in the database). If one works on a 3DCityDB build database (that will naturally be fed with CityGML data and thus providing the bounding boxes), adapting a 3DCityDB database to Itowns might only require to change the flask adapter !
• VJA/EBO: we must ask JGA if this would be possible and how to do it.

Notes: in the following the concerned/required files you need to modify eventually boil down to database.py, server.py and building-server-processdb.py (you can drop tests...)

• Search for gid in all files of building-server
  • within those files replace any occurrence of gid within string literals ('gid' or "gid") with id
  • note: you can omit documentation strings, variable names, parameters of function definition and calls. Basically you only need to change what concerns SQL requests and aliases within answers.
• Do a similar substitution of "geom" to "geometry".

Running the building-server-processdb.py script fails with the error:

1879.545501000015
1111.2461350001395
Query time : 3.321798324584961
Quadtree creation total time : 8.177662134170532
Traceback (most recent call last):
  File "building-server-processdb.py", line 236, in <module>
    initDB(args.city, cityconf, args.score)
  File "building-server-processdb.py", line 107, in initDB
    Session.update_table(city, i, j[2], j[0])
  File "/home/vincent/Documents/VCity_dev/web/building-server.git/building_server/database.py", line 255, in update_table
    cls.db.cursor().execute(sql)
  File "/home/vincent/Documents/VCity_dev/web/building-server.git/venv/lib/python3.5/site-packages/psycopg2/extras.py", line 316, in execute
    return super(NamedTupleCursor, self).execute(query, vars)
psycopg2.DataError: value too long for type character varying(10)

JGA said (2017 June 19):

• this error message means that the type of the identifier of the tile within the base is too small. Thus at some points when trying to store a geometry for too numerous objects the identifier becomes too big.
• This is due to the fact that Building-server was conceived to deal with buildings. But for us geometries are triangles (thus many more objects). Thus the build quadtree will be much deeper and blow up at some point.
• Previously the original table went (building_id, geom) and building-server would both adds a trailing tile column (to obtain (building_id, geom, tile) which is an invasive handling of geometries) and also adds a new table (tile,Box3D)
• The solution for our adaptation:
  1. create a new table (or possibly a view) building_id, geometry. Note that this requires to be able to aggregate all the geometries of a considered building,
  2. create a new (building_id, tile) table. Note: this is less invasive than adding a trailing tile column to our existing table
  3. run building server that will now work as before by adding a new (tile,Box3D) table

Patch the code to allow bigger quadtree keys. Look for varchar(10) in database.py building-server-processdb.py and replace that code with varchar(300) (because 300 is "big enough": we did choose 300 for a good reason: ignorance).

In conf/building-server.git, change featurespertile to 2000 (again an arbitrary value). featurespertile is the number of geometries linked to each tile. One geometry in JGA's context corresponds to one building. In our context, it corresponds to one polygon. Thus, this modification allows to have more than 20 polygons per tile.

The rest API seems to be functional. Yet when placing a request on /GetCity with the ad-hoc city as argument, the response body is empty. The code seems to be in building_server.py/server.py in GetCity.run() but debugging (by pacthing code with debug print()) this code is not trivial since it gets invocated within the USWGI server context. And the log file configured within building-server.git/conf/building.uwsgi.yml only logs the request...

Lessons learned from JGA:

• The mistake for not obtaining logs after patching server.py code was that...you need to relaunch the server!
• Second mistake (not ours): the reference code has changed (developers should inform their users!). The reference code is now https://github.com/itowns/itowns2.git when previously it was https://github.com/Jeremy.Gaillard/itowns2.git.
• Warning (process change): this updated repository doesn't have the proper itowns2/Examples/planar.html that defines the ad-hoc 3d-tile geometry for Lyon. This is because they are no public data and hence such an example cannot be placed into the repository. A new version of Install.md documents the new installation process
• JGA warnings:
  • the geometries present the considered database is too finely grained (down to simple triangles). One can see this with e.g. an sql request of the form SELECT ST_astext(geometry) from surface_geometry limit 5;
  • One must thus aggregate those geometries into so called "multi polygon" geometries. They are two ways to do that at the postgis level:
  • either use st_multi that transforms a geometry into multi polygon for which a transformed request is of the form SELECT ST_astext(st_multi(geometry)) from surface_geometry limit 5;
  • or use st_collect that aggregates geometries into multi polygon.SELECT ST_astext(st_collect(geometry)) from surface_geometry limit 5;
  • note: st_union is too slow for our "on the fly" itowns strategy.

• schemaspy looked promising but couldn't make it work. Whatever arguments you provide for host, port, password it will generate something in ./schemapy/index.html instead of complaining about wrong authentication (or could it be that is a social engineering scam to obtain the access codes?)
• Note: Mysql-workbench only works with MySQL... When looking for tools to