5

I'm having a little trouble importing a shapefile into PostgreSQL. Using shp2pgsql, I'm able to import the non-geometry data just fine. However, the geometry data is coming in based on some coordinate system rather than the lon/lat we require. I'm hoping to make this conversion it the import stage, but I seem to be unable to find the magic combination of projection conversion parameters in shp2pgsql that accomplishes this.

Attempting to import using...

shp2pgsql -c -d -D -W LATIN1 -s 4269 -I 'path/to/shapefile.shp' my_table | psql -d mydb

...works except for the geometry column conversion I need. Querying...

SELECT ST_AsText(geom) FROM my_table LIMIT 1;

...produces...

"MULTIPOLYGON(((-9619467.85573143 3856511.77685212,-9619466.74264784 3854145.2378255,-9619466.73474416 3854144.27900372 (...)"

My goal is to produce something like...

"MULTIPOLYGON(((-123.1 45.1,-123.2 45.2, -123.3 45.3 (...)"

(I'm using placeholder lon/lats, obviously). The projection file for the shapefile reads:

GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 
84",6378137,298.257223563,AUTHORITY["EPSG",
"7030"]],AUTHORITY["EPSG","6326"]],PRIMEM
["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT
["degree",0.0174532925199433,AUTHORITY["EPSG",
"9122"]],AUTHORITY["EPSG","4326"]]

I'm at a loss as to what to try next.

1
  • 2
    Any chance there is a .txt file included with the data? Otherwise, what is the source of the data, did you collect it yourself? ..I'm concerned that at some point in its life, someone has used ArcGIS to "clear" the projection, then define the projection as something its not--or perhaps just declared it as something it's not in the first place--and now it's all tangled.
    – elrobis
    Oct 20, 2014 at 23:01

2 Answers 2

6

Your data looks like it's Mercator (big negative X's). I'll assume web mercator, but that could be wrong, and may lead to meter-level inaccuracies (if it's "real" mercator, use 3395).

shp2pgsql -c -d -D -W LATIN1 -s 3857 -I 'path/to/shapefile.shp' my_table | psql -d mydb

Now flip the coordinates to geographics inside the database

ALTER TABLE my_table 
  ALTER COLUMN geom TYPE Geometry(Point,4326)
  USING ST_Transform(geom, 4326);

If your geometry type is something other than Point, substitute appropriately.

1
  • That WORKED! I need to buy you a virtual beer. Thank you.
    – sstringer
    Oct 21, 2014 at 11:31
0

EPGS 4269 is NAD83, not WGS84. Try changing the -s switch to 4326 instead. I'm not sure why the units are changing on you, both are decimal degrees.

1
  • Well.. based on the ST_AsText(geom) he ran, I don't think his raw data "started life" as geodetic coordinates. If I were to bet, I'd expect the source is in some flavor of UTM, that or a State Plane projection, considering he's in the lone star state.
    – elrobis
    Oct 20, 2014 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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