<div dir="ltr"><div>A simplest nobrainer way:<br><br>create table new_table as (select * from old_table);</div><div>update new_table set geom = ST_Transform(geom, new_srid);</div><div>create index on new_table using gist(geom);<br><br>A more optimized one will be to inline the list all the columns in create table as:</div><div><br></div><div>create table new_table as (select column1, column2, ... , ST_Transform(geom, new_srid) as geom from old_table);</div><div>create index on new_table using gist(geom);</div></div><br><div class="gmail_quote"><div dir="ltr">ср, 9 мая 2018 г. в 19:01, Bryan Hall <<a href="mailto:bryan.hall@chk.com">bryan.hall@chk.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div class="m_-3223749887295629863WordSection1">
<p class="m_-3223749887295629863msipheader9d2ba7ab" align="center" style="text-align:center"><span style="font-size:8.0pt;color:#0078d7">Classification: DCL-Internal</span><u></u><u></u></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><u></u> <u></u></p>
<p class="MsoNormal"><a name="m_-3223749887295629863__MailEndCompose">Alexandre,<u></u><u></u></a></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span>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:<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span>SDO_CS.TRANSFORM_LAYER('LSE_DETAIL_ACTIVE','SHAPE','LDA_NAD27',4267);<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span>This clones the LSE_DETAIL_ACTIVE table to one called LDA_NAD27, while transforming the SHAPE column to SRID 4267.<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<p class="MsoNormal"><span>Bryan<u></u><u></u></span></p>
<p class="MsoNormal"><span><u></u> <u></u></span></p>
<span></span>
<p class="MsoNormal"><b>From:</b> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Alexandre Neto<br>
<b>Sent:</b> Wednesday, May 09, 2018 10:46<br>
<b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
<b>Subject:</b> [EXTERNAL] Re: [postgis-users] Replacement for Oracle's TRANSFORM_LAYER procedure?<u></u><u></u></p></div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-3223749887295629863WordSection1">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">You may need to explain what SDO_CS.TRANSFORM does.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">To transform a table's geometry SRID I use the following:<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal"><span class="m_-3223749887295629863inbox-inbox-comment-copy">ALTER TABLE <table_name> ALTER COLUMN <geom_column_name> TYPE geometry(<geometry_type>, <target_CRS>) USING ST_Transform(</span><span class="m_-3223749887295629863inbox-inbox-inbox-inbox-comment-copy"><geom_column_name></span><span class="m_-3223749887295629863inbox-inbox-comment-copy">,
</span><span class="m_-3223749887295629863inbox-inbox-inbox-inbox-comment-copy"><target_CRS></span><span class="m_-3223749887295629863inbox-inbox-comment-copy">);</span><u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-3223749887295629863WordSection1"><div>
<div>
<p class="MsoNormal">Bryan Hall <<a href="mailto:bryan.hall@chk.com" target="_blank">bryan.hall@chk.com</a>> escreveu no dia quarta, 9/05/2018 às 16:02:<u></u><u></u></p>
</div>
</div></div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-3223749887295629863WordSection1"><div><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt">
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><u></u> <u></u></p>
<div>
<p class="MsoNormal">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.
<span style="font-family:"Segoe UI Emoji",sans-serif">😊</span><u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal">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.<u></u><u></u></p>
<p class="MsoNormal"> <u></u><u></u></p>
<p class="MsoNormal">Thanks, Bryan<u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div class="MsoNormal" align="center" style="text-align:center">
<hr size="2" width="100%" align="center">
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><u></u> <u></u></p>
</div>
</blockquote></div></div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-3223749887295629863WordSection1"><div><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"><p class="MsoNormal">_______________________________________________</p></blockquote></div></div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-3223749887295629863WordSection1"><div><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt"><p class="MsoNormal"><br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="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=" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><u></u><u></u></p></blockquote></div></div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-3223749887295629863WordSection1">
<p class="MsoNormal">-- <u></u><u></u></p>
<div>
<div>
<div>
<p class="MsoNormal">Alexandre Neto<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">---------------------<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">@AlexNetoGeo<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><a href="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=" target="_blank">http://sigsemgrilhetas.wordpress.com</a><u></u><u></u></p>
</div>
<p class="MsoNormal"><a href="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=" target="_blank">http://gisunchained.wordpress.com</a><u></u><u></u></p>
</div>
</div>
</div></div><div lang="EN-US" link="blue" vlink="purple">
<br>
<hr>
<font face="Arial" color="Gray" size="1"><br>
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).</font>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>