[postgis-users] Help with an area and hole filter query?
simon at spatialdbadvisor.com
Fri Jan 2 00:55:21 PST 2009
> This looks interesting. Actually hadn't thought of using ST_DumpRings, but
> I think that would be better than the ST_InteriorRingN
Certainly leads to simpler SQL.
> Couple of comments
> 1) You should do this
> SELECT (ST_DumpRings(a.geom)).*
> Instead of this
> SELECT (ST_DumpRings(a.geom)).geom As the_geom, path(ST_DumpRings(a.geom))
> as path
> (Which would mean in the upper part you would need to reference by .geom
> instead of the_geom
> The reason for that is internally PostgreSQL will call ST_DumpRings twice.
> This was pointed out to me by a very experienced PostgreSQL developer.
Ahh, yes, of course. I wondered about this when I did the SQL. I guess the function is deterministic which means it must process its input twice because the query optimizer doesn't realise the function is being called with the same input.
A good improvement.
> His blog entry about it is here
I'll look at it.
> 2) I think ST_BuildArea might be better than ST_MakePolygon in this regard.
> It will work fine with a single closed ring and if multiple, it turns the
> inners to holes.
Ahh, didn't notice ST_BuildArea as a replacement for ST_MakePolygon. I like what you have done in the second email and concur that it produces the sort of concise SQL we are after.
> So what I was thinking in verbiage
> ST_BuildArea(ST_Collect all exterior/interior excluding all interior rings
> where area < desired (that would exclude holes that are too small))
Will discuss your other email.
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.
Email: simon at spatialdbadvisor.com
Voice: +613 9016 3910
Mobile: +61 418 396391
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
More information about the postgis-users