Converting factfinder2.census.gov map data to KML?

Question

I've downloaded a map from http://factfinder2.census.gov/faces/tableservices/jsf/pages/productview.xhtml?pid=DEC_10_SF1_P1 (limited to San Francisco census tracts), and I'd like to convert it to KML format. However, after unzipping the shapefile .zip file and running

$ ogr2ogr -f KML pop_by_census_tract.kml 140_00.shp

I get the following error message:

ERROR 6: No translation for Mercator_Auxiliary_Sphere to PROJ.4 format is known.
Warning 1: Failed to create coordinate transformation between the
input coordinate system and WGS84.  This may be because they
are not transformable, or because projection services
(PROJ.4 DLL/.so) could not be loaded.
KML geometries may not render correctly.
This message will not be issued any more. 

Source:
PROJCS["WGS_1984_Web_Mercator_Auxiliary_Sphere",
    GEOGCS["GCS_WGS_1984",
        DATUM["WGS_1984",
            SPHEROID["WGS_1984",6378137.0,298.257223563]],
        PRIMEM["Greenwich",0.0],
        UNIT["Degree",0.0174532925199433]],
    PROJECTION["Mercator_Auxiliary_Sphere"],
    PARAMETER["False_Easting",0.0],
    PARAMETER["False_Northing",0.0],
    PARAMETER["Central_Meridian",0.0],
    PARAMETER["Standard_Parallel_1",0.0],
    PARAMETER["Auxiliary_Sphere_Type",0.0],
    UNIT["Meter",1.0]]

ERROR 1: Latitude 4553026.920500 is invalid. Valid range is [-90,90]. This warning will not be issued any more
Warning 1: Longitude -13627851.842700 has been modified to fit into range [-180,180]. This warning will not be issued any more

When I open the resulting KML file in Google Earth, I get randomly scattered lines across the entire planet. The example at code.google.com/apis/kml/articles/vector.html works fine, so the installation of GDAL (1.8.0 from macports) isn't totally broken.

http://trac.osgeo.org/gdal/ticket/3962 seems related, but I'm not sure if it's saying that Mercator_Auxiliary_Sphere is just not supported by GDAL, or if it's implying I should pass another command line option to make it work.

I wound up accomplishing my goal by downloading http://www2.census.gov/geo/tiger/GENZ2010/gz_2010_06_140_00_500k.zip, which contains shapefiles that ogr2ogr and shp2pgsql can understand, and joining it with the pure table data from factfinder2. I'm still curious about the format factfinder2 is using, but it's no longer blocking progress for me.

Could it actually be in some strange UTM projection? Latitude 4553026 would be in the ballpark, but -13627851 is kinda wacky. — neuhausr, Dec 05 '11 at 15:33

score 2 · Accepted Answer · edited Sep 21 '18 at 21:29

$ ogr2ogr -f KML -s_srs EPSG:3857 -t_srs EPSG:3857 pop_by_census_tract.kml 140_00.shp

creates a KML file with the boundaries in the right places. It needs both of those options. Just -s_srs EPSG:3857 produces the same error as in the initial question, while -t_srs produces one like

ERROR 6: No translation for Mercator_Auxiliary_Sphere to PROJ.4 format is known.
Failed to create coordinate transformation between the
following coordinate systems.  This may be because they
are not transformable, or because projection services
(PROJ.4 DLL/.so) could not be loaded.
Source:
PROJCS["WGS_1984_Web_Mercator_Auxiliary_Sphere",
    ... # Same as 140_00.prj
Target:
PROJCS["WGS 84 / Pseudo-Mercator",
    ... # Same as in the gdal ticket

score 0 · Answer 2 · answered Dec 04 '11 at 03:50

0

Try uploading the shapefile to Google Fusion Tables. That will let you download the data as KML: http://shpescape.com/

answered Dec 04 '11 at 03:50

Sean

16

Unfortunately, shpescape seems as confused as ogr2ogr when trying to upload the census data. The upload button just redirects straight back to the home page. It does fine at the data from http://www.census.gov/geo/www/cob/tr2000.html, but ogr2ogr could also handle that. – Jeffrey Yasskin Dec 04 '11 at 08:09
It looks like shpescape is actually using OGR under the covers (http://code.google.com/p/shpescape/source/browse/shapeft/views.py#26) so it's not surprising that it's no more powerful than the default options to the command-line tool.
Still, it's more convenient than converting before the upload. Thanks for the pointer, for other files.
– Jeffrey Yasskin Dec 04 '11 at 08:17

score 0 · Answer 3 · answered Dec 05 '11 at 14:11

0

You could try loading the shapefile into GeoServer and doing a WMS request with KML as the output format.

answered Dec 05 '11 at 14:11

Ian Turton

81,417
6
84
185

GeoServer's loading interface is remarkably complicated. When I load the problematic shapefile as a "store" into GeoServer (2.1.2), and then try to add a "layer" for it, it doesn't pick up either a native SRS or a declared SRS, and won't finish the import without them. Even when I create a store and layer from one of the working shapefiles (http://www2.census.gov/geo/tiger/GENZ2010/gz_2010_06_140_00_500k.zip), it wants me to guess a "Declared SRS", and the find button won't let me actually search (it just reloads the page). So ... can you be more precise? – Jeffrey Yasskin Dec 06 '11 at 04:57
1

prj2epsg.org will convert ESRI non-standard projections to a form the rest of the world uses. – Ian Turton Dec 06 '11 at 08:42
That told me that the gz_2010 data was in EPSG:4269, which worked, but the closest it got to the problematic data was EPSG:32663, which still put it north of Redding (when it should cover San Francisco). I'm going to try searching the whole PostGIS spatial_ref_sys table to see if I can find something that matches. – Jeffrey Yasskin Dec 07 '11 at 06:48
EPSG:3857 gets it. And ... I probably should have guessed this from the gdal ticket, which also mentioned 3857. Thanks for the pointers. – Jeffrey Yasskin Dec 07 '11 at 08:06

score 0 · Answer 4 · edited May 26 '19 at 20:54

If a->b doesn't work, try a->c->b.

Web Mercator may not have a proj.4 match. So if you could find the proj.4 equivalent of Web Mercator, that could be your a->c translation. Perhaps ESPG 3857? You might also need to remove the existing projection, and then add transformable one it its place.

Doing it in R, looks like I did the following:

P4S.latlon <- CRS("+proj=longlat +ellps=WGS84 +datum=WGS84") 
#define a datum when I read it in.  
tl_2010_04_tabblock00 <- readShapePoly("tl_2010_04_tabblock00", verbose=TRUE, proj4string=P4S.latlon)

This may also be helpful.

Converting factfinder2.census.gov map data to KML?

4 Answers4

Linked

Related