OSGeo
Option to suppress creating a sequence when converting to PostgreSQL/PostGIS format
Expected behavior and actual behavior.
The desired behavior is that the primary key in new postgresql/postgis tables is not a serial type. There should be an option to instead create an integer type for the row primary key and suppress the generation of a sequence. My guess is that 99.99% of the time, GIS layers that are inserted into a database using ogr2ogr are never grown and so there is no reason to clutter the database with extra sequences.
Steps to reproduce the problem.
ogr2ogr PG:dbname=testdb testlayer.gpkg
Operating system
All
GDAL version and provenance
All
I have never seen a similar wish before but maybe users have not been thinking about it. What concrete problems come from perhaps unnecessary sequences? I think I could use the option for my private use sometimes but I would not like to explain for other users in my organization that they can edit the table but they can't add new features for example by splitting a geometry. Have you considered to use the PGDump driver https://gdal.org/drivers/vector/pgdump.html and edit the CREATE TABLE?
I'm not suggesting it be the default.
Anyway, I have to use the postgis loader as ogr does not do the right thing with many of the polygons that I am loading.
On Sat, Oct 28, 2023 at 2:47 PM Jukka Rahkonen @.***> wrote:
I have never seen a similar wish before but maybe users have not been thinking about it. What concrete problems come from perhaps unnecessary sequences? I think I could use the option for my private use sometimes but I would not like to explain for other users in my organization that they can edit the table but they can't add new features for example by splitting a geometry. Have you considered to use the PGDump driver https://gdal.org/drivers/vector/pgdump.html and edit the CREATE TABLE?
— Reply to this email directly, view it on GitHub https://github.com/OSGeo/gdal/issues/8624#issuecomment-1783907642, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEQXSJ6IFBNRTKPAG35EZTYBVOMTAVCNFSM6AAAAAA6TNB3CSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBTHEYDONRUGI . You are receiving this because you authored the thread.Message ID: @.***>
as ogr does not do the right thing with many of the polygons that I am loading.
details?
I would have to verify, but I believe some of the polygons from this dataset load fine with shp2pgsql, but are rejected by ogr2ogr. https://data.tnris.org/a3c04828-1036-481b-b1e0-bb456c3cbcf4/resources/stratmap21-landparcels_48427_lp.zip
On Sun, Oct 29, 2023 at 11:27 AM Even Rouault @.***> wrote:
as ogr does not do the right thing with many of the polygons that I am loading.
details?
— Reply to this email directly, view it on GitHub https://github.com/OSGeo/gdal/issues/8624#issuecomment-1784159215, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEQXSKVT5UUBOSSDNDPRR3YBZYWNAVCNFSM6AAAAAA6TNB3CSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBUGE2TSMRRGU . You are receiving this because you authored the thread.Message ID: @.***>
I believe that I got a good result with command
ogr2ogr PG:"host=localhost port=5432 dbname=dbname user=user password=password" -nln parceltest -nlt promote_to_multi -lco precision=NO -makevalid stratmap21-landparcels_48427_starr_202101.shp
-nlt promote_to_multi is needed because the dataset contains both polygons and multipolygons
-lco precision=NO is needed for avoiding this error
ERROR: numeric field overflow
DETAIL: A field with precision 18, scale 11 must round to an absolute value less than 10^7.
CONTEXT: COPY parceltest, line 14568, column shape_area: "11790206.31399999931"
-makevalid is good to have because ESRI writes some sort of polygons in a way that makes a topology error by the OGC rules. Without parameter the geometries are written as-is and PostGIS finds topology errors later.
With the command above I get 32844 features into PostGIS and this PostGIS query does not find topology errors
select ogc_fid, ST_IsValidReason(wkb_geometry) from parceltest
where ST_Isvalid(wkb_geometry)=false;
Ah, terrific! Thanks. I thought I had tried all of these switches. Sorry for the diversion.
On Tue, Oct 31, 2023 at 2:41 AM Jukka Rahkonen @.***> wrote:
I believe that I got a good result with command
ogr2ogr PG:"host=localhost port=5432 dbname=dbname user=user password=password" -nln parceltest -nlt promote_to_multi -lco precision=NO -makevalid stratmap21-landparcels_48427_starr_202101.shp
-nlt promote_to_multi is needed because the dataset contains both polygons and multipolygons -lco precision=NO is needed for avoiding this error
ERROR: numeric field overflow DETAIL: A field with precision 18, scale 11 must round to an absolute value less than 10^7. CONTEXT: COPY parceltest, line 14568, column shape_area: "11790206.31399999931"
-makevalid is good to have because ESRI writes some sort of polygons in a way that makes a topology error by the OGC rules. Without parameter the geometries are written as-is and PostGIS finds topology errors later. [image: image] https://user-images.githubusercontent.com/1751612/279287642-39af2166-41e5-4698-9c5f-3b8861b03514.png
With the command above I get 32844 features into PostGIS and this PostGIS query does not find topology errors
select ogc_fid, ST_IsValidReason(wkb_geometry) from parceltest where ST_Isvalid(wkb_geometry)=false;
— Reply to this email directly, view it on GitHub https://github.com/OSGeo/gdal/issues/8624#issuecomment-1786660120, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEQXSKF3UGYVY7NRJTKUATYCCTTDAVCNFSM6AAAAAA6TNB3CSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOBWGY3DAMJSGA . You are receiving this because you authored the thread.Message ID: @.***>
Taking my liberty to close this as wontfix as I don't think the value of complicated the software to offer such an option is worth the benefits