Import foot traffic data from file to table

[antoniocarlon]

Import all the foot traffic data (level 22) from file to table.
The table will look like this:

CREATE TABLE "{schema}".{table} (
    quadkey TEXT,
    doyXXX  integer[24],
    ...
    doyYYY  integer[24],
    the_geom GEOMETRY(GEOMETRY, 4326),
    PRIMARY KEY (quadkey)
)

Notes:

• Each of the doyXXX column represents one Day of Year and will hold the values for the measurement for each hour of the day.
• the_geom will be a POINT geometry.

[antoniocarlon]

The raw data contains specific dates so I'm going to create columns like

"20170517" INTEGER[24],

instead of

doyXXX  INTEGER[24], 

[juanignaciosl]

Number-only columns (20170517) are not too comfortable, because forces quoting. In addition, it doesn't explain what the data is. What if some meta were prepended? Something like traffic2017017? Not sure, though, I just want to know your opinion.

[rochoa]

Some questions:

• What's the idea with this approach?
• Does that schema affect the performance? I haven't done any research, but if the row size doesn't fit into the page size: do we compensate that by having to read fewer rows?
• How do we store data from different years?
• Why do we keep the quadkey and the_geom?

[antoniocarlon]

• The idea is importing all the raw data into a table that we can use to generate aggregations (a new table, a materialized view, Create an aggregations table from the foot traffic data table #475)
• We will need to do some calculations, but this approach allows us to have all the data in one row so we need to read only one row to fetch all the data for a given point
• I'm going to change the doyXXX INTEGER[24] columns to be "20170517" INTEGER[24] (I'll take a look at the naming with Nacho's suggestion)
• We don't need both the quadkey and the geom, but if we are going to aggregate data in several zoom levels, having the quadkey is very convenient (we just need to truncate the quadkey to get the upper zoom levels)