Visualisation: exploration

[lorenzznerol]

Create first visualisation using Folium or d3.

• learn from tutorials and examples
• create a first prototype

It needs to be based on top of a relational database like postgreSQL (other issue which does not have to be built by the visualisation team).

Play around with maps / graphs / figures and tell what they mean.

• Take a sample of the data and create first insights. This could be provided by the Optimisation / Analysis team.
• Expand to the ~20 mil. data points if possible, again supported by the backend.

[md-Junaid]

[lorenzznerol]

Use Grafana to visualize geospatial data stored in TimescaleDB

https://docs.timescale.com/timescaledb/latest/tutorials/grafana/geospatial-dashboards/

SELECT time_bucket('5m', rides.pickup_datetime) AS time,
       rides.trip_distance AS value,
       rides.pickup_latitude AS latitude,
       rides.pickup_longitude AS longitude
FROM rides
WHERE $__timeFilter(rides.pickup_datetime) AND
  ST_Distance(pickup_geom,
              ST_Transform(ST_SetSRID(ST_MakePoint(-73.9851,40.7589),4326),2163)
  ) < 2000
GROUP BY time,
         rides.trip_distance,
         rides.pickup_latitude,
         rides.pickup_longitude
ORDER BY time
LIMIT 500;

[lorenzznerol]

PostGIS

PostGIS (an extension that is available at the TimescaleDB backend) seems promising, there, we can use polygons in SQL which we need to show neighboured spatial points in a certain range. If we did not use polygons, we would end up in roughly approximating such neighbour-circles only. Which is also possible, but not that exact.

https://postgis.net/docs/manual-1.3/ch04.html

Table of Contents

4.1. GIS Objects

4.1.1. OpenGIS WKB and WKT
4.1.2. PostGIS EWKB, EWKT and Canonical Forms
4.1.3. SQL-MM Part 3

4.2. Using OpenGIS Standards

4.2.1. The SPATIAL_REF_SYS Table
4.2.2. The GEOMETRY_COLUMNS Table
4.2.3. Creating a Spatial Table
4.2.4. Ensuring OpenGIS compliancy of geometries

4.3. Loading GIS Data

4.3.1. Using SQL
4.3.2. Using the Loader

4.4. Retrieving GIS Data

4.4.1. Using SQL
4.4.2. Using the Dumper

4.5. Building Indexes

4.5.1. GiST Indexes
4.5.2. Using Indexes

4.6. Complex Queries

4.6.1. Taking Advantage of Indexes
4.6.2. Examples of Spatial SQL

4.7. Using Mapserver

4.7.1. Basic Usage
4.7.2. Frequently Asked Questions
4.7.3. Advanced Usage
4.7.4. Examples

4.8. Java Clients (JDBC) 4.9. C Clients (libpq)

4.9.1. Text Cursors
4.9.2. Binary Cursors

Examples of the text representations (WKT) of the spatial objects of the features are as follows:

    POINT(0 0)

    LINESTRING(0 0,1 1,1 2)

    POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

    MULTIPOINT(0 0,1 2)

    MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))

    MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))

    GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))

Result could be like this:

Source: https://atmamani.github.io/cheatsheets/open-geo/postgis-1/

Second page of that tutorial is also interesting:

https://atmamani.github.io/cheatsheets/open-geo/postgis-2/

Table of Contents

        Using SQLAlchemy, GeoAlchemy, Pandas and GeoPandas with PostGIS
    Querying
        Querying with pandas
        Querying with geopandas
        Where clauses
        Where clauses geometry functions
    Connect to Zesty db
        Display image

---

Source: https://www.endpoint.com/blog/2018/02/08/regionating-with-postgis

---

From 2013, but perhaps still interesting?

I have written code to interpolate the DEMs from depth samples and have a proof of concept in place where I store these in non-tiled rasters in PostGIS (one raster per body of water) which works fairly well.

Last, but not least, I want to perform light client side line interpolation (or something similar) for aesthetics. I.e. the resolution of the bathymetric map may be lower (since higher resolution really has no value) but at this resolution contours typically become clunky. Perhaps this is an argument for TopoJSON and it's possibilities with simplification? Here's an example I've made with D3 (using server side generated non-tiles GeoJSON):

The answer mentions topojson and geojson, perhaps something to look at in addition to postGIS?

Source: https://stackoverflow.com/questions/18694937/bathymetric-maps-with-postgis-and-d3

---

Source: https://postgis.net/workshops/postgis-intro/geometry_returning.html