[SoC] [PostGIS] GSoC 2018 Final Report - Implement Geospatial Data Viewer in pgAdmin4 for PostGIS

[Xuri Gong]

Hi everyone,

I am Xuri Gong. This is the final report
<https://trac.osgeo.org/postgis/wiki/GeospatialDataViewerInPgAdmin4ForPostGIS_FinalReport>
for my GSoC project.

*Final Report: Implement Geospatial Data Viewer in pgAdmin4 for PostGIS*

   - *Title: *Implement Geospatial Data Viewer in pgAdmin4 for PostGIS
   - *Mentors:* Victoria Rautenbach and Frikan Erwee
   - *Student: *Xuri Gong
   - *Wiki:*
   https://trac.osgeo.org/postgis/wiki/GeospatialDataViewerInPgAdmin4ForPostGIS
   - *Repository:* https://github.com/Gooong/pgadmin4/tree/geoviewer
   - *Pull Request:* Code patch has been sent
   <https://www.postgresql.org/message-id/CAA7HE_fnAKtAqfzz2jNZGfYnB5BAcuSt_ZCsJ%2Be-nwFPDHCdxg%40mail.gmail.com>


*Abstract*
I propose to implement a geospatial data viewer in pgAdmin4 that will allow
users to view the tables in a spatial database and the results of queries
executed. With the viewer I intend to develop, users can directly view
geospatial data on a map within the pgAdmin4 GUI.

*Status Before GSoC*
pgAdmin <https://www.pgadmin.org/> is the GUI management tool for
PostgreSQL. The latest version pgAdmin4 is web-based and written in Python
and jQuery with Bootstrap, using the Flask framework. But currently there
is no geospatial data viewer in pgAdmin4-3.1, and external applications,
such as QGIS are required if we want to view the data on a map. The
pgAdmin4 Data Output panel only allows the user to view the table with the
encoded geometries in text format.

*My Proposal and Work*
With the geometry viewer I have created, users can now view individual
geometry as well as all the geometries in the map. They can also view the
properties of the geometries directly in the geometry viewer by clicking
the specific geometry. Here are some screenshots:

[image: Peek 2018-08-16 00-13.gif]
[image: screenshot_1.png]
[image: screenshot_2.png]
[image: screenshot_4.png]

Below are the default strategies for parsing data:

   - *Supported data type: *Geometry Viewer support 2d and 3dm geometries
   in EWKB format including `Point, LineString, Polygon MultiPoint,
   MultiLineString, MultiPolygon, GeometryCollection`.
   - *About SRID: *If there are geometries with different SRIDs in the same
   column, the viewer will render geometries with the same SRID in the map. If
   SRID=4326 the OSM tile layer will be added into the map.
   - *About data size: *For performance considerations, the viewer will
   render geometries no more than 100000, totaling up to 20MB.


*Test*
Please test my code following the instructions below:

   1. Download the geoviewer branch
   <https://github.com/Gooong/pgadmin4/tree/geoviewer>.
   2. Build and run pgAdmin4 following README.md in the repository.
   3. For unit test, run cd $PGADMIN4_SRC and make check-js.
   4. If you want to have a try or test manually, install PostGIS
   <https://postgis.net/> and import some geometry data (for example
import this
   database
   <https://drive.google.com/open?id=1NHWW4WPli7kxpuGaDFGVFLDXdc2D20wp>
   using psql <https://www.postgresql.org/docs/9.2/static/app-psql.html>).
   Then execute query in the pgAdmin4 and view the results.


*Future Work*
The geometry viewer is functional and below are some suggestions for future
work:
*Optimize performance:*

   -  Cluster markers using Leaflet.markercluster
   <https://github.com/Leaflet/Leaflet.markercluster> and deflate lines and
   polygons to a marker when their screen size becomes too small using
   Leaflet.Deflate <https://github.com/oliverroick/Leaflet.Deflate>.
   - Enable vectortile layer using geojson-vt
   <https://github.com/mapbox/geojson-vt>. Here is an example:
   leaflet-and-geojson-tiles
   <https://www.getbounds.com/blog/leaflet-and-geojson-tiles/>.
   - PgAdmin4 only load 1000 records by default. When users click 'view'
   button in the column header, show the map firstly and load the other
   records in the background and then add the records dynamically.

*New features:*

   - Custom geometry style, e.g SELECT *, '#333333' as __fillcolor,
   '#232323' as __strokecolor, 0.5 as __strokewidth FROM xxx.
   - Custom background tile layer. Enable users to specify their own
   tilelayer url.
   - Highlight selected feature. Add 'zoom home extent' button.


Regards,
Xuri Gong
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/soc/attachments/20180816/803ecacb/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Peek 2018-08-16 00-13.gif
Type: image/gif
Size: 2938069 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/soc/attachments/20180816/803ecacb/attachment-0001.gif>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: screenshot_1.png
Type: image/png
Size: 137746 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/soc/attachments/20180816/803ecacb/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: screenshot_2.png
Type: image/png
Size: 226391 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/soc/attachments/20180816/803ecacb/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: screenshot_4.png
Type: image/png
Size: 119565 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/soc/attachments/20180816/803ecacb/attachment-0005.png>