<!DOCTYPE html><html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css">body { font-family:'Times New Roman'; font-size:13px}</style>
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class=""><div>Paul,</div><div><br></div><div>I can't find a suitable gnomic srid for PostGIS.</div><div><br></div><div>I have tried this:</div><div><br></div><div>INSERT INTO spatial_ref_sys (srid, proj4text) VALUES (55000, '+proj=gnom +lat_0=90 +lon_0=-7.646288');<br></div><div><br></div><div>but I get this when I use ST_Transform.</div><div><br></div><div> UPDATE data.osm_county SET geom55000 = ST_Transform(geog4326::geometry,55000);<br></div><div><br></div><div>
<span style="color: rgb(34, 34, 34); font-family: "Source Code Pro"; font-size: 12.95px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: pre-wrap; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">AddToPROJ4SRSCache: could not parse proj4 string '+proj=gnom +lat_0=90 +lon_0=-7.646288' major axis or radius = 0 or not given</span>
</div><div><span style="color: rgb(34, 34, 34); font-family: "Source Code Pro"; font-size: 12.95px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: pre-wrap; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;"><br></span></div><div>I understand your position about things being hard. </div><div><br></div><div>At the moment I would be satisfied with a way of taking OSM data and successfully (aka no topological errors) projecting it so that I can achieve what I need to do the projected geometry data.</div><div><br></div><div>regards</div><div>Simon</div><div><br></div><div>On Tue, 22 Jan 2019 03:41:21 +1100, Paul Ramsey <pramsey@cleverelephant.ca> wrote:<br></div><br><blockquote style="margin: 0 0 0.80ex; border-left: #0000FF 2px solid; padding-left: 1ex">If it’s just Ireland, transform to a good Irish projection after casting to geometry. For maximum awsomeness, transform to gnomonic, in which great circles are represented as straight lines… then even all the introduced points will be at the correct geographic locations. Works for smaller areas, like Ireland.<div class=""><br class=""></div><div class="">P<br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Jan 20, 2019, at 9:23 PM, Simon Greener <<a href="mailto:simon@spatialdbadvisor.com" class="">simon@spatialdbadvisor.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class="">Folks,</div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><br class=""></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class="">I'm in a situation where I need to ST_Union or ST_Collect some osm_county Polygon (not MultiPolygon) data for Ireland.</div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><br class=""></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class="">Now, because ST_Union or ST_Collect do not support geography, I cast to geometry before calling.</div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><br class=""><font face="'Courier New',monospace" class=""><strong class="">select min(a.osm_id) as osm_id,<span class="Apple-converted-space"> </span><br class=""> a.name,<br class=""> count(*) as parts, <br class=""> ST_Union(a.geog4326::geometry)::geography as geog4326 -- or ST_Collect<br class="">from data.osm_county as a<br class="">group by a.name;</strong></font></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><strong class=""><br class=""></strong></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><big class="">Whence I get this:</big></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><strong class=""><br class="">ERROR: lwgeom_area_spher(oid) returned area < 0.0<br class=""></strong></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><strong class=""><br class=""></strong></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><big class="">Investigating I get results like this:</big></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><strong class=""><br class="">select distinct st_isvalidreason(a.geog4326::geometry) from data.osm_county as a;</strong></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><strong class=""><br class="">"Hole lies outside shell[-10.2589459 53.9746452]"<br class="">etc</strong></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><strong class=""><br class=""></strong><big class="">I guess this is expected because geodetic lines in the source geography are being treated as straight in the cast'd geometry.</big><strong class=""><br class=""><br class=""></strong><big class="">If I use ST_Transform to project a 4326 poly to a 3857 and then call the ST_Union aggregate, or identify a single geography that has the invalidity and execute a self-union, I get the following in both situations.</big><strong class=""><br class=""><br class="">ERROR: GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: Hole lies outside shell at or near point -1148162.9982628345 7095296.1166736316 at -1148162.9982628345 7095296.1166736316<br class=""><br class=""></strong>I can't for the life of me work out how to complete the aggregated ST_Union on the 4326 geography data.<strong class=""><br class=""></strong></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><br class=""></div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class="">Anyone point out what I am doing wrong or give me a pointer to what I can do to achieve the aggregated union?</div><div style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""> </div><div id="M2Signature" style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><div class=""><div class="">Regards<br class=""></div><div class=""><font face="Kunstler Script" class=""><big class=""><big class=""><big class=""><big class="">Simon</big></big></big></big></font><em class=""><strong class=""><br class=""></strong></em></div><div class=""><em class=""><strong class="">--------------------------------------------------------------------------------------------------------<br class=""></strong></em></div><div class=""><em class=""><strong class="">Spatial Advice & Solutions Architecture<br class="">Database Spatial Stored Procedure Designer</strong></em><br class="">Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME<br class="">Awarded "2011 Oracle Spatial Excellence Award for Education and Research"<br class="">A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust<br class="">W:<span class="Apple-converted-space"> </span><a href="http://www.spdba.com.au/" class="">www.spdba.com.au</a><br class="">E:<span class="Apple-converted-space"> </span><a href="mailto:simon@spdba.com.au" class="">simon@spdba.com.au</a><br class="">V: +61<span class="Apple-converted-space"> </span><strong class="">362 396 397</strong><br class="">M: +61<span class="Apple-converted-space"> </span><strong class="">418 396 391</strong><br class="">GITC Supplier: T1005<br class="">Skype: sggreener<br class="">Long: 147.20515 (147° 12' 18" E)<br class="">Lat: -43.01530 (43° 00' 55" S)<br class="">GeoHash: r22em9r98wg<br class="">NAC:W80CK 7SWP3</div></div></div><span style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">_______________________________________________</span><br style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><span style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">postgis-users mailing list</span><br style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><a href="mailto:postgis-users@lists.osgeo.org" style="font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">postgis-users@lists.osgeo.org</a><br style="caret-color: rgb(0, 0, 0); font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" style="font-family: "Times New Roman"; font-size: 13px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div></blockquote></div><br class=""></div></blockquote><br><br><br><div id="M2Signature"><div>-- </div><div><div>Regards<br></div><div><font face="Kunstler Script"><big><big><big><big>Simon</big></big></big></big></font><em><strong><br></strong></em></div><div><em><strong>--------------------------------------------------------------------------------------------------------<br></strong></em></div><div><em><strong>Spatial Advice & Solutions Architecture<br>Database Spatial Stored Procedure Designer</strong></em><br>Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME<br>Awarded "2011 Oracle Spatial Excellence Award for Education and Research"<br>A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust<br>W: www.spdba.com.au<br>E: simon@spdba.com.au<br>V: +61 <strong>362 396 397</strong><br>M: +61 <strong>418 396 391</strong><br>GITC Supplier: T1005<br>Skype: sggreener<br>Long: 147.20515 (147° 12' 18" E)<br>Lat: -43.01530 (43° 00' 55" S)<br>GeoHash: r22em9r98wg<br>NAC:W80CK 7SWP3</div></div></div></body></html>