[postgis-users] Geography/geometry

Simon Greener simon at spatialdbadvisor.com
Mon Jan 21 13:28:48 PST 2019


Paul,

I can't find a suitable gnomic srid for PostGIS.

I have tried this:

INSERT INTO spatial_ref_sys (srid, proj4text) VALUES (55000, '+proj=gnom +lat_0=90 +lon_0=-7.646288');

but I get this when I use ST_Transform.

UPDATE data.osm_county SET geom55000 = ST_Transform(geog4326::geometry,55000);

AddToPROJ4SRSCache: could not parse proj4 string '+proj=gnom +lat_0=90 +lon_0=-7.646288' major axis or radius = 0 or not given

I understand your position about things being hard.

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.

regards
Simon

On Tue, 22 Jan 2019 03:41:21 +1100, Paul Ramsey <pramsey at cleverelephant.ca> wrote:

> 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.
>
> P
>
>> On Jan 20, 2019, at 9:23 PM, Simon Greener <simon at spatialdbadvisor.com> wrote:
>>
>> Folks,
>>
>> I'm in a situation where I need to ST_Union or ST_Collect some osm_county Polygon (not MultiPolygon) data for Ireland.
>>
>> Now, because ST_Union or ST_Collect do not support geography, I cast to geometry before calling.
>>
>> select min(a.osm_id) as osm_id,      a.name,
>>       count(*) as parts,      ST_Union(a.geog4326::geometry)::geography as geog4326 -- or ST_Collect
>> from data.osm_county as a
>> group by a.name;
>>
>>>> Whence I get this:
>>
>>>> ERROR: lwgeom_area_spher(oid) returned area < 0.0
>>
>>>> Investigating I get results like this:
>>
>>>> select distinct st_isvalidreason(a.geog4326::geometry) from data.osm_county as a;
>>
>>>> "Hole lies outside shell[-10.2589459 53.9746452]"
>> etc
>>
>>>> I guess this is expected because geodetic lines in the source geography are being treated as straight in the cast'd >>geometry.
>>
>> 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.
>>
>> 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
>>
>> I can't for the life of me work out how to complete the aggregated ST_Union on the 4326 geography data.
>>
>> Anyone point out what I am doing wrong or give me a pointer to what I can do to achieve the aggregated union?
>>Regards
>> Simon
>> --------------------------------------------------------------------------------------------------------
>> Spatial Advice & Solutions Architecture
>> Database Spatial Stored Procedure Designer
>> Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME
>> Awarded "2011 Oracle Spatial Excellence Award for Education and Research"
>> A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust
>> W: www.spdba.com.au
>> E: simon at spdba.com.au
>> V: +61 362 396 397
>> M: +61 418 396 391
>> GITC Supplier: T1005
>> Skype: sggreener
>> Long: 147.20515 (147° 12' 18" E)
>> Lat: -43.01530 (43° 00' 55" S)
>> GeoHash: r22em9r98wg
>> NAC:W80CK 7SWP3
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Regards
Simon
--------------------------------------------------------------------------------------------------------
Spatial Advice & Solutions Architecture
Database Spatial Stored Procedure Designer
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE FME
Awarded "2011 Oracle Spatial Excellence Award for Education and Research"
A: 39 Cliff View Drive, Allens Rivulet, 7150, Tas, Aust
W: www.spdba.com.au
E: simon at spdba.com.au
V: +61 362 396 397
M: +61 418 396 391
GITC Supplier: T1005
Skype: sggreener
Long: 147.20515 (147° 12' 18" E)
Lat: -43.01530 (43° 00' 55" S)
GeoHash: r22em9r98wg
NAC:W80CK 7SWP3
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190122/96c4d197/attachment.html>


More information about the postgis-users mailing list