0

I could really use some direction here. I'm fairly new to PostGIS, and I'm coming up with nothing in my search for answers.

I'm pulling in various shapefiles with several different projections (EPSG/SRID). I extract the GEOGCS from the .prj file, look up the SRID number using https://developers.arcgis.com/javascript/3/jshelp/gcs.html, matching it and import them with shp2pgsql -s. This is pretty straight forward. Generally it's working fine. Other times, not so much.

When it's not working, the coordinates I'm getting are like 153009.914, 5497499.47 (clearly off the map). I believe that this is a simple projection issue, and I'm not using the right SRID.

.prj:
PROJCS["EO_Lambert_Conformal_Conic",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",1000000.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-84.0],PARAMETER["Standard_Parallel_1",44.5],PARAMETER["Standard_Parallel_2",54.5],PARAMETER["Latitude_Of_Origin",0.0],UNIT["Meter",1.0]]

Import: # shp2pgsql -c -s 4269 -W UTF-8 -I /Shape/ont/DIVISION.shp ont | psql -U postgres -d shape

Structure:

   Column   |            Type             | Collation | Nullable |                  Default                  
------------+-----------------------------+-----------+----------+-------------------------------------------
 gid        | integer                     |           | not null | nextval('ont_gid_seq'::regclass)
 objectid   | integer                     |           |          | 
 ed_id      | numeric                     |           |          | 
 pd_number  | numeric                     |           |          | 
 pd_label   | character varying(64)       |           |          | 
 ed_name_en | character varying(64)       |           |          | 
 ed_name_fr | character varying(64)       |           |          | 
 shape_leng | numeric                     |           |          | 
 shape_area | numeric                     |           |          | 
 geom       | geometry(MultiPolygon,4269) |           |          | 
Indexes:
    "ont_pkey" PRIMARY KEY, btree (gid)
    "ont_geom_idx" gist (geom)

SQL: select ST_Astext(geom) AS coordinates FROM ont limit 1;

Result: **MULTIPOLYGON(((574380.4001 4808575.0399,574434.7803 4808545.44,574496.2521 4808512.3351,............... ***etc.

What am I doing wrong?

Thank you for any insight!

Ian
  • 11
  • 2

1 Answers1

0

Simplifying a bit, the SRID you are using is the one of the datum (The geogCS, geographic Coordinate System), i.e. the description of the "round" earth. Unit is degree and all coordinates are +- 180 / +- 90.

On top of that, there is projection which transform the coordinates to a flat surface. In your case, it is a special case of Lambert Conformal Conic, for Elections Ontario (EO_Lambert_Conformal_Conic), and the units are in meters.

Using this projection name, we can find that the SRID to use is 7149

JGH
  • 15,928
  • 4
  • 31
  • 48
  • Thanks JGH, that seems simple enough. But I tried to import with that number but I got an: ERROR: AddGeometryColumn() - invalid SRID. I found the insert statement from the link you provided, and added it to my spatial_ref_sys, but still get the same error. Any thoughts? – Ian Oct 27 '20 at 17:44
  • Try with the value from `select srid from spatial_ref_sys where auth_srid=7149;` (likely 97149) – JGH Oct 27 '20 at 18:11
  • I did, it's there! Odd eh? – Ian Oct 27 '20 at 18:25
  • :-) great! Since multiple sources can have the same internal IDs, I guess PostGIS automatically creates an SRID of its own – JGH Oct 27 '20 at 18:26
  • so is it fully working? (I will update the answer if it's the case) – JGH Oct 27 '20 at 18:26
  • Also, I tried your logic on 'NAD_1983_CSRS_New_Brunswick_Stereographic' in a google search and followed the link to https://spatialreference.org/ giving me an SRID of 2953, imported that shapefile and it resulted in bad coordinates again... There is still more I need to understand, it seems. – Ian Oct 27 '20 at 18:32
  • So, I ran the import with 97419 and it DID import. But the data in the geom isn't a coordinate.... :( Darn! – Ian Oct 27 '20 at 18:46
  • I just ran a test with data from Elections Ontario and `97419` and it seems OK. You will have to create such CRS in QGIS/ArcGIS too, as this custorm SRID is not recognized by default – JGH Oct 27 '20 at 18:52
  • if you are looking for unprojected data (in degrees), it would be easier to reproject the data to 4326 or 4269 before exporting it to PostGIS – JGH Oct 27 '20 at 18:56
  • JGH, you did it. As you suggested I reprojected 97149 to 4326 and the data was coordinates in degrees. I guess I need to learn the difference between unprojected and projected data??? To put it simply? Thanks for working the problem with me!! – Ian Oct 27 '20 at 19:21