How to define Z dimension on table with PostGIS for import in TileMill?

Question

I'm trying to reproduce the idea behind this blogpost from Mapbox, but with other data, for example the ne_10m_populated_places_simple dataset from Natural Earth. By adding a z-dimension to the data (eg. the scalerank column, size of city), the idea is to be able to set a height in CartoCSS in Tilemill and get a 3D-representation of the data, with x,y as geo-point and z as value (like in Mapbox's example).

The result could perhaps be something like this (except this map is oblique, different story):

What do I need to do with PostGIS (or other) so that the z-dimension is set for value and correctly converted so I can set the height in CartoCSS?

Answer 1

Actually, you don't need to add a z-dimension in order to achieve the building-height that they use in that blog post. In fact, the map pluto image you posted is using the same cartocss method. Basically, you just need a column in your dataset that includes some variable for building-height. In their case, they have three columns, z_small, z_large, and z_xlarge, each of which is a numeric value.

Here, I've taken the style in the blog post and applied it in CartoDB,

Here you can see I have columns for each of those (plus another column for the building size_sqm),

Answer to do it anyway

In case anyone comes across this thread and really really wants to get geometries with a z-dimension out of CartoDB, here is the way.

Get default geometry

SELECT the_geom FROM ne_50m_admin_1_states LIMIT 1

and GeoJSON over the api

Add Z of 100 to Geometry

SELECT 
  ST_Translate(ST_Force3D(the_geom),0,0,100) AS the_geom 
FROM ne_50m_admin_1_states 
LIMIT 1

and GeoJSON over the API -

https://andrew.cartodb.com/api/v2/sql?format=geojson&q=SELECT%20ST_Translate(ST_Force3D(the_geom),0,0,100)%20AS%20the_geom%20FROM%20ne_50m_admin_1_states%20LIMIT%201

Add Z based on a second column

In this case, I'll just use cartodb_id of each row to be the Z for the resulting geometry.

SELECT 
  ST_Translate(ST_Force3D(the_geom),0,0,cartodb_id) AS the_geom 
FROM ne_50m_admin_1_states 
LIMIT 1

and GeoJSON over the API -

https://andrew.cartodb.com/api/v2/sql?format=geojson&q=SELECT%20ST_Translate(ST_Force3D(the_geom),0,0,cartodb_id)%20AS%20the_geom%20FROM%20ne_50m_admin_1_states%20LIMIT%201