Stored procedures.

[njmattes]

@legendOfZelda @ricardobarroslourenco Started a branch for stubbing out the various stored procedures we need. In order to minimize network traffic, we'll want to return only the data within the user's viewport. Probably best to pass a bounding box as an argument to the stored procedures.

Hack away!

[ghost]

yes, that's good. btw, to get the tiles that intersect with a bounding box viewport, see query 1 in #9.
the stored procedures are supposed to go into: EDE/ede/sql/, just to mention.

[njmattes]

So, my ignorance of stored procedures leads me to ask more questions. When it comes to subselecting only pixels in the user's viewport:

Is there any reason not to call one stored procedure from another? Ie, I have one that creates a polygon from 2 lats and 2 lons:

CREATE OR REPLACE FUNCTION polygon_from_bbox(
  north float, east float, south float, west float
) RETURNS geometry AS $$
BEGIN
  RETURN ST_Polygon(ST_GeomFromText('LINESTRING('
                                    || west || ' ' || south || ', '
                                    || east || ' ' || south || ', '
                                    || east || ' ' || north || ', '
                                    || west || ' ' || north || ', '
                                    || west || ' ' || south || ')'), 4326);
END;
$$ LANGUAGE plpgsql;

Then one that uses that to eg select centroids:

CREATE OR REPLACE FUNCTION get_raster_centroids(
  mid int, vid int, north float, east float, south float, west float)
RETURNS TABLE(x int, y int, st_astext text) AS $$
DECLARE
  poly geometry;
BEGIN
  poly := polygon_from_bbox(north, east, south, west);
  RETURN QUERY
  SELECT r.x, r.y, ST_AsText(r.geom)
  FROM (SELECT (ST_PixelAsCentroids(rast, 1, false)).*
        FROM grid_data AS gd
        WHERE gd.meta_id = mid
              AND gd.var_id = vid
              AND ST_Intersects(
                rast, poly)
       ) AS r;
END;
$$ LANGUAGE plpgsql;

Is that kosher?

[njmattes]

Also, I presume a polygon made in such a way is made from four great circles (as it would be in d3)? So with an equirectangular projection you wouldn't wind up with a rectangle, but something like an unwrapped truncated cone? Is there a faster way using PostGIS to ensure a projected rectangle rather than adding multiple points along the northern and southern edges of the bounding box?

[ghost]

the code above runs through and calling stored procedures within other stored procedures should be fine, but let's ask @TanuMalik here to be safe.

not sure i understand the other question fully. i mean if the user selects a quad in a d3 viewport and we use an equirectangular projection then we are going to end up with a rectangle on the plane, aren't we?

[njmattes]

If we assume that the polygon is constructed by connecting the points by the shortest lines, then all four edges of the bounding box will be great circles (like how a plane from NY to Copenhagen flies further north in order to get the shortest route).

So if we pass the SW, SE, NE, NW corners of the viewport, the segments SE->NE and NW->SW should be fine because longitudinal lines are great circles around a sphere. But from SW->SE and NE->NW the shortest line will have a northerly arc in the northern hemisphere, will be 'flat' along the equator, etc. Because latitudinal lines aren't great circles (barring the equator).

Unless there's some manner of telling PostGIS not to draw straight lines assuming a spherical geometry, but to draw straight lines in the projected planar geometry. Otherwise you could pass enough intermediate points between NE->NW to 'flatten' the curve. That's sloppy but generally effective enough.

[ghost]

hmmm, but why do we assume that 'the polygon is constructed by connecting the points by the shortest lines' in the first place? i mean if we use d3's 'Plate Carrée' and the user drags on that 'Plate Carrée' viewport, then taking the SW, SE, NE, NW corners of the viewport is correct, isn't it?

[ricardobarroslourenco]

@legendOfZelda the thing on using Plate Carrée is that assumes the usage of a Equirectangular projection, which is not the case, because we are working on the WGS84 Coordinate System (that assumes, usually, an Azimuthal or Orthographic projections). @njmattes which one is currently implemented on ATLAS?

I need a little of time to think on the projection issue. Usually the database is able to provide the reprojections, specially if it is OGR compliant, like PostGIS is. @raffmont do you have any suggestion on this theme of projections?

[ricardobarroslourenco]

Actually I made a mistake on this assumption. I was looking into the ATLAS code, and it seems that we use an Equirectangular projection, at atlas-viewer/atlas_web/static/js/grid.js.

However, when displaying, we have some sort of Azimuthal projection, and probably the parameters acquired from the viewport would reflect that. I've got confused in this. @njmattes are we using a "base" projection for the data that we receive from the database, and when displaying it, we reproject it?

[njmattes]

Sorry, we have an on-campus interview happening today, so I won't be able to look to deeply into this until the weekend. But I'm pretty sure that the current version of ATLAS only uses equirectangular. The old prototype used three projections each at a different scale. But I don't remember off the top of my head what they were. Highest zoom level was equirect though.