2

I have a shapefile that is supposed to be the city of San Francisco. I got it from the good folks at Transbase. On their tiles, it seems to align with the city itself, but when I uploaded it to CartoDB, it wound up hovering about ten miles north of where it belongs:

Right shape, wrong spot

You can see the map on CartoDB at http://cdb.io/1Lv2U4o.

I did use QGIS to merge the census tracts into a single polygon, but I don't see how that would shove everything north.

I'm not even sure how to troubleshoot this! I assume it's a projection issue, however?

If I do SELECT ST_srid(the_geom) AS geom_srid, ST_srid(the_geom_webmercator) AS webmerc_srid FROM san_francisco_california I get two different values:

geom_srid: 4326
webmerc_srid: 3857

though I see those same two values in other, perfectly functional datasets.

  • Could you link to either the dataset you used or the map that you made from it? – Andy Eschbacher Sep 14 '15 at 6:18
  • @AndyEschbacher Added a link to the map. The data I'd have to chase down, but I downloaded the QGIS connection from Transbase and then output one layer as a shapefile for the city. – Amanda Sep 15 '15 at 1:11
  • I don't know CartoDB, so excuse eventually dumb questions. I reproduced the first step: Make a QGIS connection to the TransBase PostGIS server and load a layer. It is in SRID 3857 and aligns well with OSM tiles. Then I would use Vector, Geoprocessing Tools, Dissolve, which produces a shapefile. What was your next step? Do you use your own PostGIS server or is it running at CartoDB? – Redoute Sep 15 '15 at 8:38
  • @Redoute: then I exported that layer as a shapefile and uploaded the shapefile to CartoDB (which runs on PostGIS). I've done this dozens of times with other maps, some manipulation in QGIS, some on CartoDB, and I've never managed to botch the projection. – Amanda Sep 15 '15 at 16:39
  • OK, I have an account now. :-) cdb.io/1OUVgUa :-/ – Redoute Sep 15 '15 at 17:33
4

For a shapefile in CRS 3857 (Pseudo Mercator) QGIS writes a prj projection file with the following WKT (formatted):

PROJCS["WGS_84_Pseudo_Mercator",
    GEOGCS["GCS_WGS_1984",
        DATUM["D_WGS_1984",
            SPHEROID["WGS_1984",6378137,298.257223563]],
        PRIMEM["Greenwich",0],
        UNIT["Degree",0.017453292519943295]],
    PROJECTION["Mercator"],
    PARAMETER["central_meridian",0],
    PARAMETER["false_easting",0],
    PARAMETER["false_northing",0],
    UNIT["Meter",1],
    PARAMETER["standard_parallel_1",0.0]]

When importing the shapefile to CartoDB the coordinates get corrupted. The same happens in QGIS itself, if you delete QGIS's own and preferred projection file with suffix qpj. QGIS then reads the file as CRS 54004 "World Mercator", showing the same shifting as seen in CartoDB. Because of this, I guess there is a bug in GDAL/OGR, which causes either the wrong WKT being written or this WKT used wrong.

Workaround a): Before you zip and upload e.g. SF.shp, delete the file SF.prj, then copy the file SF.qpj and rename it to SF.prj.

Workaround b): Reproject the shapefile to lat/lon (EPSG 4326) in QGIS before uploading it.

Bonus solution c) if you really really would want to repair the coordinates inside CartoDB with PostGIS:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 954004, 'esri', 54004, '+proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +ellps=WGS84 +datum=WGS84 +units=m +no_defs ', 'PROJCS["World_Mercator",GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Mercator_1SP"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",0],PARAMETER["Standard_Parallel_1",0],UNIT["Meter",1],AUTHORITY["EPSG","54004"]]');
UPDATE mytable
    SET the_geom = ST_Transform(ST_SetSRID(ST_Transform(the_geom, 954004), 3857), 4326);

(spatial_ref_sys code from http://spatialreference.org/ref/esri/54004/postgis/)

  • Et voila! I went with Workaround A. Such a relief! – Amanda Sep 16 '15 at 17:50
5

This sounds like a projection issue.

You can try reprojecting to EPSG:3857 -- WGS84 Web Mercator (Auxiliary Sphere) and see what happens when you upload your next shapefile to CartoDB.

1

According to the Transbase site, their data is in the 900913 projection. Therefore, as @Mista Gill suggests, you can run the following query into the SQL toolbar of CartoDB to reproject it:

SELECT 
  ST_Transform(ST_SetSRID(the_geom, 900913), 4326) as the_geom,        
  ST_Transform(ST_SetSRID(the_geom, 900913), 3857) as the_geom_webmercator,
  cartodb_id 
FROM tablename

If you want to generate a dataset from this without having to keep the SQL query applied, just click on the "create dataset from query" button that appears when a query is applied in the CartoDB Editor.

Another approach that you can use is to just generate a table from the_geom column (and any other columns you might want to take, separated by commas), as the_geom_webmercator and the cartodb_id are automatically generated:

SELECT 
  ST_Transform(ST_SetSRID(the_geom, 900913), 4326) as the_geom,        
  other_column_name,
  other_column_name_2,
FROM tablename

Notice that this last query won't be visible in the map view until you create a dataset from it, as you're not generating the Web Mercator geometries needed to display the map (stored in the_geom_webmercator).

  • Hmm. The re-projection doesn't give me an error but I'm not seeing the shape on the map at all. – Amanda Sep 15 '15 at 1:26
  • Could you share the link from which you downloaded the data from the Transbase site? I haven't been able to find it! And yes, you're right. I tried downloading a copy of your dataset and the reprojection is sending the geometry to (0,0), so there's something wrong there. – iriberri Sep 16 '15 at 10:42
  • @iriberri a) You don't need Transbase data to reproduce the error. Create any shapefile in QGIS with CRS 3857 and upload it to cartodb. – Redoute Sep 16 '15 at 15:44
  • 1
    @iriberri b) On the TransBase site in the right lower corner click on connect. There you find a .zip file with instructions how to connect with QGIS to their PostGIS server. From there download the census tracts and dissolve them. – Redoute Sep 16 '15 at 15:47
  • @iriberri -- see Redoute's walk through to get to the offending Shapefile. FWIW, you don't have to dissolve them. The original also lands up on Point Richmond. – Amanda Sep 16 '15 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.