[postgis-users] [Partially solved] Re: Upgrade from Postgis 1.5.1-5 to 2.3.1

Thu Jul 18 10:42:27 PDT 2019

Hi,

I don't see your pull request. Can you send over a link to it?

On Thu, Jul 18, 2019 at 8:22 PM Michel Philippenko <michel.ing93 at gmail.com>
wrote:

> Привет Darafei
>
> Sorry for the delay. I pushed my pull request in order to suggest a
> modification of the documentation, as we talked about previously.
>
> You asked me :
>
> - what is IGN  <== I putted a link in the text I proposed.
>
> - why it produces invalid SRIDs <== I'm not expert of cartography and
> cannot explain the technical reason why in old databases using french IGN
> we have SRID out of range. It seems that French IGN had specific system
> about 10 years ago. I putted in my pull request an example of an issue. As
> I seemed to me, these SRID correspond mostly to french islands which are
> far from France (but not only islands also few parts of France itself). The
> warnings I had on my screen mentionned these ones for example : 310024140,
> 310642254, ..., 110600000, 110013000, 110013000, and many others.
>
> - what do you lose by removing them <== I had no more problems when doing
> my hard upgrade of my database :-)  As I could understand, the old SRIDs
> which have been out of range correspond to new SRID which are inside the
> right range ; it seems that the IGN cartography data are still there but
> with another format :
>
> *OLD database :* SELECT srtext FROM spatial_ref_sys where srid=*310423221*
> ;
> returns :
> PROJCS["St Pierre et Miquelon 1950",GEOGCS["St Pierre et Miquelon 1950
> ",DATUM["St Pierre et Miquelon 1950",SPHEROID["Clarke
> 1866",6378206.4000,294.9786982000000,AUTHORITY["IGNF","ELG004"]],TOWGS84[-95.5930,573.7630,173.4420,-0.9602,1.2510,-1.3918,42.626500],AUTHORITY["IGNF","REG423"]],PRIMEM["Greenwich",0.000000000,AUTHORITY["IGNF","LGO01"]],UNIT["degree",0.01745329251994330],AXIS["Longitude",EAST],AXIS["Latitude",NORTH],AUTHORITY["IGNF","STPM50GEO"]],PROJECTION["Transverse_Mercator",AUTHORITY["IGNF","PRC0221"]],PARAMETER["semi_major",6378206.4000],PARAMETER["semi_minor",6356583.8000],PARAMETER["latitude_of_origin",0.000000000],PARAMETER["central_meridian",-57.000000000],PARAMETER["scale_factor",0.99960000],PARAMETER["false_easting",500000.000],PARAMETER["false_northing",0.000],UNIT["metre",1],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["IGNF","STPM50UTM21"]]
>
> *New database after hard upgrade :* SELECT srtext FROM spatial_ref_sys
> where srid=*911423*;
> returns :
> GEOCCS["St Pierre et Miquelon 1950",DATUM["St Pierre et Miquelon
> 1950",SPHEROID["Clarke
> 1866",6378206.4000,294.9786982000000,AUTHORITY["IGNF","ELG004"]],TOWGS84[-95.5930,573.7630,173.4420,-0.9602,1.2510,-1.3918,42.626500],AUTHORITY["IGNF","REG423"]],PRIMEM["Greenwich",0.000000000,AUTHORITY["IGNF","LGO01"]],UNIT["metre",1],AXIS["X",OTHER],AXIS["Y",EAST],AXIS["Z",NORTH],AUTHORITY["IGNF","STPM50"]]
>
> Thank you for your attention,
> with best regards,
> Mikhael
>
>
> Le sam. 10 nov. 2018 à 19:11, Darafei "Komяpa" Praliaskouski <
> me at komzpa.net> a écrit :
>
>> Привет,
>>
>> That chapter is part of PostGIS documentation which is part of PostGIS
>> itself. You can edit here:
>> https://github.com/postgis/postgis/blob/svn-trunk/doc/installation.xml
>>
>> (just push [Edit], do your changes and [Save] -> [New pull request]).
>>
>> Please also mention what is IGN and why it produces invalid SRIDs, and
>> what do you lose by removing them. :)
>>
>> пт, 9 нояб. 2018 г. в 19:24, Michel Philippenko <michel.ing93 at gmail.com>:
>>
>>> Hello,
>>> I have managed to avoid errors in relation with
>>> spatial_ref_sys_srid_check and I asked the administrators of the website
>>> postgis.net to add some information in this page :
>>> https://postgis.net/docs/postgis_installation.html#hard_upgrade
>>> in the chapter :
>>> "Some custom records of spatial_ref_sys in dump file have an invalid
>>> SRID value"
>>>
>>> Information to insert :
>>> "Typically, if you use the french system IGN, you will have problems
>>> with SRID values out of range. You need first to throw out completely the
>>> IGN from the sql which is resulting from postgis_restore.pl. So, first
>>> run :
>>> perl utils/postgis_restore.pl "/somepath/olddb.backup" > oldbb.sql
>>> then
>>> grep -v IGNF olddb.sql > olddb-without-IGN.sql
>>> then create your newdb, activate the required Postgis extensions, and
>>> insert properly the french system IGN with this script :
>>>
>>> https://raw.githubusercontent.com/Remi-C/IGN_spatial_ref_for_PostGIS/master/Put_IGN_SRS_into_Postgis.sql
>>> then, to finish, run :
>>> psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql
>>> 2> errors.txt
>>> "
>>> Thank you very much for you attention,
>>> With best regards,
>>> Mikhael Philippenko.
>>>
>>> Le jeu. 8 nov. 2018 à 14:55, Michel Philippenko <michel.ing93 at gmail.com>
>>> a écrit :
>>>
>>>> Hello,
>>>> I'm new with Postgis.
>>>> I'm upgrading a database from Postgis 1.5.1-5 to 2.3.1 following the
>>>> instructions given on this web page :
>>>> https://postgis.net/docs/postgis_installation.html#hard_upgrade.
>>>> The errors below are displayed when importing my dump. Will my DB work
>>>> well ? Should I fight more to have no errors at all ?
>>>> Thank you for your help and your attention.
>>>> With best regards,
>>>> Mikhael.
>>>>
>>>> ERREUR:  la famille d'opérateur « btree_geography_ops » existe déjà
>>>> pour la méthode d'accès « btree »
>>>> ERREUR:  la famille d'opérateur « btree_geometry_ops » existe déjà pour
>>>> la méthode d'accès « btree »
>>>> ERREUR:  la famille d'opérateur « gist_geography_ops » existe déjà pour
>>>> la méthode d'accès « gist »
>>>> ERREUR:  la contrainte de vérification « spatial_ref_sys_srid_check »
>>>> est rompue par une ligne
>>>> ERREUR:  n'a pas pu créer l'index unique « spatial_ref_sys_pkey »
>>>> DÉTAIL : La clé (srid)=(999954) est dupliquée.
>>>>
>>>> My translation in English :
>>>> ERROR:  the operator family « btree_geography_ops » already exists for
>>>> the access method « btree »
>>>> ERROR:  the operator family « btree_geometry_ops »already exists for
>>>> the access method « btree »
>>>> ERROR:  the operator family « gist_geography_ops » already exists for
>>>> the access method « gist »
>>>> ERROR:  the constraint check « spatial_ref_sys_srid_check » is broken
>>>> for one line
>>>> ERROR:  could not create the uniq index « spatial_ref_sys_pkey »
>>>> DETAIL : The key (srid)=(999954) is duplicated.
>>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> --
>> Darafei Praliaskouski
>> Support me: http://patreon.com/komzpa
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190718/ecae98bd/attachment.html>