Opened 7 years ago

Closed 6 years ago

Last modified 6 years ago

#1759 closed defect (fixed)

shp2pgsql st_transform not unique requires casting

Reported by: robe Owned by: mcayland
Priority: blocker Milestone: PostGIS 2.0.1
Component: loader/dumper Version: 2.0.x
Keywords: Cc:

Description

shp2pgsql is emitting code like this

ST_Transform('0101000020E61000009A081B9E5E9557C0006F8104C51F4440', 900913)

If you use ST_Transform. However if you have raster installed in your db, you get an ST_Transform is not unique notice, because raster also has an ST_Transform function.

This needs to be changed to

ST_Transform('0101000020E61000009A081B9E5E9557C0006F8104C51F4440'::geometry, 900913);

See user posting: http://postgis.refractions.net/pipermail/postgis-users/2012-April/033534.html

Attachments (1)

shp2pgsql-transform.patch (788 bytes) - added by mcayland 7 years ago.
Fix ST_Transform for shp2pgsql

Download all attachments as: .zip

Change History (9)

Changed 7 years ago by mcayland

Attachment: shp2pgsql-transform.patch added

Fix ST_Transform for shp2pgsql

comment:1 Changed 7 years ago by mcayland

Component: postgisloader/dumper
Owner: changed from pramsey to mcayland

How about the attached patch which adds explicit casts for both geometry/geography and passes regression for me?

comment:2 Changed 7 years ago by robe

Works for me. Fill free to commit.

comment:3 Changed 7 years ago by robe

Resolution: fixed
Status: newclosed

Mark, Hope you don't mind I've committed at r9621 . I think this is an important fix and I would like to get out a 2.0.1 experimental windows build out sometime soon.

comment:4 Changed 7 years ago by robe

Just a note -- I forgot to check the geography type to see if this works. We don't have an ST_Transform for geography so it may not work. I'll reopen if it doesn't work for geography.

comment:5 Changed 7 years ago by mcayland

Okay - I was away at my parents over Easter and their WiFi? doesn't always work that well, so struggling to get internet access. The cast should be fine - for the first version of the patch, I added the cast inside ST_Transform() which tripped up one of the regression tests (thanks to strk for adding this for the new feature!) which is why the explicit cast now wraps everything.

comment:6 Changed 6 years ago by robe

Resolution: fixed
Status: closedreopened

Evidentally I missed testing this on geography:

I tested with this command:

%SHP2PGSQL% -c -s 4269:4326 -G -g geog  -W "latin1" tl_2010_42_zcta510 tiger_staging.zcta510_42 > zip.sql

And it needs to cast the geometry first before transforming so I guess you need to make the same change for geography.

Note:

It produces

ST_Transform('gubblygook',4326)::geography

and needs to produce:

ST_Transform('gubblygook'::geometry,4326)::geography

comment:7 Changed 6 years ago by robe

Resolution: fixed
Status: reopenedclosed

Fixed for trunk at r9965 and 2.0 at r9964

Mark et. al feel free to make further changes if you are not satisfied with my fix.

comment:8 Changed 6 years ago by mcayland

Looks fine to me. I was just about to post that I couldn't reproduce this, however I see that I didn't have rtpostgis.sql loaded into my test database which is why I didn't pick up on this before.

Note: See TracTickets for help on using tickets.