[postgis-users] Getting dimensions of polygon sides

[Martin Davis]

Sigh - yet another use for a SPLIT BY clause!

Paragon Corporation wrote:
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022