Hi all, After several tries, I am still unable to get this feature to work within Postgis 2.0... And with PostgreSQL 9.2, it is no more possible to downgrade to Postgis 1.5... I am completely locked.
I try, once again, to give the steps to reproduce my bug: 1/ insert spatial_ref_sys entries which used to work in Postgis 1.5 : insert into spatial_ref_sys values (320002120,'IGNF',320002120,'PROJCS["Lambert II étendu",GEOGCS["Nouvelle Triangulation Française Paris grades",DATUM["NTF",SPHEROID["Clarke 1880 IGN",6378249.2000,293.4660210000000,AUTHORITY["IGNF","ELG010"]],TOWGS84[-168.0000,-60.0000,320.0000,0,0,0,0],AUTHORITY["IGNF","REG002"]],PRIMEM["Paris",2.337229167,AUTHORITY["IGNF","LGO02"]],UNIT["grad",0.01570796326794897],AXIS["Longitude",EAST],AXIS["Latitude",NORTH],AUTHORITY["IGNF","NTFP"]],PROJECTION["Lambert_Conformal_Conic_1SP",AUTHORITY["IGNF","PRC0120"]],PARAMETER["semi_major",6378249.2000],PARAMETER["semi_minor",6356515.0000],PARAMETER["latitude_of_origin",46.800000000],PARAMETER["central_meridian",0.000000000],PARAMETER["scale_factor",0.99987742],PARAMETER["false_easting",600000.000],PARAMETER["false_northing",2200000.000],UNIT["metre",1],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["IGNF","LAMBE"]]','+init=IGNF:LAMBE +title=Lambert II etendu +proj=lcc +nadgrids=ntf_r93.gsb,null +wktext +towgs84=-168.0000,-60.0000,320.0000 +a=6378249.2000 +rf=293.4660210000000 +pm=2.337229167 +lat_0=46.800000000 +lon_0=0.000000000 +k_0=0.99987742 +lat_1=46.800000000 +x_0=600000.000 +y_0=2200000.000 +units=m +no_defs'); insert into spatial_ref_sys values (310024140,'IGNF',310024140,'PROJCS["Lambert 93",GEOGCS["Réseau géodésique français 1993",DATUM["Réseau géodésique français 1993",SPHEROID["IAG GRS 1980",6378137.0000,298.2572221010000,AUTHORITY["IGNF","ELG037"]],TOWGS84[0.0000,0.0000,0.0000,0,0,0,0],AUTHORITY["IGNF","REG024"]],PRIMEM["Greenwich",0.000000000,AUTHORITY["IGNF","LGO01"]],UNIT["degree",0.01745329251994330],AXIS["Longitude",EAST],AXIS["Latitude",NORTH],AUTHORITY["IGNF","RGF93G"]],PROJECTION["Lambert_Conformal_Conic_2SP",AUTHORITY["IGNF","PRC0140"]],PARAMETER["semi_major",6378137.0000],PARAMETER["semi_minor",6356752.3141],PARAMETER["latitude_of_origin",46.500000000],PARAMETER["central_meridian",3.000000000],PARAMETER["standard_parallel_1",44.000000000],PARAMETER["standard_parallel_2",49.000000000],PARAMETER["false_easting",700000.000],PARAMETER["false_northing",6600000.000],UNIT["metre",1],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["IGNF","LAMB93"]]','+init=IGNF:LAMB93'); 2/ This raises an when adding a geometry column : "ERROR: AddGeometryColumn() - SRID must be <= 998999" 3/ I reaffect a neuw SRID to those 2 entries : insert into spatial_ref_sys values (910002,'IGNF',320002120,'PROJCS["Lambert II étendu",GEOGCS["Nouvelle Triangulation Française Paris grades",DATUM["NTF",SPHEROID["Clarke 1880 IGN",6378249.2000,293.4660210000000,AUTHORITY["IGNF","ELG010"]],TOWGS84[-168.0000,-60.0000,320.0000,0,0,0,0],AUTHORITY["IGNF","REG002"]],PRIMEM["Paris",2.337229167,AUTHORITY["IGNF","LGO02"]],UNIT["grad",0.01570796326794897],AXIS["Longitude",EAST],AXIS["Latitude",NORTH],AUTHORITY["IGNF","NTFP"]],PROJECTION["Lambert_Conformal_Conic_1SP",AUTHORITY["IGNF","PRC0120"]],PARAMETER["semi_major",6378249.2000],PARAMETER["semi_minor",6356515.0000],PARAMETER["latitude_of_origin",46.800000000],PARAMETER["central_meridian",0.000000000],PARAMETER["scale_factor",0.99987742],PARAMETER["false_easting",600000.000],PARAMETER["false_northing",2200000.000],UNIT["metre",1],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["IGNF","LAMBE"]]','+init=IGNF:LAMBE +title=Lambert II etendu +proj=lcc +nadgrids=ntf_r93.gsb,null +wktext +towgs84=-168.0000,-60.0000,320.0000 +a=6378249.2000 +rf=293.4660210000000 +pm=2.337229167 +lat_0=46.800000000 +lon_0=0.000000000 +k_0=0.99987742 +lat_1=46.800000000 +x_0=600000.000 +y_0=2200000.000 +units=m +no_defs'); insert into spatial_ref_sys values (910001,'IGNF',310024140,'PROJCS["Lambert 93",GEOGCS["Réseau géodésique français 1993",DATUM["Réseau géodésique français 1993",SPHEROID["IAG GRS 1980",6378137.0000,298.2572221010000,AUTHORITY["IGNF","ELG037"]],TOWGS84[0.0000,0.0000,0.0000,0,0,0,0],AUTHORITY["IGNF","REG024"]],PRIMEM["Greenwich",0.000000000,AUTHORITY["IGNF","LGO01"]],UNIT["degree",0.01745329251994330],AXIS["Longitude",EAST],AXIS["Latitude",NORTH],AUTHORITY["IGNF","RGF93G"]],PROJECTION["Lambert_Conformal_Conic_2SP",AUTHORITY["IGNF","PRC0140"]],PARAMETER["semi_major",6378137.0000],PARAMETER["semi_minor",6356752.3141],PARAMETER["latitude_of_origin",46.500000000],PARAMETER["central_meridian",3.000000000],PARAMETER["standard_parallel_1",44.000000000],PARAMETER["standard_parallel_2",49.000000000],PARAMETER["false_easting",700000.000],PARAMETER["false_northing",6600000.000],UNIT["metre",1],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["IGNF","LAMB93"]]','+init=IGNF:LAMB93'); 4/ Performing a ST_Transform fails : select xl, yl, ST_X(geom)::INT AS xl93, ST_Y(geom)::INT AS yl93 from ( select *, ST_Transform(ST_SetSRID(ST_MakePoint(xl, yl), 910143), 910142) AS geom from (values (556866, 1902345) , (689174, 2448195) , (729095, 2528577) , (335755, 2059702) , (623207, 2210468) , (850337, 1871486) , (915275, 1889534) , (639551, 1778327) , (584365, 2273523) , (649599, 2228833)) as t(xl, yl) )g order by xl; Gives : " PostGIS was unable to transform the point because either no grid shift files were found, or the point does not lie within the range for which the grid shift is defined. Refer to the ST_Transform() section of the PostGIS manual for details on how to configure PostGIS to alter this behaviour." I repeat that this conversion works good under proj4 : cs2cs -I +init=IGNF:LAMBE +to +init=IGNF:LAMB93 I have tried to specify the proj4text of IGNF:LAMBE with its nadgrids, but I get the same result. Any help would be appreciated. Thanks in advance, Cedric -----Message d'origine----- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de DUPREZ Cédric Envoyé : jeudi 19 juillet 2012 09:22 À : PostGIS Users Discussion Objet : Re: [postgis-users] Coordinate transformationwithcustomrecordsofspatial_sys_ref Thank you for your help. I have modified the SRIDs of each entry in the IGNF file, by incrementing them from 910001 to 910320. But now, when I try the conversion with ST_Transform, I get the following error message: "transform: couldn't project point (556866 1.90234e+06 0): failed to load datum shift file (-38)" With the following notice: "PostGIS was unable to transform the point because either no grid shift files were found, or the point does not lie within the range for which the grid shift is defined. Refer to the ST_Transform() section of the PostGIS manual for details on how to configure PostGIS to alter this behaviour." I tried the conversion directly in Proj4 with cs2cs and it works correctly. This should mean that Proj.4 is build with the grid-shift files, as it is said in the ST_Transform documentation. Here is the command I execute in Proj4 : cs2cs -I +init=IGNF:LAMBE +to +init=IGNF:LAMB93 Then, giving Lambert II coordinates transform them correctly. But in Postgis, it does not work. What am I doing wrong? Regards, Cedric -----Message d'origine----- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de robert_cl...@doh.state.fl.us Envoyé : mardi 17 juillet 2012 14:49 À : postgis-users@postgis.refractions.net Objet : Re: [postgis-users] Coordinate transformation withcustomrecordsofspatial_sys_ref One easy way to generate the syntax required to add a custom projection is to select something similar at spatialreference.org and click "PostGIS spatial_ref_sys INSERT statement," then copy and paste into a text or query editor to revise. Blinding flash of the obvious, perhaps, but hope it's helpful to someone. -Rob -----Original Message----- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Sandro Santilli Sent: Tuesday, July 17, 2012 5:49 AM To: DUPREZ Cédric Cc: PostGIS Users Discussion Subject: Re: [postgis-users] Coordinate transformation with customrecordsofspatial_sys_ref On Tue, Jul 17, 2012 at 11:34:46AM +0200, DUPREZ Cédric wrote: > SELECT AddGeometryColumn('public', 'pts', 'geom', 320002120, 'POINT', > 2, false); > > This gives me the following error message: "ERROR: AddGeometryColumn() > - SRID must be <= 998999 > > How can I use the correct 320002120 SRID specified by French IGN? Is 320002120 in your spatial_ref_sys already ? You'll need to assign it a different SRID, the spatial_ref_sys has provision for "auth_srid" so you can put 320002120 in there and use something valid for PostGIS' "srid" field. Unfortunately we never got around writing a page about SRID ranges in PostGIS manual (contributions welcome!) but the idea is that you put your "custom" entries somewhere from 910000 and below 999000. --strk; ,------o-. | __/ | Delivering high quality PostGIS 2.1 | / 2.1 | http://strk.keybit.net - http://vizzuality.com `-o------' _______________________________________________ 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 _______________________________________________ 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
