[postgis-users] [EXTERNAL] Re: Replacement for Oracle's TRANSFORM_LAYER procedure?

[Darafei "Komяpa" Praliaskouski]

A simplest nobrainer way:

create table new_table as (select * from old_table);
update new_table set geom = ST_Transform(geom, new_srid);
create index on new_table using gist(geom);

A more optimized one will be to inline the list all the columns in create
table as:

create table new_table as (select column1, column2, ... ,
ST_Transform(geom, new_srid) as geom from old_table);
create index on new_table using gist(geom);

ср, 9 мая 2018 г. в 19:01, Bryan Hall <bryan.hall at chk.com>:

> Classification: DCL-Internal
>
>
>
> Alexandre,
>
>
>
> SDO_CS.TRANSFORM_LAYER clones the input table (or MV) to a new table,
> transforming the geometry column specified to the new SRID. For example:
>
>
>
> SDO_CS.TRANSFORM_LAYER('LSE_DETAIL_ACTIVE','SHAPE','LDA_NAD27',4267);
>
>
>
> This clones the LSE_DETAIL_ACTIVE table to one called LDA_NAD27, while
> transforming the SHAPE column to SRID 4267.
>
>
>
> Bryan
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Alexandre Neto
> *Sent:* Wednesday, May 09, 2018 10:46
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* [EXTERNAL] Re: [postgis-users] Replacement for Oracle's
> TRANSFORM_LAYER procedure?
>
>
>
> You may need to explain what SDO_CS.TRANSFORM does.
>
>
>
> To transform a table's geometry SRID I use the following:
>
>
>
> ALTER TABLE <table_name> ALTER COLUMN <geom_column_name> TYPE
> geometry(<geometry_type>, <target_CRS>) USING ST_Transform(
> <geom_column_name>, <target_CRS>);
>
>
>
> Bryan Hall <bryan.hall at chk.com> escreveu no dia quarta, 9/05/2018 às
> 16:02:
>
>
>
> I’m starting to look at the stored procedures we will need to move to
> PostGIS from Oracle, and I don’t see anything like SDO_CS.TRANSFORM_LAYER
> in the manual for a built-in procedure. Yes of course there is
> ST_Transform, but that does it in place, or in-line. However, here we are
> making a new table in a different SRID and it would be rather nice not to
> hard-code the columns in the source table (or MV in this case), or copy the
> table as a whole and then do a clunky in-place update. Yes, I’d rather just
> add another geography column and make it a computed column – but stupid
> ESRI software has to read this data. 😊
>
>
>
> Is there a good site to look for “convenience functions” like this for
> PostGIS? I’d rather not spend time “re-inventing the wheel” if I don’t have
> to.
>
>
>
> Thanks, Bryan
>
>
> ------------------------------
>
>
>
> _______________________________________________
>
>
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.osgeo.org_mailman_listinfo_postgis-2Dusers&d=DwMFaQ&c=M7nMI6M-Uv3ihEfN-QJ3_sQdVexoO64eU4Ftr4lnVk0&r=-89XSUoV_kZKlHf89FQGuyqPyUjA53juyA-7r7GoZVA&m=VJx8UZRUZFpdr1ed5RsLe4AkoOQd6583QJe3ijFDTKI&s=GRSBAMi-gfM8IbXGcO8-kpVRYTqVoNl5C2bc0Ns2VYc&e=>
>
> --
>
> Alexandre Neto
>
> ---------------------
>
> @AlexNetoGeo
>
> http://sigsemgrilhetas.wordpress.com
> <https://urldefense.proofpoint.com/v2/url?u=http-3A__sigsemgrilhetas.wordpress.com&d=DwMFaQ&c=M7nMI6M-Uv3ihEfN-QJ3_sQdVexoO64eU4Ftr4lnVk0&r=-89XSUoV_kZKlHf89FQGuyqPyUjA53juyA-7r7GoZVA&m=VJx8UZRUZFpdr1ed5RsLe4AkoOQd6583QJe3ijFDTKI&s=Qg237IQgPug6UcNiYy0IK1RXInkSfDg09SweWtZAo40&e=>
>
> http://gisunchained.wordpress.com
> <https://urldefense.proofpoint.com/v2/url?u=http-3A__gisunchained.wordpress.com&d=DwMFaQ&c=M7nMI6M-Uv3ihEfN-QJ3_sQdVexoO64eU4Ftr4lnVk0&r=-89XSUoV_kZKlHf89FQGuyqPyUjA53juyA-7r7GoZVA&m=VJx8UZRUZFpdr1ed5RsLe4AkoOQd6583QJe3ijFDTKI&s=sjDQ5sUi7TYoGrSSYxZoCjL3CfUebZ_wTdr9koJG_Ds&e=>
>
> ------------------------------
>
> This email (and attachments if any) is intended only for the use of the
> individual or entity to which it is addressed, and may contain information
> that is confidential or privileged and exempt from disclosure under
> applicable law. If the reader of this email is not the intended recipient,
> or the employee or agent responsible for delivering this message to the
> intended recipient, you are hereby notified that any dissemination,
> distribution or copying of this communication is strictly prohibited. If
> you have received this communication in error, please notify the sender
> immediately by return email and destroy all copies of the email (and
> attachments if any).
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180510/d7b641cf/attachment.html>