search

CartoDB / cartodb-postgresql

PostgreSQL extension for CartoDB
BSD 3-Clause "New" or "Revised" License
111 stars 52 forks source link

has_usable_geom is not using a valid the_geom in some scenarios #205

Open iriberri opened 8 years ago

iriberri commented 8 years ago

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 https://github.com/CartoDB/cartodb-postgresql/blob/54973c01f2a6561a728653f68b5e9b330048da12/scripts-available/CDB_CartodbfyTable.sql#L772 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 commented 8 years ago

Definitely this assignment is not expected anywhere.

iriberri commented 8 years ago

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 commented 8 years ago

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 commented 8 years ago

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