#2417 closed defect (fixed)
pgsql2shp doesnt see SRID
Reported by: | darkblueb | Owned by: | strk |
---|---|---|---|
Priority: | medium | Milestone: | PostGIS 2.2.0 |
Component: | utils/pgsql2shp | Version: | 2.0.x |
Keywords: | srid, pgsql2shp | Cc: |
Description
using 2.1rc2, pgsql2shp does not see SRID in a trivial dump
dbb@xps-8300:~$ psql ne3_test psql (9.2.4) Type "help" for help. ne3_test=# CREATE TABLE shp_test AS VALUES ('Example', ST_SetSRID(ST_MakePoint(-122.427159, 37.760028), 4236)); SELECT 1 ne3_test=# \d shp_test Table "public.shp_test" Column | Type | Modifiers ---------+----------+----------- column1 | text | column2 | geometry | ne3_test=# select column1, st_asewkt(column2) from shp_test ; column1 | st_asewkt ---------+---------------------------------------- Example | SRID=4236;POINT(-122.427159 37.760028) (1 row) ne3_test=# select st_isvalid(column2) from shp_test ; st_isvalid ------------ t (1 row) ne3_test=# \q dbb@xps-8300:~$ /home/shared/srcs_xps8300/postgis-2.1.0rc2/loader/pgsql2shp -f test_shapefile.shp ne3_test shp_test Initializing... Done (postgis major version: 2). Output shape: Point Dumping: X [1 rows]. dbb@xps-8300:~$ ogrinfo test_shapefile.shp INFO: Open of `test_shapefile.shp' using driver `ESRI Shapefile' successful. 1: test_shapefile (Point) dbb@xps-8300:~$ ogrinfo test_shapefile.shp -al INFO: Open of `test_shapefile.shp' using driver `ESRI Shapefile' successful. Layer name: test_shapefile Geometry: Point Feature Count: 1 Extent: (-122.427159, 37.760028) - (-122.427159, 37.760028) Layer SRS WKT: GEOGCS["Hu Tzu Shan 1950", DATUM["Hu_Tzu_Shan_1950", SPHEROID["International 1924",6378388,297, AUTHORITY["EPSG","7022"]], TOWGS84[-637,-549,-203,0,0,0,0], AUTHORITY["EPSG","6236"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.0174532925199433, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4236"]] COLUMN1: String (7.0) OGRFeature(test_shapefile):0 COLUMN1 (String) = Example POINT (-122.427159 37.760028)
Change History (16)
comment:1 by , 11 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:2 by , 11 years ago
Resolution: | wontfix |
---|---|
Status: | closed → reopened |
… since you didn't make it a typed geometry, the srid will be 0…
the SRID is embedded in the geometry
ne3_test=# select column2 from shp_test ; column2 ---------------------------------------------------- 01010000208C1000004BCCB392569B5EC0AB05F69848E14240 (1 row) ne3_test=# select st_asewkt('01010000208C1000004BCCB392569B5EC0AB05F69848E14240'::geometry); st_asewkt ---------------------------------------- SRID=4236;POINT(-122.427159 37.760028) (1 row)
comment:3 by , 11 years ago
no disrespect intended, but that explanation doesnt make much sense… Someone could close this ticket again as "wont fix", sure but at least acknowledge that its not entirely consistent
comment:4 by , 11 years ago
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
darkblueb,
the point is that it will cause more damage to fix than just let it go. Your example is a small table, but imagine a huge table with mixed srids. The routine would have to check each record.
People should not be making untyped geometry tables if they expect an typed shapefile to come out of it is my point. I'm happy to mark this as a documentation bug and explain the issue better but don't see the need to fix it since it is by design.
The only exception is with an SQL query which is the case where the routine does need to compute it.
comment:5 by , 11 years ago
Component: | postgis → documentation |
---|---|
Milestone: | PostGIS 2.1.0 → PostGIS 2.0.4 |
Resolution: | wontfix |
Status: | closed → reopened |
comment:6 by , 11 years ago
Milestone: | PostGIS 2.0.4 → PostGIS Future |
---|
comment:7 by , 11 years ago
another aspect to this is.. Using the general technique of
CREATE TABLE new_table AS SELECT pkey, ST_SetSRID( ST_Transform( geom, 4326), 4326) FROM existing_valid_table;
new_table's geometry is internally marked SRID=4326
(via lwgeom_set_srid()
but new_table geom has no typemod,
so new_table geom does not appear in geometry_columns, or
generally behave as expected.
A second step, explicitly calling UpdateGeometrySRID( new_table, geom, 4326)
sets things right.
An general improvement would to create some single SQL line that would fit into the CREATE TABLE as SELECT .. mold and document it.
comment:8 by , 11 years ago
Milestone: | PostGIS Future → PostGIS 2.1.1 |
---|
How about this
CREATE TABLE new_table AS SELECT pkey, ST_Transform( geom, 4326)::geometry(POINT,4326) As geom FROM existing_valid_table;
Does it in one step and registers correctly.
You could also just pass an sql statement and avoid the whole create table step as I noted already. If you used an SQL statement it would register right because it wouldn't find any answer in geometry_columns so as I stated before this should work just fine.
pgsql2shp -f test_shapefile.shp "SELECT * from ne3_test" shp_test
If you can put together a documentation patch to save me the trouble of explaining this that would be really swell.
comment:9 by , 11 years ago
Owner: | changed from | to
---|---|
Status: | reopened → new |
comment:10 by , 11 years ago
If this is not a regression it's ok not to call it a bug, but I think we should try our best to read SRID from values if not constrained in typmod or otherwise. I'm fighting for the same behavior in GDAL: http://trac.osgeo.org/gdal/ticket/5131
comment:11 by , 11 years ago
A note about the performance hit: pgsql2shp already has to scan the whole result set, I don't see any major performance hit in reading and checking SRID for compatibility. Mixed SRID could be WARNED about with the consequence of producing no .prj file at all (or of bailing out).
comment:12 by , 11 years ago
Component: | documentation → loader/dumper |
---|---|
Owner: | changed from | to
strk,
I'm switching this ticket over to you since you feel so strongly about it. Like I said the only time where this is an issue is if you load up a table that has no constraints OR typmod, which would result in a table incorrectly registered in geometry_columns or of mixed SRID. If it finds no record in geometry_columns (such as the case with passing an SQL statement, it already inspects it.
So for me I have no itch here to scratch.
comment:14 by , 11 years ago
Milestone: | PostGIS 2.1.1 → PostGIS 2.2.0 |
---|
comment:15 by , 9 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
Closing as it seems to work here. The .prj file is correctly created as long as the row is found in spatial_ref_sys.
comment:16 by , 11 months ago
Component: | utils/shapefile-loader-dumper → utils/pgsql2shp |
---|
This affects 2.0 as well I think, but I don't want to fix it this round and not sure I want to fix it so I'm going to mark as a won't fix.
The issue is that now that geometry_columns contains all tables (registered), it will look in the geometry_columns table for the srid and since you didn't make it a typed geometry, the srid will be 0 and wil be found. If I were to fix this issue it would make the dump slower for large tables taht are untyped since it wil fal into teh DISTINCT check trap. I think to resolve, just best to follow best practice rather than change this behavior.
Try creating your table as follows:
and I bet it will work as you expect.
or if you really want the srid, then use SQL so it won't find the wrong answer in geometry_columns