has_usable_geom is not using a valid the_geom in some scenarios

[iriberri]

Scenario 6: the_geom text && wkb_geometry geometry(Point, 4326) -> it explodes (Text column the_geom is missing!)
Scenario 7 - (the post-ogr2ogr typical one): the_geom geometry && wkb_geometry geometry(Point, 4326) -> it takes wkb_geometry

and

cartodb-postgresql/scripts-available/CDB_CartodbfyTable.sql

Line 772 in 54973c0

text_geom_column_srid := true;

here an option which is typically used for text columns is being used in a geometry scope. The default value for that variable is actually true, and there's nothing inside the geometry scope that sets it to false. Would need to check how the table rewriter is using it.

[iriberri]

Definitely this assignment is not expected anywhere.

[iriberri]

This piece of code is overwriting an existent the_geom::text (if it exists) whenever there's already another geometry column in the table.

[iriberri]

I think that the general issue can be reduced to: whenever there's a column not named the_geom for which the column information has SRID 4326 (::geometry(X, 4326), not as in ::geometry) it is used and a possible valid the_geom is dropped. Ogr2ogr is generating this type of columns, as the SRID of the destination table is specified, and this is why the_geom is dropped whenever ogr2ogr guesses lat/long columns in the table. Moreover, the geometries are being promoted to multi, from which we get a multipoint the_geom.

[iriberri]

https://gist.github.com/iriberri/24c9daab23c98aed977b