[postgis-users] Getting dimensions of polygon sides

[Paragon Corporation]

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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users