[postgis-users] Getting dimensions of polygon sides

[Simon Greener]

Regina,

> Could have convinced me you were a postgis expert :)

Thanks for the compliment but I will still claim amateur status! I know SQL fairly well but the PostgreSQL "framework" is still something that will take many years to master. The sad thing, for me, is that I have no customers (I am an independent spatial consultant living at the World's End) who use PostgreSQL/PostGIS. All my efforts with PostgreSQL/PostGIS are me learning new tricks in my spare time for the day when that customer arrives!

> I'll have to kick this around a bit.

I would appreciate what you come up with.

> Couple of minor comments
>
> 1)  I would probably encapsulate this
>  SELECT exteriorring(p_geom) as geom
>                          UNION ALL
>                          SELECT interiorringn(p_geom,generate_series(1,numinteriorrings(p_geom))) as geom
>                        ) a
>               ) b;
>
> Into an sql function (NOTE not a plpgsql function).  But I'm a bit torn about that and is more a close judgement call.

Generally, Regina, I agree with you. I prefer to solve all my problems with heavily tuned (via explain plan etc) single SQL statements rather than wrap them in functions whether SQL or plpgsql/PLSQL. Since Oracle doesn't have SQL Functions (only PL/SQL) I will have a look at how I can use them in this exercise.

> a) on the one had it would tuck away logic, but I think its a common enough construct that its probably getting in the way of digesting the overrall picture.

No "right" answer here I think. I too will play around with it.

> b) sql functions like that would get inlined anyway so you would see it in an explain plan if you really needed to analyze them and would get optimized just as effectively as the rest of your statement (where as if you stuffed it in a plpgsql construct - it would not get inlined and not be optimized).

OK, got that.

> 2) Use the new naming conventions - you have a mix of both.  e.g.
>
> ST_NumInteriorRings, ST_InteriorRingN  etc.

My recollection here was that I started with ST_* (because it would be more transportable to Oracle ST_*) but found that a problem existed with one of them that didn't in the traditional PostGIS function signature form. I'll revisit this to provide better feedback.

> 3) As far as circular strings and what not - I have a similar need, but the Postgis curved support as you have probably noticed is very spotty.

Yes, I went looking for ST_curvepolygon etc type functions (Oracle has some in its implementation of the SQLMM standard) but couldn't find them. Instead of writing some myself I put that aspect of the "exercise" in the "for another day" entry of my calendar!

Thanks for your comments: it has heartened me greatly.

S
-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
   Email: simon at spatialdbadvisor.com
   Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3