pgsql2shp doesnt see SRID

[darkblueb]

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)

[robe]

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:

CREATE TABLE shp_test AS VALUES ('Example', ST_SetSRID(ST_MakePoint(-122.427159, 37.760028), 4236)::geometry(point,4326));

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

pgsql2shp -f test_shapefile.shp "SELECT * from ne3_test" shp_test
 

[darkblueb]

… 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)

[darkblueb]

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

[robe]

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.

[darkblueb]

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.

[robe]

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.

[strk]

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

[strk]

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).

[robe]

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.

[strk]

I can't reproduce the bug with 2.0.2SVN (r10166) Can you, darkblue ?

[strk]

Closing as it seems to work here. The .prj file is correctly created as long as the row is found in spatial_ref_sys.