Re: [postgis-users] spatialreference.org srid insert error
Since early on (perhaps always) the insert statements generated by spatialreference.org have appended ‘9’ to the SRID. I’ve never understood why. I think, however, that in earlier PostGIS instances, there was no enforcement of a maximum integer, so many would have never noticed. BTW, with California State Plane you might think to see if there is an equivalent SRID in the official EPSGs, rather than the ESRI number for it (102243), but I don’t know your application here. In Ohio, ESRI’s 102722 is about the same as EPSG 3734. Best, Steve http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com <http://www.clemetparks.com/> clevelandmetroparks.com From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Peter Tittmann Sent: Tuesday, July 17, 2012 3:20 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] spatialreference.org srid insert error Thanks Astrid! Seems a little strange that spatialreference.org would produce an invalid insert statement. Regardless, thanks for the pointer. Peter -- Peter Tittmann On Tuesday, July 17, 2012 at 1:09 PM, Astrid Emde wrote: Hello Peter, there is a constraint that checks that srid is < 998999 ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check CHECK (srid > 0 AND srid <= 998999); You should set sird in the insert to 102243 instead of 9102243: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 102243, 'esri', 102243, '+proj=lcc +lat_1=37.07 +lat_2=38.43 +lat_0=36.5 +lon_0=-120.5 +x_0=200 +y_0=50 +ellps=GRS80 +units=m +no_defs ', 'PROJCS["NAD_1983_HARN_StatePlane_California_III_FIPS_0403",GEOGCS["GCS_North_American_1983_HARN",DATUM["NAD83_High_Accuracy_Regional_Network",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",200],PARAMETER["False_Northing",50],PARAMETER["Central_Meridian",-120.5],PARAMETER["Standard_Parallel_1",37.07],PARAMETER["Standard_Parallel_2",38.43],PARAMETER["Latitude_Of_Origin",36.5],UNIT["Meter",1],AUTHORITY["EPSG","102243"]]'); Astrid On Tue, July 17, 2012 6:14 pm, Peter Tittmann wrote: Hi, Im trying to add a srid from spatial reference.org (http://spatialreference.org/ref/esri/102243/postgis/) but i'm getting a error using the insert statement: ERROR: new row for relation "spatial_ref_sys" violates check constraint "spatial_ref_sys_srid_check" I saw this ticket (http://www.google.com/url?sa=t <http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQ> &rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQ FjAA&url=http%3A%2F%2Ftrac.osgeo.org%2Fpostgis%2Fticket%2F1505&ei=f6gFUOy kJcWl2AXz2eDKBQ&usg=AFQjCNH1dlCvTvWEYntRmqflCDtAOUT49g&sig2=tch67lIoPp2IO RZaCyOpSw) and thread (http://www.google.com/url?sa=t <http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CDgQ> &rct=j&q=&esrc=s&source=web&cd=2&ved=0CDgQ FjAB&url=http%3A%2F%2Fpostgis.refractions.net%2Fpipermail%2Fpostgis-devel %2F2012-February%2F018485.html&ei=f6gFUOykJcWl2AXz2eDKBQ&usg=AFQjCNGxJREc 0HrXIbAFuKSfvjU0tbfg9A&sig2=0wH8-t8hCMiuIWKDL6AeFQ) but seems lie it should be fixed. Thanks in advance! Peter -- Peter Tittmann ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Mit freundlichen Grüßen Astrid Emde --- Aufwind durch Wissen! Qualifizierte OpenSource-Schulungen bei der www.foss-academy.eu -- Astrid Emde WhereGroup GmbH & Co.KG Eifelstraße 7 53119 Bonn Germany Fon: +49(0)228 90 90 38 - 19 Fax: +49(0)228 90 90 38 - 11 astrid.e...@wheregroup.com www.wheregroup.com Folgen Sie der WhereGroup auf twitter: http://twitter.com/WhereGroup_com Amtsgericht Bonn, HRA 6788 --- Komplementärin: WhereGroup Verwaltungs GmbH vertreten durch: Olaf Knopp, Peter Stamm --- pgp-public key: http://pgp.mit.edu:11371/pks/lookup?search=0x06DA52D72D515284 Signierte und/oder verschlüsselte Nachrichten sind sehr willkommen Signed and/or encrypted mail is highly appreciated ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] spatialreference.org srid insert error
On 18 July 2012 07:19, Peter Tittmann wrote: > Seems a little strange that spatialreference.org would produce an invalid > insert statement. Regardless, thanks for the pointer. Personally, I feel that spatialreference.org is a bit broke (but otherwise an excellent resource!). Firstly, it is broke with respect to the current version of PostGIS, where their SRID > MAX_SRID, thus invalid. Secondly, their SRIDs are probably internal representations in their database, so they have really peculiar codes. For instance, if one were to use the definition of EPSG:4326 from http://spatialreference.org/ref/epsg/4326/postgis/ they wound need to use SRID=94326 to encode geometries, which is really odd and could raise some unexpected behaviour if it were converted to a geography type. So in general, try to set srid the same as auth_srid. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] spatialreference.org srid insert error
Thanks Astrid! Seems a little strange that spatialreference.org would produce an invalid insert statement. Regardless, thanks for the pointer. Peter -- Peter Tittmann On Tuesday, July 17, 2012 at 1:09 PM, Astrid Emde wrote: > Hello Peter, > > there is a constraint that checks that srid is < 998999 > > > ALTER TABLE spatial_ref_sys > ADD CONSTRAINT spatial_ref_sys_srid_check CHECK (srid > 0 AND srid <= > 998999); > > You should set sird in the insert to 102243 instead of 9102243: > > INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, > srtext) values > ( 102243, 'esri', 102243, '+proj=lcc +lat_1=37.07 > +lat_2=38.43 +lat_0=36.5 +lon_0=-120.5 +x_0=200 > +y_0=50 +ellps=GRS80 +units=m +no_defs ', > 'PROJCS["NAD_1983_HARN_StatePlane_California_III_FIPS_0403",GEOGCS["GCS_North_American_1983_HARN",DATUM["NAD83_High_Accuracy_Regional_Network",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",200],PARAMETER["False_Northing",50],PARAMETER["Central_Meridian",-120.5],PARAMETER["Standard_Parallel_1",37.07],PARAMETER["Standard_Parallel_2",38.43],PARAMETER["Latitude_Of_Origin",36.5],UNIT["Meter",1],AUTHORITY["EPSG","102243"]]'); > > Astrid > > On Tue, July 17, 2012 6:14 pm, Peter Tittmann wrote: > > Hi, > > > > > > Im trying to add a srid from spatial reference.org (http://reference.org) > > (http://spatialreference.org/ref/esri/102243/postgis/) but i'm getting a > > error using the insert statement: > > > > ERROR: new row for relation "spatial_ref_sys" violates check constraint > > "spatial_ref_sys_srid_check" > > > > > > I saw this ticket > > (http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQ > > FjAA&url=http%3A%2F%2Ftrac.osgeo.org > > (http://2Ftrac.osgeo.org)%2Fpostgis%2Fticket%2F1505&ei=f6gFUOy > > kJcWl2AXz2eDKBQ&usg=AFQjCNH1dlCvTvWEYntRmqflCDtAOUT49g&sig2=tch67lIoPp2IO > > RZaCyOpSw) and thread > > (http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CDgQ > > FjAB&url=http%3A%2F%2Fpostgis.refractions.net > > (http://2Fpostgis.refractions.net)%2Fpipermail%2Fpostgis-devel > > %2F2012-February%2F018485.html&ei=f6gFUOykJcWl2AXz2eDKBQ&usg=AFQjCNGxJREc > > 0HrXIbAFuKSfvjU0tbfg9A&sig2=0wH8-t8hCMiuIWKDL6AeFQ) but seems lie it > > should be fixed. > > > > Thanks in advance! > > > > > > Peter > > > > > > -- > > Peter Tittmann > > > > > > > > ___ > > postgis-users mailing list postgis-users@postgis.refractions.net > > (mailto:postgis-users@postgis.refractions.net) > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > -- > Mit freundlichen Grüßen > > Astrid Emde > > --- > Aufwind durch Wissen! > > Qualifizierte OpenSource-Schulungen > bei der www.foss-academy.eu (http://www.foss-academy.eu) > > -- > > Astrid Emde > WhereGroup GmbH & Co.KG > Eifelstraße 7 > 53119 Bonn > Germany > > Fon: +49(0)228 90 90 38 - 19 > Fax: +49(0)228 90 90 38 - 11 > > astrid.e...@wheregroup.com (mailto:astrid.e...@wheregroup.com) > www.wheregroup.com (http://www.wheregroup.com) > Folgen Sie der WhereGroup auf twitter: http://twitter.com/WhereGroup_com > > Amtsgericht Bonn, HRA 6788 > --- > Komplementärin: > WhereGroup Verwaltungs GmbH > vertreten durch: > Olaf Knopp, Peter Stamm > --- > pgp-public key: > http://pgp.mit.edu:11371/pks/lookup?search=0x06DA52D72D515284 > Signierte und/oder verschlüsselte Nachrichten sind sehr willkommen > Signed and/or encrypted mail is highly appreciated > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > (mailto:postgis-users@postgis.refractions.net) > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] spatialreference.org srid insert error
Hello Peter, there is a constraint that checks that srid is < 998999 ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check CHECK (srid > 0 AND srid <= 998999); You should set sird in the insert to 102243 instead of 9102243: INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 102243, 'esri', 102243, '+proj=lcc +lat_1=37.07 +lat_2=38.43 +lat_0=36.5 +lon_0=-120.5 +x_0=200 +y_0=50 +ellps=GRS80 +units=m +no_defs ', 'PROJCS["NAD_1983_HARN_StatePlane_California_III_FIPS_0403",GEOGCS["GCS_North_American_1983_HARN",DATUM["NAD83_High_Accuracy_Regional_Network",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Lambert_Conformal_Conic_2SP"],PARAMETER["False_Easting",200],PARAMETER["False_Northing",50],PARAMETER["Central_Meridian",-120.5],PARAMETER["Standard_Parallel_1",37.07],PARAMETER["Standard_Parallel_2",38.43],PARAMETER["Latitude_Of_Origin",36.5],UNIT["Meter",1],AUTHORITY["EPSG","102243"]]'); Astrid On Tue, July 17, 2012 6:14 pm, Peter Tittmann wrote: > Hi, > > > Im trying to add a srid from spatial reference.org > (http://spatialreference.org/ref/esri/102243/postgis/) but i'm getting a > error using the insert statement: > > ERROR: new row for relation "spatial_ref_sys" violates check constraint > "spatial_ref_sys_srid_check" > > > I saw this ticket > (http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQ > FjAA&url=http%3A%2F%2Ftrac.osgeo.org%2Fpostgis%2Fticket%2F1505&ei=f6gFUOy > kJcWl2AXz2eDKBQ&usg=AFQjCNH1dlCvTvWEYntRmqflCDtAOUT49g&sig2=tch67lIoPp2IO > RZaCyOpSw) and thread > (http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CDgQ > FjAB&url=http%3A%2F%2Fpostgis.refractions.net%2Fpipermail%2Fpostgis-devel > %2F2012-February%2F018485.html&ei=f6gFUOykJcWl2AXz2eDKBQ&usg=AFQjCNGxJREc > 0HrXIbAFuKSfvjU0tbfg9A&sig2=0wH8-t8hCMiuIWKDL6AeFQ) but seems lie it > should be fixed. > > Thanks in advance! > > > Peter > > > -- > Peter Tittmann > > > > ___ > postgis-users mailing list postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- Mit freundlichen Grüßen Astrid Emde --- Aufwind durch Wissen! Qualifizierte OpenSource-Schulungen bei der www.foss-academy.eu -- Astrid Emde WhereGroup GmbH & Co.KG Eifelstraße 7 53119 Bonn Germany Fon: +49(0)228 90 90 38 - 19 Fax: +49(0)228 90 90 38 - 11 astrid.e...@wheregroup.com www.wheregroup.com Folgen Sie der WhereGroup auf twitter: http://twitter.com/WhereGroup_com Amtsgericht Bonn, HRA 6788 --- Komplementärin: WhereGroup Verwaltungs GmbH vertreten durch: Olaf Knopp, Peter Stamm --- pgp-public key: http://pgp.mit.edu:11371/pks/lookup?search=0x06DA52D72D515284 Signierte und/oder verschlüsselte Nachrichten sind sehr willkommen Signed and/or encrypted mail is highly appreciated ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] spatialreference.org srid insert error
Hi, Im trying to add a srid from spatial reference.org (http://spatialreference.org/ref/esri/102243/postgis/) but i'm getting a error using the insert statement: ERROR: new row for relation "spatial_ref_sys" violates check constraint "spatial_ref_sys_srid_check" I saw this ticket (http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQFjAA&url=http%3A%2F%2Ftrac.osgeo.org%2Fpostgis%2Fticket%2F1505&ei=f6gFUOykJcWl2AXz2eDKBQ&usg=AFQjCNH1dlCvTvWEYntRmqflCDtAOUT49g&sig2=tch67lIoPp2IORZaCyOpSw) and thread (http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&ved=0CDgQFjAB&url=http%3A%2F%2Fpostgis.refractions.net%2Fpipermail%2Fpostgis-devel%2F2012-February%2F018485.html&ei=f6gFUOykJcWl2AXz2eDKBQ&usg=AFQjCNGxJREc0HrXIbAFuKSfvjU0tbfg9A&sig2=0wH8-t8hCMiuIWKDL6AeFQ) but seems lie it should be fixed. Thanks in advance! Peter -- Peter Tittmann ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
