Bruce, Unfortunately I don't think I can move that into the CROSS JOIN because the generate_series would then be dependent on the other table. If PostgreSQL had a CROSS APPLY similar to SQL Server 2005, then I could implement what you are saying and it probably would be faster. Anyrate I think my 1000 is too high so I could probably improve speed a fair amount by reducing that to max numpoints of any of my parcels.
For 5000 parcels the insert took I think about 60 secs on my modest server and generated about 19000 records. I had originally planned to do all 200,000 parcels and when I started thinking 200,000 X 10 would generate over 2 million records (then I forgot I had condos in there that I should have excluded so really only 1 million) , I decided to just scale down to what I actually needed for this particular project. Thanks, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Rindahl Sent: Wednesday, June 11, 2008 4:25 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Getting dimensions of polygon sides Regina After looking closely at your code I think you are doing exactly the same as I suggested. I had to add the intermediate steps because my method needed to find the line (distance) between 2 arbitrary points on the polygon where you are always looking at two adjacent ones. The only thing I see is your last line. Will changing : FROM dnd.rems_survey ap ) p) As b CROSS JOIN generate_series(1, 1000) n WHERE n < b.nump; to: FROM dnd.rems_survey ap ) p) As b CROSS JOIN generate_series(1, b.nump - 1) n; be any faster? It should avoid a WHERE test in each iteration. Bruce Obe, Regina wrote: > Bruce, > > Thanks for the below. I think it will take me a while to digest what > you are doing. I think I learned a couple of things off the bat. > > 1) I may need the Right Hand Rule call since I was assuming things > were already ordered correctly and my simple spot check seems to > suggest that it is but you never know. > > 2) I'm confused between ST_Boundary and ST_ExteriorRing and when to > use one over the other. I assume ST_Boundary takes into consideration > holes where as ST_ExteriorRing just gives you the outer ring. Now I'm > thinking about it I probably should replace my ST_Boundary with > ST_ExteriorRing. > > In case anyone is curious. Attached is a snapshot of what I get when > I use my simple 2 point assumption. It works in most cases but in > others where they used more than 2 points to describe each corner, I > get extra measures. > Not the dimok is my ideal case and the dimalmost okay - see how I have > an extra measure. > > You think Simplifying before I extract would fix that. > > My final query and updates look like this - seems to run fairly fast > for the 5000 parcel list I care about. > > INSERT INTO assessing.parcdimstime_2008(pid, pid_year, the_geom) > SELECT b.pid, b.pid_year, ST_MakeLine(ST_PointN(the_boundary,n), > ST_PointN(the_boundary, n + 1)) As the_side > FROM (SELECT pid, pid_year, the_boundary, > ST_NumPoints(the_boundary) As nump > FROM (SELECT ap.parcelid as pid, 2008 as pid_year, > ST_Boundary(ap.the_geom) As > the_boundary > FROM dnd.rems_survey ap ) p) As b > CROSS JOIN generate_series(1, 1000) n > WHERE n < b.nump; > > > UPDATE assessing.parcdimstime_2008 SET side_length = > CAST(ST_Length(the_geom) As numeric(8,2)); > > > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
