6

The County of Napa, California, uses a projection "NAD_1983_StatePlane_California_II_FIPS_0402_Feet"; for example their roads.prj file has:

PROJCS["NAD_1983_StatePlane_California_II_FIPS_0402_Feet",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",6561666.666666666],PARAMETER["False_Northing",1640416.666666667],PARAMETER["Central_Meridian",-122.0],PARAMETER["Standard_Parallel_1",38.33333333333334],PARAMETER["Standard_Parallel_2",39.83333333333334],PARAMETER["Latitude_Of_Origin",37.66666666666666],UNIT["Foot_US",0.3048006096012192]]

I looked this projection up at spatialreference.org and fount a match to this: http://spatialreference.org/ref/esri/102642/ There is an INSERT statement provided:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 9102642, 'esri', 102642, '+proj=lcc +lat_1=38.33333333333334 +lat_2=39.83333333333334 +lat_0=37.66666666666666 +lon_0=-122 +x_0=2000000 +y_0=500000.0000000002 +ellps=GRS80 +datum=NAD83 +to_meter=0.3048006096012192 +no_defs ', 'PROJCS["NAD_1983_StatePlane_California_II_FIPS_0402_Feet",GEOGCS["GCS_North_American_1983",DATUM["North_American_Datum_1983",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",6561666.666666666],PARAMETER["False_Northing",1640416.666666667],PARAMETER["Central_Meridian",-122],PARAMETER["Standard_Parallel_1",38.33333333333334],PARAMETER["Standard_Parallel_2",39.83333333333334],PARAMETER["Latitude_Of_Origin",37.66666666666666],UNIT["Foot_US",0.30480060960121924],AUTHORITY["EPSG","102642"]]'); [inserted new line for clarity]

As you can see the values for the column "srid" is "9102642".

The problem is that the table "spatial_ref_sys" prohibits a value that exceeds 998999. Here is the table SQL showing the constraint:

CREATE TABLE spatial_ref_sys ( srid integer NOT NULL, auth_name character varying(256), auth_srid integer, srtext character varying(2048), proj4text character varying(2048), CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid), CONSTRAINT spatial_ref_sys_srid_check CHECK (srid > 0 AND srid <= 998999) )

The constraint is the "<= 998999" and since the INSERT statement's srid value is 9102642, the INSERT fails.

So I checked to see if the srid 102642 was taken and it was not:

select srid from spatial_ref_sys where srid = 102642;

I then modified the insert statement by removing the first digit "9" from the srid value and then performing the insert.

I then proceeded to import a shapefile using the PostGIS Shapefile Import/Export Manager (v.2.1.0) and specified the srid to the column I had created: enter image description here

My questions are:

1) why would the INSERT statement for PostGIS be in excess of 998999 when PostGIS's table has a constraint to limit srids?

2) Will the work-around I used cause any problems or violate some "plan"?

3) If the first digit "9" is an error, what is the best way to report such at spatialreference.org? Their website does not seem to provide for error reporting.

  • 2
    Check instead for EPSG:2226. 102642 is an old (deprecated) Esri well-known ID for the same coordinate reference system. – mkennedy Nov 12 '13 at 16:50
  • far as i know you can remove constraint <=998999 – simplexio Apr 16 '15 at 9:04
7
  1. Because spatialreference.org pre-dates PostGIS 2.0, when the SRID key limitation was added
  2. No problems with your workaround
  3. sr.org is basically defunct, there's no active maintainers, you can file a ticket for posterity and the next maintainers at http://trac.osgeo.org/metacrs/ under the sr.org category. The '9' was probably tacked on to avoid conflicting with the base number in the event that you had the ESRI codes loaded up.
0

Try using http://epsg.io/

For example, put the SRID after the slash: http://epsg.io/102642

There are Export links at the bottom to view different representations of the projections. The PostGIS Export shows the correct INSERT statement.

You can also add .sql to the URL (e.g. http://epsg.io/102642.sql) to get the raw data.

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.